cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Interface Usage Report

I don't know if this will help anyone, but I put this together to give an accurate and easy to read report for Device Interface Usage.

SELECT

NodeCaption AS [Device Name],

AgentIP AS [Device IP],

 

CAST(COUNT(CASE WHEN NCM_interfaces.OperStatus = '1' THEN 1 ELSE NULL END) as nvarchar) + '/' + CAST(COUNT(AdminStatus) as nvarchar) AS 'Interfaces In Use'

,CAST(100 * COUNT(CASE WHEN NCM_interfaces.OperStatus = '1' THEN 1 ELSE NULL END) as int) / CAST(COUNT(AdminStatus) as int) AS Interface_Use_Pct

FROM

NCM_Nodes

INNER JOIN NCM_Interfaces ON NCM_Interfaces.NodeID=NCM_nodes.NodeID
WHERE NCM_Interfaces.InterfaceType = '6'
GROUP BY NodeCaption, AgentIP
ORDER BY Interface_Use_Pct DESC

0 Kudos
2 Replies
Community Manager
Community Manager

Is this intentionally ignoring interfaces which are shutdown? (AdminStatus of 2)

"Shoot for the stars to reach the moon"
0 Kudos

Yes, I have a report for admin up, Oper Up, Admin down, Oper Down... but it didn't prove useful for what I needed.

0 Kudos