So I found this post from 2017 that looks like it should do what I need for a specific set of our clients.
The code in it is reproduced below and all I've done is add to the WHERE clause and ClientPrefix = 'XYZ' (where XYZ is replaced with an actual value) - but even without that, I only get an overall total number of ports in each device and not the Up / Down details.
SELECT NodeCaption AS [Device Name], AgentIP AS [Device IP], COUNT( CASE WHEN n.Interfaces.AdminStatus = 'Up' THEN 1 ELSE NULL END ) AS [Admin Up], COUNT( CASE WHEN n.Interfaces.OperStatus = 'UP' THEN 1 ELSE NULL END ) AS [Oper Up], COUNT( CASE WHEN n.Interfaces.AdminStatus = 'Down' THEN 1 ELSE NULL END ) AS [Admin Down], COUNT( CASE WHEN n.Interfaces.OperStatus = 'Down' THEN 1 ELSE NULL END ) AS [Oper Down], COUNT( n.Interfaces.AdminStatus ) AS [Total] FROM NCM.Nodes n WHERE n.Interfaces.PhysicalInterface = 'Y' and ClientPrefix = 'XYZ' GROUP BY NodeCaption, AgentIP ORDER BY NodeCaption
The output looks like this:
Any ideas why it isn't picking up the detail values?
Is it as simple as I just need to change the words to numbers? As looking at NCM.Interfaces my OperStatus values, etc are all numbers?
e.g. OperStatus has values from Null to 0, 1, 2, up to 7 as in change the UP in "WHEN n.Interfaces.OperStatus = 'UP'" to its numeric value?
As a complete aside, I was surprised the ClientPrefix bit worked as that is a Custom Property field from another table and my understanding is that you have to JOIN other tables in to achieve that...