Help with editing the SQL in a Report

[stuartd]

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...

[Seashore]

You are on to something, if your operstatus is all number, ie. not Up or Down they will just be ignored.

I made another query for all that don't use NCM but NPM. Added a where-clause to filter out some interface types that is virtual.

SELECT
  N.Caption AS [Device Name],
  IP_address AS [Device IP],
       COUNT(
            CASE WHEN i.AdminStatus = 1 
            THEN 1 ELSE NULL END
            ) 
                 AS [Admin Up],
       COUNT(
            CASE WHEN i.OperStatus= 1 
            THEN 1 ELSE NULL END
            ) 
                 AS [Oper Up],
       COUNT(
            CASE WHEN i.AdminStatus = 2 
            THEN 1 ELSE NULL END
            ) 
                 AS [Admin Down],
       COUNT(
            CASE WHEN I.OperStatus = 2 
            THEN 1 ELSE NULL END
            ) 
                 AS [Oper Down],
       COUNT(
            I.AdminStatus
            ) 
                 AS [Total] 
FROM 
    orion.Nodes n
    Inner join Orion.npm.Interfaces I on I.NodeID=n.NodeID
WHERE
    I.Type NOT IN (1,24,53,131)  --Other, Loopback, Proprietary Virtual, Encapsulation Interface
--WHERE
  --i.p.PhysicalInterface = 'Y' --and ClientPrefix = 'XYZ'
GROUP BY
  n.Caption, n.IP_Address
ORDER BY
  n.Caption

[stuartd · Accepted solution]

Thanks for that @Seashore and as soon as I typed that up I went off and tried it and got expected results 🙂

I haven't tried your variant, but a primary reason for not using NPM is that, AIUI, it will only report back on interfaces that are being monitored. Whereas, again AIUI, NCM grabs everything and it's just a matter of extracting the relevant detail.

I note you've edited the WHERE clause - I'd definitely need to include my ClientPrefix = but I feel I can go off and work on that one.