Help Creating SWQL Query That Generates a Report of Missing Devices at a Site

Hello! First time poster. I have what feels like a complicated ask (hopefully it's not and i'm overthinking it) and would like to gain insight or assistance coming up with the best way to go about this.

I work at a company with 1100+ sites. Each site should have multiple switches, firewalls, security panels, DVRs, etc. I am wanting to generate an alert or report that is able to validate that each site has the appropriate number of devices added. Ideally the alert/report would identify if a site only has 5 devices, when it should have 6 for example, then relay which site it found that at. All of our sites have custom properties tied to it such as StoreNumber, SiteType, DeviceType, etc.

I'm not 100% sure how to go about this, but here is what i've got so far:

SELECT Nodes.Caption
FROM Orion.Nodes
WHERE Nodes.CustomProperties.SiteType = 'Store' AND Caption LIKE '%' + Nodes.CustomProperties.StoreNumber + '%'
ORDER BY Caption

This returns all the device's that i'm interested in doing a comparison on. I'm not sure how to go about doing a check and/or grouping all the nodes by StoreNumber, then checking that x amount of devices exist per store number.

If it helps, all the devices do follow a standard naming scheme. All the switches will end in S01P, S02P, S03P, etc. All the Firewalls will end in F01P, F02P, etc. DVRs will all end with 'DVR.'

Any help is appreciated on this!

Find more posts tagged with

swql query

SWQL

SWQL Studio

swql_studio

Alerting

Accepted answers

shuth

This is probably a starting point if you are just after the StoreNumber. Something like the following will list the StoreNumbers that have less than 6 devices with SiteType=Store.

If you remove or comment out the HAVING line, it will list all the stores and the device count.

SELECT StoreNumber, COUNT(NodeID) AS NodeCount
FROM Orion.NodesCustomProperties
WHERE SiteType = 'Store'
GROUP BY StoreNumber
HAVING COUNT(NodeID) < 6

All comments

shuth

This is probably a starting point if you are just after the StoreNumber. Something like the following will list the StoreNumbers that have less than 6 devices with SiteType=Store.

If you remove or comment out the HAVING line, it will list all the stores and the device count.

SELECT StoreNumber, COUNT(NodeID) AS NodeCount
FROM Orion.NodesCustomProperties
WHERE SiteType = 'Store'
GROUP BY StoreNumber
HAVING COUNT(NodeID) < 6

Disprovable

This is an amazing starting point and gets me the info I was wanting from my original ask. Thank you so much!

This is a long shot, but I don't suppose you know of a way to create a nested query that would check to see if there's a node that ends in S01P, S02P, S03P, F01P, F02P, etc. If it isn't able to find a node name that doesn't contain one of those strings in it for each site that showed up with less than 6 devices make a "missing devices" column that contains all the entries that are missing.

Just for context this is going to be used to essentially true up our environment on a weekly basis. We have MANY admins that are adding/deleting nodes in the environment and this would be a report that would identify what sites and devices we are missing from monitoring.

shuth

Hmm, not off the top of my head without something messy. Could potentially split it up into 6 different queries. Feed that list of StoreNumbers into another query that excludes the other 5 captions, or something, but maybe I'm over thinking that.