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
Here's the query with only one join
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)
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
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
WHERE lastseen < DATEADD(day, -90, GETDATE())
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.