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

reporting on inactive ports

We have recently purchased UDT to compliment NPM and would like to report on ports that are live but have been inactive for xx days so they can be shutdown. Is this possible and if so what criteria within report writer do i need to make it work - Thanks


NPM 10.5, UDT 3.0.0

5 Replies
Level 14

Here's the query with only one join

SELECT UDT_UnUsedPorts.NodeID

      ,UDT_UnUsedPorts.Caption

      ,UDT_UnUsedPorts.DNS

      ,UDT_UnUsedPorts.IP_Address

      ,UDT_UnUsedPorts.PortID

      ,UDT_UnUsedPorts.Name

      ,UDT_UnUsedPorts.PortDescription

      ,UDT_UnUsedPorts.DaysUnused

      ,UDT_Port.OperationalStatus

      ,UDT_Port.AdministrativeStatus

  FROM UDT_UnUsedPorts

  JOIN UDT_Port ON UDT_Port.PortID = UDT_UnUsedPorts.PortID

  WHERE UDT_Port.OperationalStatus =1 AND UDT_Port.AdministrativeStatus =1 AND (UDT_UnUsedPorts.DaysUnused = 'Never' OR UDT_UnUsedPorts.DaysUnused > 5)

It uses the view UnusedPorts that has a column "DaysUnused". Replace the "5" with the number of days. Additionally, this shows ports that have never been used. If you want to filter them out, the last line of the query should be like this:

WHERE UDT_Port.OperationalStatus =1 AND UDT_Port.AdministrativeStatus =1 AND (UDT_UnUsedPorts.DaysUnused != 'Never' AND UDT_UnUsedPorts.DaysUnused > 5)

HTH,

Michal

Brilliant, works well thanks. I made a minor tweak to suit my own particular requirement  UDT_Port.OperationalStatus =2 AND UDT_Port.AdministrativeStatus =1

where 1=UP and 2=DOWN

0 Kudos

On further investigation whiclst being a vast improvement on what had gone before I have found this report not to be as accurate as hoped, as when checking against the "show interface" on the switch there is disparity between the results. I have raised this with Soalrwinds and the reason I am seeing disparity between the information in the report and the switchport interface is because the report info comes from the poller so depends on poll times and when a device was last seen on an interface which is different to the actual switchport info which is realtime

The dev team are looking at reporting on the actual OID to get this real time information which will make the report accurate and reliable.


It is important for the information to be accurate as I would soon become unpopular if i was to start shutting down the wrong ports!!


I will be posting a the feature request but to help you search it is - Feature Request - 520439: Port usage data gathered from device


Thanks



0 Kudos

thisAny this is great.  Any chance we can get this ability as a feature in the next version.  As both a GUI tool and a report?

0 Kudos

No one likes UDT custom SQL cause it has at least 6 or 7 joins to be useful at all.

I would start here but you will need joins to get from the portid to the node id

SELECT [FirstSeen]

      ,[LastSeen]

      ,[PortID]

      ,[EndpointID]

      ,[VlanID]

      ,[ConnectionType]

      ,[ID]

  FROM UDT_PortToEndpointHistory

  WHERE lastseen < DATEADD(day, -90, GETDATE())