Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

SWQL Query Help

I'm terrible at SQL and I'm trying to learn SWQL this year. Examples go a long way for my learning.

Can someone give me a basic SWQL query to list nodes and filter by node custom property?

I believe the data I want is in Orion.Nodes and Orion.NodesCustomProperties. I see there is a link but I'm unclear how to get what I want without a JOIN.

Find more posts tagged with

swql query

Accepted answers

KMSigma.SWI

SELECT [Nodes].Caption
     , [Nodes].IPAddress
     , [Nodes].CustomProperties.City
     , [Nodes].CustomProperties.Comments
-- other CP's like above
FROM Orion.Nodes AS [Nodes]
-- You can also filter for specific nodes based on the custom property
-- WHERE [Nodes].CustomProperties.City = 'Austin'

All comments

KMSigma.SWI

SELECT [Nodes].Caption
     , [Nodes].IPAddress
     , [Nodes].CustomProperties.City
     , [Nodes].CustomProperties.Comments
-- other CP's like above
FROM Orion.Nodes AS [Nodes]
-- You can also filter for specific nodes based on the custom property
-- WHERE [Nodes].CustomProperties.City = 'Austin'

LatteLarry

Can you advise how to write the WHERE to show me custom properties that are blank (NULL)? Something is wrong with the way I'm writing it and it returns a table with no results. No error either. I've tried writing the NULL different ways and even using ISNULL with the same blank results. There are definitely Nodes with NULL.

SELECT TOP 100
[Nodes].Caption
, [Nodes].IPAddress
, [Nodes].DNS
, [Nodes].CustomProperties.TeamOwner_Nodes
, [Nodes].CustomProperties.ResponseLevel_Nodes
-- other CP's like above
FROM Orion.Nodes AS [Nodes]
-- You can also filter for specific nodes based on the custom property
-- WHERE [Nodes].CustomProperties.City = 'Austin'
WHERE [Nodes].CustomProperties.TeamOwner_Nodes = ''

christopher.t.jones123

@LatteLarry, try this one. When trying to track down blank CP values i like to do both an IS NULL or ='' because when you do a bulk import it'll sometimes change the NULL to a blank value which isnt picked up by the NULL. I've had good success keeping track of them doing something like below.

SELECT TOP 100 
       [Nodes].Caption
     , [Nodes].IPAddress
     , [Nodes].DNS
     , [Nodes].CustomProperties.TeamOwner_Nodes
     , [Nodes].CustomProperties.ResponseLevel_Nodes
-- other CP's like above
FROM Orion.Nodes AS [Nodes]
-- You can also filter for specific nodes based on the custom property
-- WHERE [Nodes].CustomProperties.City = 'Austin'
WHERE [Nodes].CustomProperties.TeamOwner_Nodes = '' OR [Nodes].CustomProperties.TeamOwner_Nodes IS NULL

KMSigma.SWI

You can also use the IsNull function (generally better accepted and saves a step)

SELECT TOP 100 
       [Nodes].Caption
     , [Nodes].IPAddress
     , [Nodes].DNS
     , [Nodes].CustomProperties.TeamOwner_Nodes
     , [Nodes].CustomProperties.ResponseLevel_Nodes
-- other CP's like above
FROM Orion.Nodes AS [Nodes]
-- You can also filter for specific nodes based on the custom property
-- WHERE [Nodes].CustomProperties.City = 'Austin'
WHERE IsNull([Nodes].CustomProperties.TeamOwner_Nodes, '') = ''

NULL is a tricky beast that can be difficult to track down - which is why the IsNull function exists at all.

Syntax is like this:

IsNull(<fieldName>, <replacement>) = <result>

If the <fieldName> is NULL, then use the value in <replacement>
If it's not then pass on the value in <fieldName>.