Is there a way to get get a report of switchports not used in a specific period of time? (a reclaim switchport report)?

dwaynek

Is there a way to get get a report of switchports not used in a specific period of time? (a reclaim switchport report)?

stuartd

I have a report I created for just this and other reasons.

The pre-reqs are the nodes are configured for NCM backup and are successfully backing up.

Code is:

SELECT
  NodeCaption AS [Device Name],
  AgentIP AS [Device IP],

       COUNT(
            CASE WHEN n.Interfaces.OperStatus = 'Up' 
            THEN 1 ELSE NULL END
            ) 
                 AS [Oper Up],

       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 ( NodeCaption LIKE '%-DS%' 
      OR NodeCaption LIKE '%VSS%'
      OR NodeCaption LIKE '%HQ-CR%'


GROUP BY
  NodeCaption, AgentIP
ORDER BY
  NodeCaption

You may be able to remove the following lines as we require this for a particular client:

AND ( NodeCaption LIKE '%-DS%'
OR NodeCaption LIKE '%VSS%'
OR NodeCaption LIKE '%HQ-CR%'

And it looks like this:

You can run this daily, weekly, monthly as needs fit and then manipulate the results in Excel to show changes between current and last run.