6 Replies Latest reply on Jul 23, 2019 1:57 PM by nickzourdos

    UDT Unused Ports Report

    emunizaga

      I want to edit an existing report in order to try to add in some more columns.  I want to add the MAC address and the VLAN.  I click on the plus tap but can't find where to add these.  Can anyone help?

       

      \

        • Re: UDT Unused Ports Report
          nickzourdos

          I'm doing a little digging and it looks like the MAC and VLAN are stored in the "UDT.Port" table in the database, but the info you've already got in this report is stored in the "UDT.UnusedPorts" table. I'm trying to write some SWQL that would build this report for you, but I'm running into some roadblocks. I'll ask some SWQL experts and see if we can get something whipped up.

          • Re: UDT Unused Ports Report
            nickzourdos

            Thanks to wluther this SWQL query seems to pull exactly what you're looking for:

             

            SELECT
            up.Caption as NodeName
            ,p.Node.IPAddress
            ,p.Name as PortName
            ,p.PortDescription
            ,up.DaysUnused
            ,p.MACAddress
            ,v.VlanID as VLAN
            FROM Orion.UDT.Port AS p
            
            JOIN Orion.UDT.UnusedPorts AS up ON up.NodeID=p.NodeID AND up.PortID=p.PortID
            JOIN Orion.UDT.VLAN AS v ON v.PortID=p.PortID
            
            WHERE p.Node.IPAddress = 'x.x.x.x'
            AND up.DaysUnused != 'Never'
            AND up.DaysUnused > 0
            
            ORDER BY DaysUnused

             

            Tweak the filtering as needed, I've got it set to only produce data for a single node, and to exclude any ports that have "never" been unused or have an DaysUnused value of zero.

             

            Does this work for you? Keep in mind that in the latest version of Orion (v2019.2) custom SWQL resources are broken... that bug will be addressed in Hotfix 2.

              • Re: UDT Unused Ports Report
                emunizaga

                When comparing this to running the command "show interface links", this report does not give me the correct information.  How can i get something to look like this?  The standar UDT report does not match running this cisco command to show the same info.

                 

                PortName           Down Time    Down Since
                Gi1/36   9263D1        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/37   9269D3        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/38   9270D1        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/39   9270D3        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/40   9271D1        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/41   9271D3        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/42   9272D1        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/43   9272D3        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/44   9273D1        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/45   9273D3        49 weeks, 4 days, 22 hours, 32 minutes 47 secs  19:19:43  Wed Aug 8 2018
                Gi1/46   9370D1        00 secs
                Gi1/47   9371D1        00 secs
                Gi1/48   9372D1        00 secs