Having worked with the SDK (namely SWQL) nearly since its release to the public I have say that I find the SDK/API feature rich, extraordinarily reliable and key path to a Solarwinds installation that meets the unique needs of each environment I've worked with it. However, there are a few things I would like to mention that I feel are unfortunate oversights or omissions from the SWQL language. SWQL has no CHARINDEX() type function. This is a frustrating short coming of the query language. Unless I want an extraordinarily large list of custom properties, one for each value pair, there is no way to parse a custom property value based on a know string. Also, let's be honest with ourselves, the array functions require manual delimination and string entry... unless someone knows something I don't... which is completely possible. CONCAT() can not be used in the 2nd argument of SUBSTRING() (more specifically a varchar(max)) This is almost as frustrating as #1 and some days even more so. If it was possible to use CONCAT() in the 2nd argument it would alleviate some of the frustration and lack of functionality created by the complete absence of a CHARINDEX() type function. SWQL has no REPLACE() type function. This is another frustrating handicap of the SWQL language. REPLACE() is another mainstay in the string manipulation family of functions that is sadly absent from the SWQL function library. I can live without the ability to use the local variables, temporary tables, the inability to CAST() a datetime and the advanced string by type parsing that SQL offers. These 3 issues have me, more often then not, using SQL queries rather than SWQL queries. It's unfortunate as I prefer to use the native query language when ever possible. My hats off to the developers and maintainers of the SDK. It's a heck of thing you've got there.

This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL Functions Feeback... Enhancments?

dnerdahl over 6 years ago

Having worked with the SDK (namely SWQL) nearly since its release to the public I have say that I find the SDK/API feature rich, extraordinarily reliable and key path to a Solarwinds installation that meets the unique needs of each environment I've worked with it.

However, there are a few things I would like to mention that I feel are unfortunate oversights or omissions from the SWQL language.

SWQL has no CHARINDEX() type function.
1. This is a frustrating short coming of the query language. Unless I want an extraordinarily large list of custom properties, one for each value pair, there is no way to parse a custom property value based on a know string.
  Also, let's be honest with ourselves, the array functions require manual delimination and string entry... unless someone knows something I don't... which is completely possible.
CONCAT() can not be used in the 2nd argument of SUBSTRING() (more specifically a varchar(max))
1. This is almost as frustrating as #1 and some days even more so. If it was possible to use CONCAT() in the 2nd argument it would alleviate some of the frustration and lack of functionality created by the complete absence of a CHARINDEX() type function.
SWQL has no REPLACE() type function.
1. This is another frustrating handicap of the SWQL language. REPLACE() is another mainstay in the string manipulation family of functions that is sadly absent from the SWQL function library.

I can live without the ability to use the local variables, temporary tables, the inability to CAST() a datetime and the advanced string by type parsing that SQL offers. These 3 issues have me, more often then not, using SQL queries rather than SWQL queries. It's unfortunate as I prefer to use the native query language when ever possible.

My hats off to the developers and maintainers of the SDK. It's a heck of thing you've got there.

Top Replies

0 tdanner over 6 years ago

Thanks for the kind words!
1. CHARINDEX would be very useful. I opened an issue internally to get that added.
2. The second argument to SUBSTRING is a number. I don't think I understand what you are trying to do here. Are you assembling a number by concatenating strings?
3. Good news on this REPLACE - this is implemented for NPM 12.2, which is currently in beta.
Cancel
Vote Up +1 Vote Down

Cancel
0 dnerdahl over 6 years ago in reply to tdanner
tdanner
Thank you for the reply!
This makes me extremely excited. The shear volume of queries I'll be able to convert from SQL to SWQL would keep me busy for weeks. The reliability of queries written to the SDK schema vs. directly to the database tables makes my business continuity personality very happy. I've been bitten by dbo schema changes in the past.
Yes. That's exactly what I have been trying to do. However, based on your comment I pulled out a few of the code bits I had worked on in the past. I wrapped the CONCAT() in with a (+ 0) and it ran just fine. For some reason I was getting a varchar() back instead of an int(). I must have a rouge space or something that was hiding in there... or a numeric substring() return that thought it was a varchar().
I'm glad to hear that REPLACE is coming! I may have done a dance.
Cancel
Vote Up 0 Vote Down

Cancel
0 dnerdahl over 6 years ago

I seem to have omitted one item that has continued to perplex me, and caused me to write lots of seemingly unnecessary code. It popped back up today as I was troubleshooting an application interchange.
Why do MINUTE(GETDATE()) and HOUR(GETDATE()) return single digits in the format of "1,2,3,4" and not "01,02,03,04"?
Cancel
Vote Up 0 Vote Down

Cancel
0 tdanner over 6 years ago in reply to dnerdahl

Because they return integers, not strings.
Cancel
Vote Up 0 Vote Down

Cancel
0 dnerdahl over 6 years ago in reply to tdanner

See, now... that makes complete sense.
Unfortunately I'm usually comparing MINUTE() to a two character format because datetime fields store minutes in a "01,02,03,04" format.
Cancel
Vote Up 0 Vote Down

Cancel
0 tdanner over 6 years ago in reply to dnerdahl

Can you use the "+0" method to convert your "03"-style numbers into integers so they can be directly compared with the return value of MINUTE()?
Cancel
Vote Up +1 Vote Down

Cancel
0 dnerdahl over 6 years ago in reply to tdanner

Ah but when you CONCAT() an HOUR() with a MINUTE() you can end up with some interestingly incorrect times...
Like "1:01 AM" which concatenates to "11" or "1:1"
So I stopped doing that and just evaluated the hour and minute as two separate logic statements.
Cancel
Vote Up 0 Vote Down

Cancel
0 tdanner over 6 years ago in reply to dnerdahl

At this point there is no nice way to zero-pad a number in SWQL. The best I have come up with is this:
CASE WHEN X < 10 THEN ('0' + ToString(X)) ELSE ToString(X) END
Cancel
Vote Up +1 Vote Down

Cancel
0 msarkar over 6 years ago in reply to tdanner

tdanner, is there any update on CHARINDEX function on SWQL?
Cancel
Vote Up 0 Vote Down

Cancel
0 tdanner over 6 years ago in reply to msarkar

It is implemented, but the release that has it won't be this year.
Cancel
Vote Up 0 Vote Down

Cancel