The below is not an exhaustive list of functions that are supported in SWQL, but it should be enough to get you started. If you’d like to review the complete list, it is available in the GitHub wiki.
Comparisons with Null
We’ll not get into the minutia of how NULL is distinct from an empty string in this Wiki. That’s been discussed elsewhere online and if you feel the need, you can investigate.
For simple logic, you can use IS NULL or IS NOT NULL to check that a value is or is not NULL. For string comparisons, it’s considered good form to use the function ISNULL to check for NULL since this removes the overly complex NULL-based logic. The key defining characteristic about NULL is this: An empty string, the number 0, and the Boolean value of False are not the same as NULL, though to the human mind they frequently are confused as such.
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].NodeDescription IS NULL
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
The above logic will probably return no results because the Node Description is an empty string and not a NULL value (remember: an empty string is not the same as a NULL value). To easily check for both possibilities (which is very common), you can use the ISNULL function. The ISNULL function operates in this way:
ISNULL(<Fieldname>, <ArbitraryValue>)
If the field <FieldName> is NULL, then return the <ArbitraryValue>, otherwise return the value in <FieldName>.
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 ISNULL([Nodes].NodeDescription, '') = ''
ORDER BY [Nodes].ResponseTime DESC, [Nodes].Caption
In this example, the WHERE clause states: if the Node Description is NULL, then return an empty string. If it is not NULL, then return the stored value. Compare that returned value (either the empty string or the field value) to an empty string.
This is the most common way to work with NULLs when it pertains to string comparisons. This is an important distinction and it’s an easier lesson to learn before we engage with more complicated scenarios.
Integer to Decimal Conversation
SWQL does not support the CAST or CONVERT SQL functions for changing data types. The simplest way to convert an integer to a decimal value is to multiply it by 1.0 (the ‘.0’ is important here).
This can be easier understood when working with a simple aggregate query.
SELECT [Nodes].Vendor AS [VendorName]
, AVG([Nodes].ResponseTime) AS [Average Response Time]
FROM Orion.Nodes AS [Nodes]
GROUP BY [Nodes].Vendor
Response Time is stored as an integer (response time in milliseconds). Since SWQL doesn’t know if you want to return the values in any other form, it defaults to returning the results as the original data type (an integer).
This can be overridden by forcibly converting an integer value to a decimal value. Any integer value multiplied (or divided) by a decimal value will result in a decimal data type. Since multiplying or dividing any value by 1.0 yields the same result, we can forcibly convert the values from an integer type to a decimal type.
SELECT [Nodes].Vendor AS [VendorName]
, AVG( [Nodes].ResponseTime * 1.0 ) AS [Average Response Time]
FROM Orion.Nodes AS [Nodes]
GROUP BY [Nodes].Vendor
Multiplying the integer value from Response Time by 1.0 converts it to a decimal value which, in turn, allows the AVG function to return a decimal value.
Rounding Numbers
Converting from a decimal to an integer can be done with the ROUND function.
SELECT [Nodes].Vendor AS [VendorName]
, AVG( [Nodes].ResponseTime * 1.0 ) AS [Average Response Time]
, ROUND( AVG( [Nodes].ResponseTime * 1.0 ), 2) AS [Rounded Response Time]
FROM Orion.Nodes AS [Nodes]
GROUP BY [Nodes].Vendor
The ROUND function will round values to the nth decimal place. In the above example we are presented with the average of response times, but we only want it to 2 decimal places. If you pass 0 for the decimal places, traditional rounding takes place. Additionally, the functions CEILING and FLOOR are available which round up or down, respectively, directly to the next integer.
SELECT [Nodes].Vendor AS [VendorName]
, AVG( [Nodes].ResponseTime * 1.0 ) AS [Average Response Time]
, ROUND( AVG( [Nodes].ResponseTime * 1.0 ), 2) AS [Rounded Response Time]
, CEILING( AVG( [Nodes].ResponseTime * 1.0 ) ) AS [Upper Integer]
, FLOOR( AVG( [Nodes].ResponseTime * 1.0 ) ) AS [Lower Integer]
FROM Orion.Nodes AS [Nodes]
GROUP BY [Nodes].Vendor