This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

  • 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())

  • Here's the query with only one join emoticons_happy.png

    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

  • 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?

  • 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

  • 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