cancel
Showing results for 
Search instead for 
Did you mean: 
emunizaga
Level 9

UDT Unused Ports Report

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?

edit resource.png\

add-column.png

6 Replies
nickzourdos
Level 14

Re: UDT Unused Ports Report

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.

0 Kudos
nickzourdos
Level 14

Re: UDT Unused Ports Report

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.

emunizaga
Level 9

Re: UDT Unused Ports Report

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
0 Kudos
nickzourdos
Level 14

Re: UDT Unused Ports Report

This looks much different than the report from your original post, which is what I wrote the query for. What is the data in the second column, is it a port description?

0 Kudos
emunizaga
Level 9

Re: UDT Unused Ports Report

This is the output from running the command "show interface links" on a cisco device.  As you can see, UDT report does not match the times reported in using this command.  Hence this report is not accurate.

0 Kudos
nickzourdos
Level 14

Re: UDT Unused Ports Report

What is your retention period set to? You can check this under Settings > All Settings > UDT Settings > Data Retention

0 Kudos