cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 10

SWQL for status count in multiple columns from same table

Jump to solution

Namaste folks,

 

looking for SWQL which can provide output as follows. 

ref table:

nodeIDnode statusnode category(custom property)
11server
21server
31server
41network
51network
61network
71network
82server
92network
102network

 

required output:

categoryup (1)down(2)
network42
server31
0 Kudos
1 Solution

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

View solution in original post

5 Replies
Level 9

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}

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_Providerupdown
xyz2010
abc305
bla bla400

 

 

thanks

0 Kudos

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

View solution in original post

Thanks Bubowski.

 

0 Kudos
Level 9

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

0 Kudos