Multiple status interface report

Hey everyone,

Trying to create a report for a selected types of nodes where it will show count of interface with status against each device

Please optimize the below query

select n.nodeid, n.Caption as NodeName, i.InterfaceName, i.Status
from orion.nodes n
inner join orion.npm.interfaces i on n.nodeid = i.nodeid where nodename like '%Cisco%'
COUNT (CASE Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE Status WHEN '2' THEN 1 else NULL end) AS DOWN


  • You are missing the necessary GROUP BY for your aggregate functions.  Are you trying to count the number of up/down/etc. interfaces for all devices with a Caption that contains 'Cisco'?

    SELECT COUNT([Int].Status) AS [IntCount]
         , [Int].StatusDescription
    FROM Orion.NPM.Interfaces AS [Int]
    WHERE [Int].Node.Caption LIKE '%Cisco%'
    GROUP BY [Int].StatusDescription

    This is probably closer to what you are looking to accomplish.

  • Looking for a report which will show all cisco device interface count with respective status.

    Below is the required output

    Device 1 there are 15 up, 10 down and 5 shutdown interfaces

Reply Children
No Data