Namaste folks, looking for SWQL which can provide output as follows. ref table: nodeIDnode statusnode category(custom property)11server21server31server41network51network61network71network82server92network102network required output: categoryup (1)down(2)network42server31

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 for status count in multiple columns from same table

Namaste folks,

looking for SWQL which can provide output as follows.

ref table:

nodeID	node status	node category(custom property)
1	1	server
2	1	server
3	1	server
4	1	network
5	1	network
6	1	network
7	1	network
8	2	server
9	2	network
10	2	network

required output:

category	up (1)	down(2)
network	4	2
server	3	1

Find more posts tagged with

Accepted answers

bubowski

Ok, your first request was a little different. I just tested the below script on my database and got a good result. FYI... you do not need to SELECT nodeid, just include it in your join statement.

SELECT
ISP_Provider,
COUNT (CASE Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE Status WHEN '2' THEN 1 else NULL end) AS DOWN
FROM ORION.Nodes
JOIN Orion.NodesCustomProperties ON nodes.nodeid = NodesCustomProperties.nodeid
WHERE ISP_Provider <> 'NULL'
GROUP BY ISP_Provider

All comments

bubowski

I think this should work for you:

SELECT
COUNT (CASE Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE Status WHEN '2' THEN 1 else NULL end) AS DOWN,
COUNT (Status) as Total
FROM ORION.Nodes

bubowski

Sorry, just saw your custom property. Try this and fill in your custom property where applicable:

SELECT
{Your Custom_Property},
COUNT (CASE Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE Status WHEN '2' THEN 1 else NULL end) AS DOWN
FROM ORION.Nodes
JOIN {Custom Property Table} ON nodes.nodeid = {custompropertytable}.nodeid
WHERE {Custom_Property} = 'Server' OR {Custom_Property} = 'Network'
GROUP BY {Custom_Property}

anil.singh

I guess, I'm doing something wrong or gave the wrong info at first.

your provided sql not working.

So, here is the thing, two tables used.

1- SELECT NodeID, Status FROM Orion.Nodes

2- SELECT ISP_Provider, NodeID FROM Orion.NodesCustomProperties

will make filter, only for Status = '1' AND Status = '2' AND ISP_Provider IS NOT NULL

required output to show how many nodes are up and down per ISP. Need this to use in modern dashboard.

ISP_Provider	up	down
xyz	20	10
abc	30	5
bla bla	40	0

thanks

bubowski

Ok, your first request was a little different. I just tested the below script on my database and got a good result. FYI... you do not need to SELECT nodeid, just include it in your join statement.

anil.singh

Thanks Bubowski.