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