Standard Boolean operators are supported in SWQL including AND, OR, negation (NOT), membership (IN), and range (BETWEEN). These can be nested and are evaluated based on their order or via parenthetical groupings. Parenthetical groups are processed first and then the result is passed back to the parent level as a single Boolean value.
Membership checking with the IN Operator
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Vendor IN ( 'Linux', 'Windows' )
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
The above query is functionally identical to checking the Vendor to match each entry listed in the membership target.
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Vendor = 'Linux'
OR [Nodes].Vendor = 'Windows'
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
Negation Operations
Likewise, this comparison can be combined with other Boolean functions, like the negation (NOT) function.
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Vendor NOT IN ( 'Linux', 'Windows' )
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
Again, this is functionally the same as checking for matching within the parenthetical pair of “Windows” and “Linux” and then taking the negation of that result.
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE NOT (
[Nodes].Vendor = 'Linux'
OR [Nodes].Vendor = 'Windows'
)
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
Range Comparisons with BETWEEN
Like the IN operator, the BETWEEN operator also has an exploded interpretation. The BETWEEN operator behaves as a combination of greater than and less than operations at the same time. The lower bound should always be used first and the upper bound, second.
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].ResponseTime BETWEEN 0 AND 10
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
Numerical Comparisons
Standard numerical comparisons are handled with traditional operators to which you are already familiar: greater than, greater than or equal to, less than, less than or equal to, equal to, and not equal to. These are enacted with standard syntax to which you are already familiar.
| Comparison | Operator |
|---|
| Greater than | > |
| Greater than or equal to | >= |
| Less than | < |
| Less than or equal to | <= |
| Equal to | = |
| Not equal to | <> (or !=) |
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Vendor = 'Windows'
AND [Nodes].ResponseTime > 1
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
The above query shows when the vendor is Windows and the Response Time is greater than 1.
Combining Boolean Logic
To use complex Boolean logic, it’s best to use parenthesis to handle the filters.
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE ( [Nodes].Vendor = 'Windows' OR [Nodes].Vendor = 'Linux' )
AND [Nodes].ResponseTime > 1
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
The above query will show nodes with response times over 1 and who’s vendor is either Windows or Linux.
String Matching
String matching is frequently used for grouping and most often the LIKE operator is used.
The LIKE comparison uses wildcards to represent unknown characters. For multiple characters, use the percent sign (%) and for single characters use the underscore (_). They can be used together for complex matching.
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Caption LIKE '%P-__d'
The above query will match any node with Caption that is starts with anything but has “P-“, followed by two characters, and ends with a “d”.
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Caption LIKE 'WEST%' --Starts with "WEST"
OR [Nodes].Caption LIKE '%CORE' --Ends with "CORE"
OR [Nodes].Caption LIKE '%-3750-%' --Contains "-3750-"
The most common uses of the LIKE syntax are outlined above. These are the starts with, ends with, and contains operations common to string filtering.
Note that you cannot combine the LIKE comparison and an IN comparison. Although this is valid syntax:
SELECT [Nodes].Caption AS [Node Name]
, [Nodes].IPAddress AS [IP]
, [Nodes].Vendor AS [VendorName]
, [Nodes].ResponseTime AS [Response Time]
FROM Orion.Nodes AS [Nodes]
-- DOES NOT RETURN WHAT YOU THINK
WHERE [Nodes].Caption IN ( 'WEST%', '%CORE', '%-3750-%' )
The IN operator does not support wildcards – just literal string comparisons, since it is a simplification of the OR operator, not the LIKE operator.