Multiple status interface report

prashant_kadam

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%'
.status,
COUNT (CASE Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE Status WHEN '2' THEN 1 else NULL end) AS DOWN

Thanks

KMSigma.SWI

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.