SQL Query on CustomTablePoller Data

Got some custom table pollers running for wireless anchor status. I want to alert when ALL of the anchors for a give wireless lan controller have rowstatus !=1.

Let's say this is my data

rowid	nodeid	label	rowstatus
1	1011	LC1	1
2	1011	LC1	1
3	1023	LC2	0
4	1023	LC2	0
5	1023	LC2	0
6	2011	LC3	1
7	2011	LC3	1
8	2011	LC3	1
9	2011	LC3	1

My SQL skill are good enough for simple stuff like find which rows have rowstatus !=1

SELECT NODEID 
FROM CUSTOMPOLLERSTATUSTABLE
WHERE POLLIERID = 'EXAMPLE_POLLERID)
AND ROWSTATUS =1 1

but I'm struggling to work our how to return the node id's of the nodes where ALL of the rows for that node have rowstatus !=1

Find more posts tagged with

custom_poller

sql

Accepted answers

BaldFeegle

Turns out I just needed a conditional count.

SELECT Nodes.Caption, Nodes.NodeID
FROM Nodes 
JOIN
CustomPollerStatusTable cps
ON cps.NodeID = Nodes.NodeID
WHERE cps.pollerid = 'example_poller_id'
GROUP BY Nodes.Caption, Nodes.NodeID
HAVING COUNT (CASE WHEN cps.RowStatus = 1 THEN 1 END) = 0

All comments

BaldFeegle

SELECT COUNT (concat(NodeID, rowstatus)), NodeID
FROM [SolarWindsOrion].[dbo].[CustomPollerStatusTable]
WHERE pollerid = 'EXAMPLE_POLLERID'
AND rowstatus != 1
group by NodeID

I feel like I'm edging closer with this which returns the count of nodeid's that have rowstatus != 1 but can't work out how to compare that to the total number of rows for each nodeid.

BaldFeegle

Turns out I just needed a conditional count.

SELECT Nodes.Caption, Nodes.NodeID
FROM Nodes 
JOIN
CustomPollerStatusTable cps
ON cps.NodeID = Nodes.NodeID
WHERE cps.pollerid = 'example_poller_id'
GROUP BY Nodes.Caption, Nodes.NodeID
HAVING COUNT (CASE WHEN cps.RowStatus = 1 THEN 1 END) = 0