Null field impact on queries

[trilobite_rex]

What is the impact of a null field on a string comparison? For example, we want to eliminate UPSes from showing up on our down nodes page widget.
We cobbled together the following query and there are rows in the table where the DevicePurpose field is null (the other three fields do not have any
rows in the table with null values). The question that is being asked does this have any effect on any of the nodes that have an assigned value of Null?

IsTest = 'False' and IsWorkstation = 'False' and MachineType <> 'VMware ESX Server' and DevicePurpose <> 'UPS'

or do we need to use the following query:

IsTest = 'False' and IsWorkstation = 'False' and MachineType <> 'VMware ESX Server' and (DevicePurpose is Null or (DevicePurpose is not Null and DevicePurpose <> 'UPS'))

[mesverrum]

For a query like that you can also do this to save some miles on your keyboard

IsTest = 'False' and IsWorkstation = 'False' and MachineType <> 'VMware ESX Server' and ISNULL(DevicePurpose,'') <> 'UPS'

[xtraspecialj]

Yeah, it's a limitation of SQL and since the backend API essentially relies on SQL queries, it becomes a limitation of the product.  We solved it by creating an automation PowerShell script that runs regularly as a scheduled task on one of our polling engines.  It goes and finds custom property values that are set to NULL and it replaces them with the word '_Undefined'.  Not the most elegant solution, but it works.

[sum_giais]

An interesting spin on that, the SWIS API exposes additional options not available when creating the property in the UI. Namely being able to define a default value. If interested you can read up.

https://github.com/solarwinds/OrionSDK/wiki/Managing-Custom-Properties

https://thwack.solarwinds.com/t5/Orion-SDK-Documents/TC18-Creating-and-Updating-Orion-Custom-Propert...