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 Assistance Please

Hi All,

This is my first post, I'm hoping someone can help me with my SWQL query.
I simply want to show A list of devices that have a node status of 2 or 3 and the country in their custom properties is not equal to 'Cloud'

SELECT
A.DisplayName as NodeName,
IPaddress as IPAddress,
A.Status as NodeStatus,
B.Country as Country
FROM Orion.Nodes A
JOIN Orion.NodesCustomProperties B ON A

WHERE (NodeStatus = 2) OR (NodeStatus = 3) AND (Country != 'Cloud')

Find more posts tagged with

Accepted answers

flackovic

Hey, sorry for the late reply. This should work for you:

SELECT
Count (N.Status) as value,
N.CustomProperties.Country AS Country

FROM Orion.Nodes N

WHERE N.Status IN ('1') AND N.CustomProperties.Country != 'Cloud'

GROUP BY N.CustompProperties.Country

COUNT is an "aggregate" function, so you need to tell it which field to aggregate by, which is done with the GROUP BY clause. In this case, you want a COUNT of every individual Country (except for Cloud).

All comments

flackovic

NodesCustomProperties values are already contained within the Nodes table as a Navigation Property, so you can avoid the JOIN altogether here. Something like this should work:

SELECT
N.DisplayName AS NodeName,
N.IPaddress AS IPAddress,
N.Status AS NodeStatus,
N.CustomProperties.Country AS Country

FROM Orion.Nodes N

WHERE N.Status IN ('2','3') AND N.CustomProperties.Country != 'Cloud'

You can read more on SWQL Studio and Navigation Properties here: SWQL Studio

joso2110

flackovic, thank you that worked perfectly for my list.
Sorry last question, how can I make this work when I'm displaying a value below? It errors out when I add the N.CustomProperties.Country AS Country

SELECT
Count (N.Status) as value,
N.CustomProperties.Country AS Country

FROM Orion.Nodes N

WHERE N.Status IN ('1') AND N.CustomProperties.Country != 'Cloud'

joso2110

This is the error when I run it:

"Provided SWQL query is not valid. Details: RunQuery failed, check fault information. Column 'dbo.NodesCustomProperties.Country' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

flackovic

Hey, sorry for the late reply. This should work for you:

SELECT
Count (N.Status) as value,
N.CustomProperties.Country AS Country

FROM Orion.Nodes N

WHERE N.Status IN ('1') AND N.CustomProperties.Country != 'Cloud'

GROUP BY N.CustompProperties.Country