5 Replies Latest reply on Sep 18, 2013 2:48 AM by netman_ma

    reporting on inactive ports

    netman_ma

      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

        • Re: reporting on inactive ports
          bluefunelemental

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

          1 of 1 people found this helpful
          • Re: reporting on inactive ports
            michalB

            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

            1 of 1 people found this helpful
              • Re: reporting on inactive ports
                jspanitz

                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?

                • Re: reporting on inactive ports
                  netman_ma

                  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

                    • Re: reporting on inactive ports
                      netman_ma

                      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