11 Replies Latest reply on Jan 15, 2016 7:36 AM by grossjd

    Custom report - Unused ports in XX days OR never, admin/oper status

    punk

      I need a report that will tell me the number of ports that haven't been used in XX days OR never, taking into account administrative and operational status. For instance, I need to view all ports that have not seen any activity in 30 days or more, OR never, and that are administratively up, but operationally down.

       

      I wanted to build it myself and made significant progress, but always ran into roadblocks and I just don't have the time to re-learn SQL or enough knowedlge of the Orion DB to build the query from scratch. I'm also unable to properly modify this report (http://thwack.solarwinds.com/docs/DOC-143398) provided by Mav to acomplish what I need. I can build seperate reports for each, but then that data would need to be taken from UDT and compared to obtain the final end-result, which isn't very efficient. I checked the Content section of the UDT...area...here, and there are no customr reports uploaded.

       

      Any help would be appreciated...

       

      Thanks,

        • Re: Custom report - Unused ports in XX days OR never, admin/oper status
          punk

          I wrote this simple query, which works well:

           

          SELECT
           UDT_UnUsedPorts.NodeID AS NodeID,
           UDT_UnUsedPorts.PortID AS PortID,
           UDT_UnUsedPorts.DNS AS DNS,
           UDT_UnUsedPorts.IP_Address AS IP_Address,
           UDT_UnUsedPorts.Name AS Name,
           UDT_UnUsedPorts.PortDescription AS Port_Description,
           UDT_UnUsedPorts.DaysUnused AS DaysUnused,
           UDT_Port.PortID AS PortID2,
           UDT_Port.AdministrativeStatus AS AdminStatus,
           UDT_Port.OperationalStatus AS OperStatus
          FROM UDT_UnUsedPorts
          INNER JOIN UDT_Port
           ON UDT_Port.PortID = UDT_UnUsedPorts.PortID
          WHERE
           UDT_UnUsedPorts.DNS LIKE 'switch%'
          


          (I removed the WHERE statements that limit the ouput to DaysUnused >= 30, etc. for this example)

           

          ...except that I've noticed UDT_UnUsedPorts.DaysUnused is often incorrect. In this example, I queried data for a specific switch, then compared the results of that query to the switch itself. Here's a snippit, slightly sanitized:

           

          NodeID PortID DNS     Port DaysUnused AdminStatus OperStatus
          1  1666 switch.domain.com Fa0/1 0   1   1
          1  1674 switch.domain.com Fa0/9 16   1   1
          1  1676 switch.domain.com Fa0/11 17   1   1
          1  1678 switch.domain.com Fa0/17 4   1   1
          1  1680 switch.domain.com Fa0/19 18   1   1
          1  1681 switch.domain.com Fa0/20 16   1   1
          1  1687 switch.domain.com Fa0/14 2   1   1
          1  1690 switch.domain.com Gi0/1 0   1   1
          1  1668 switch.domain.com Fa0/3 Never  1   2
          1  1670 switch.domain.com Fa0/5 Never  1   2
          1  1671 switch.domain.com Fa0/6 Never  1   2
          1  1672 switch.domain.com Fa0/7 Never  1   2
          1  1673 switch.domain.com Fa0/8 Never  1   2
          1  1675 switch.domain.com Fa0/10 Never  1   2
          1  1677 switch.domain.com Fa0/12 Never  1   2
          1  1679 switch.domain.com Fa0/18 Never  1   2
          1  1682 switch.domain.com Fa0/21 Never  1   2
          1  1684 switch.domain.com Fa0/23 Never  1   2
          1  1685 switch.domain.com Fa0/24 Never  1   2
          1  1689 switch.domain.com Fa0/16 Never  1   2
          1  1691 switch.domain.com Gi0/2 Never  1   2
          

           

          If you look at the switch, you can tell that the view's output is way off. Using Fa0/9 as an example, UDT claims it has been unused for 16 days. But it also says that the port is up/up, which is true. If you check the switch, not only is the port up/up but it's currently passing traffic. I'm probably just doing something wrong, but I'm getting incredibly frustraited trying to dig through the database to build this report.

          • Re: Custom report - Unused ports in XX days OR never, admin/oper status
            punk

            Sorry for the delay, folks. I had a bout of temporary amnesia regarding this thread. The development team wrote a query to adjust our database schema, which resolved the issue completely. This fix is still slated for UDT v2.1, last I heard. I would post the schema update, but I'm not sure if that's Kosher.

              • Re: Custom report - Unused ports in XX days OR never, admin/oper status
                cvachovecj

                Please post the fix -- the more people can find it the better.

                 

                Thanks,

                Jiri

                  • Re: Custom report - Unused ports in XX days OR never, admin/oper status
                    punk

                    OK. For reference, this is the final query that I wrote. It's been a decade since I've written a SQL query, so I'm sure that a more efficient one could be written, but it's worked well for us so far:

                     

                    SELECT
                     UDT_UnUsedPorts.NodeID AS NodeID,
                     UDT_UnUsedPorts.PortID AS PortID,
                     UDT_UnUsedPorts.DNS AS DNS,
                     UDT_UnUsedPorts.IP_Address AS IP_Address,
                     UDT_UnUsedPorts.Name AS Name,
                     UDT_UnUsedPorts.PortDescription AS Port_Description,
                     UDT_UnUsedPorts.DaysUnused AS DaysUnused,
                     UDT_Port.PortID AS PortID2,
                     UDT_Port.AdministrativeStatus AS AdminStatus,
                     UDT_Port.OperationalStatus AS OperStatus
                    FROM UDT_UnUsedPorts
                    INNER JOIN UDT_Port
                     ON UDT_Port.PortID = UDT_UnUsedPorts.PortID   
                    WHERE
                     (
                      ISNUMERIC(UDT_UnUsedPorts.DaysUnused) = 1
                      AND CAST(UDT_UnUsedPorts.DaysUnused AS INT) >= 30
                      OR UDT_UnUsedPorts.DaysUnused = 'Never'
                     )
                     AND UDT_Port.AdministrativeStatus = 1
                     AND UDT_Port.OperationalStatus = 2
                    


                    And here is the schema fix from SolarWinds. I take no responsibility if your database ends up cross-eyed once you apply it. Blame Jiri. ;-P

                    ALTER VIEW [dbo].[UDT_UnUsedPorts]
                    AS
                    SELECT     
                      Nodes.NodeID
                      , Nodes.Caption
                      , Nodes.DNS
                      , Nodes.IP_Address
                      , UDT_Port.PortID
                      , UDT_Port.Name
                      , UDT_Port.PortDescription
                      , Case 
                       when DATEDIFF(day, P2E.LastSeen, GETUTCDATE())= 2147483647 then 'Never'
                       else convert(varchar,DATEDIFF(day, P2E.LastSeen, GETUTCDATE()))
                      End  AS DaysUnused
                     FROM UDT_Port 
                     INNER JOIN Nodes ON UDT_Port.NodeID = Nodes.NodeID 
                     LEFT OUTER JOIN (
                      SELECT DISTINCT PortID, LastSeen 
                      FROM (
                       SELECT PortID, LastSeen, MAX(ISNULL(LastSeen, '9999-12-31')) OVER (PARTITION BY PortID) Max_LastSeen 
                       FROM UDT_PortToEndpoint
                      ) MostRecent WHERE ISNULL(LastSeen, '9999-12-31') = Max_LastSeen
                     ) P2E ON UDT_Port.PortID = P2E.PortID
                     WHERE P2E.LastSeen IS NOT NULL
                    UNION ALL
                    SELECT     
                      Nodes.NodeID
                      , Nodes.Caption
                      , Nodes.DNS
                      , Nodes.IP_Address
                      , UDT_Port.PortID
                      , UDT_Port.Name
                      , UDT_Port.PortDescription
                      , 'Never' AS DaysUnused
                     FROM Nodes 
                     INNER JOIN UDT_Port ON Nodes.NodeID = UDT_Port.NodeID 
                     LEFT OUTER JOIN UDT_PortToEndpoint ON UDT_Port.PortID = UDT_PortToEndpoint.PortID
                     WHERE UDT_PortToEndpoint.EndpointID IS NULL
                    
                    GO
                    
                • Re: Custom report - Unused ports in XX days OR never, admin/oper status
                  grossjd

                  Sorry to bump this old discussion, but if the schema fix was supposed to be rolled into UDT v2.1, it never made it.  I have UDT v3.2.2 and the UDT_UnusedPorts view does not look anything like the fix you posted above.

                   

                  Was there a patch issued to correct this view or do we just need to do it manually?