cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

SWQL Single Character Wildcard / String Parsing

Jump to solution

Does SWQL have a single character wildcard?  I've been working with the SDK for a while and find string parsing to be the greatest difficulty.

I'm still trying to find a way to work around for the lack of IP Address sorting.  SWQL functions for IP Addresses

https://thwack.solarwinds.com/ideas/10592

I generally find I need REGEX pattern matching MySQL :: MySQL 5.7 Reference Manual :: 3.3.4.7 Pattern Matching and a bunch of string functions MySQL :: MySQL 8.0 Reference Manual :: 12.5 String Functions that just don't exist.

How do people generally accomplish what is needed using only SubString and CharIndex?

Nesting of these functions doesn't appear to work...

    SELECT N.IPAddress AS IP

         , N.DnsBackward

         , SUBSTRING( IP

                    , 1

                    , CHARINDEX('.',IP,1)-1) AS O1

         , SUBSTRING( IP

                    , CHARINDEX('.',IP,1)+1

                    , CHARINDEX('.',IP,CHARINDEX('.',IP,1))-1) AS O2

         , SUBSTRING( IP

                    , CHARINDEX('.',IP,CHARINDEX('.',IP,1))+1

                    , CHARINDEX('.',IP,CHARINDEX('.',IP,CHARINDEX('.',IP,1)))-1) AS O3

         , SUBSTRING( IP

                    , CHARINDEX('.',IP,CHARINDEX('.',IP,CHARINDEX('.',IP,1)))+1

                    , CHARINDEX('.',IP,CHARINDEX('.',IP,CHARINDEX('.',IP,CHARINDEX('.',IP,1))))-1) AS O4

    FROM IPAM.IPNode AS N

1 Solution

Try an underscore.

View solution in original post

14 Replies
Level 9

A handful of months later and I have a method to sort by IP Address from combining the various suggestions:

SELECT DisplayName
     , IPAddress
     , 0 + ToString(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IPAddress,'.','|§|§|')),0)) AS O1
     , 0 + ToString(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IPAddress,'.','|§|§|')),1)) AS O2
     , 0 + ToString(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IPAddress,'.','|§|§|')),2)) AS O3
     , 0 + ToString(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IPAddress,'.','|§|§|')),3)) AS O4
FROM Orion.Nodes
ORDER BY O1,O2,O3,O4

Thanks for the help.

Level 9

codebender

Try using % as a wildcard. It's been a while since I've created a SWQL query, but I used that for some wildcard purposes.

0 Kudos

Percent is a multi-character wild card that is documented Use SolarWinds Query Language (SWQL) in the Orion Platform - SolarWinds Worldwide, LLC. Help and Sup...It appears that, although undocumented, underscore '_' works as a single character like other SQL syntax.

The question was more general.  It appears that the only thing a string can be converted it (aside from other strings) is a date.

Without string manipulation syntax, how do people parse strings in SWQL?

0 Kudos

Normally if I can't do it in swql I wrap the whole query up in powershell and do my manipulations and parsing there.

- Marc Netterfield, Github

I'm using PowerShell in several instances as well, but have cases where I need SWQL to perform string parsing tasks.  The powershell implementation seems to expose much more functionality of the SDK than others.

0 Kudos
Level 9

Found a way to break apart the octets, but don't see a way to convert them into integers for the ORDER BY  ...

SELECT N.IPAddress AS IP

     , N.DnsBackward

     , ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),0) AS O1

     , ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),1) AS O2

     , ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),2) AS O3

     , ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),3) AS O4

FROM IPAM.IPNode AS N

WHERE IP IS NOT NULL

ORDER BY ???

Any ideas?

0 Kudos

tdanner had suggested in the past that you can do 0+yourvalue to get around the lack of casting in SWQL

SWQL Query Integer Conversion

- Marc Netterfield, Github
0 Kudos

Perhaps that functionality has been removed or I don't understand the suggested syntax.

When I do the following, I get an error: Failed to compare two elements in the array.

SELECT N.IPAddress AS IP

     , N.DnsBackward

     , 0 + ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),0) AS O1

     , 0 + ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),1) AS O2

     , 0 + ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),2) AS O3

     , 0 + ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),3) AS O4

FROM IPAM.IPNode AS N

WHERE IP IS NOT NULL

ORDER BY O1, O2, O3, O4

0 Kudos

Ah yeah, i can imagine that stacking those functions could give you problems since it would seem like you are trying to do math against multiple objects in the array.  I don't normally use any of those array functions in SWQL. 

Might be able to nest your whole array and subnet deal in a subquery and then do the 0+ and order by stuff in the outer query, but thats just a guess

- Marc Netterfield, Github
0 Kudos

That was a good idea, but I get the error: Attempted to access and element as a type incompatible with the array from this.

SELECT IP

     , DNS

     , 0 + O1 AS I1

     , 0 + O2 AS I2

     , 0 + O3 AS I3

     , 0 + O4 AS I4

FROM (

    SELECT N.IPAddress AS IP

         , TOLOWER(N.DnsBackward) AS DNS

         , (ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),0)) AS O1

         , (ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),1)) AS O2

         , (ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),2)) AS O3

         , (ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),3)) AS O4

    FROM IPAM.IPNode AS N

    WHERE IP IS NOT NULL

)

ORDER BY I1, I2, I3, I4

0 Kudos

SELECT L.Region, L.Country, L.Site

,ToString(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(N.IPAddress,'.','|§|§|')),0))

+'.'+ToString(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(N.IPAddress,'.','|§|§|')),1))

+'.'+ToString(ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(N.IPAddress,'.','|§|§|')),2)+'.0') AS [Subnet]

FROM Orion.Nodes N JOIN Orion.NodesCustomProperties L ON N.NodeID = L.NodeID

Try an underscore.

View solution in original post

Underscore does appear to work as a single wild card.

Level 13

I don't believe that SWQL currently supports a single wildcard character.

See Orion Platform SWQL, Thwack! Now I got it.

0 Kudos