Looking for query/report to show switchports that have not been used since <date>

Even better would be if we could setup solarwinds to automatically turn off ports that have not been utilized in over 3 months, but just being able to pull up a report would be a great start.

I believe this sql statement should pull the information that you want, any interfaces with 0 in and out packets in more than 3 months that are not unplugged (status 4) or shutdown (status 10)

SELECT DISTINCT n.Caption, i.InterfaceName FROM Interfaces AS i JOIN Nodes AS n ON i.NodeID = n.NodeID JOIN InterfaceTraffic_Daily AS it ON i.InterfaceID = it.InterfaceID WHERE (it.DateTime <= DATEADD(MONTH,-3,GETDATE())) AND ((i.Status != 4) OR (i.Status != 10)) AND ((it.In_TotalPkts = 0) AND (it.Out_TotalPkts = 0)) GROUP BY n.Caption, i.InterfaceName

-EDIT-

although i just realized that i missed the last line of your message about what you ideally would like to see but they could be added in in the select portion

Have you considered UDT (User Device Tracker)? You can see a sample UDT report that is pretty close to what you are after

http://oriondemo.solarwinds.com/Orion/Report.aspx?Report=UDT_Unused_Ports&ReturnTo=aHR0cDovL29yaW9uZGVtby5zb2xhcndpbmRzL…

Hello!  I'm interested in this type of report as well.  We have over 700 switches in our environment and that number will soon almost double.  Has there been any success on this front?

Thanks!

As holyguacamole mentions, have you got UDT?

Go get UDT then add this Custom Query to a node details page. This will provide details from the nodes,interfaces, and UDT ports tables for the node you are viewing but the last column is the date when traffic was last seen on that interface.

SELECT

Interfaces.InterfaceName as InterfaceName

, Interfaces.detailsurl as [_LinkFor_InterfaceName]

, '/Orion/images/StatusIcons/Small-' + Interfaces.StatusIcon AS [_IconFor_InterfaceName]

, Interfaces.InterfaceAlias as Caption

, Interfaces.detailsurl as [_LinkFor_Caption]

, s1.ShortDescription AS [Admin Status]

, s2.ShortDescription AS [Operational Status]

, Interfaces.unpluggable  as [Unpluggable]

--, interfaces.customproperties.Alert_on_down

, NodePortInterfaceMap.VlanID

--, PortToEndpointCurrent.VlanID

, IPAddressCurrent.Endpoint.MACAddress

, IPAddressCurrent.IPAddress

, IPAddressCurrent.DNSNames.DNSName

, (SELECT TOP 1 InterfaceTraffic.DateTime AS [ColumnA] FROM Orion.NPM.InterfaceTraffic WHERE Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.TotalPackets <> 0 ORDER BY InterfaceTraffic.DateTime desc)  AS [Last Seen]

FROM Orion.NPM.Interfaces

LEFT OUTER JOIN Orion.StatusInfo s1 on Interfaces.AdminStatus = s1.StatusID

LEFT OUTER JOIN Orion.StatusInfo s2 on Interfaces.OperStatus = s2.StatusID

LEFT OUTER JOIN Orion.UDT.Port on Port.NodeID = Interfaces.NodeID AND Port.Name = Interfaces.InterfaceName

LEFT OUTER JOIN Orion.UDT.PortToEndpointCurrent on Port.PortID = PortToEndpointCurrent.PortID AND ConnectionType = 1

LEFT OUTER JOIN Orion.NodePortInterfaceMap on Interfaces.InterfaceIndex = NodePortInterfaceMap.IfIndex AND NodePortInterfaceMap.PortType = 1 AND NodePortInterfaceMap.NodeID = ${NodeID}

LEFT OUTER JOIN Orion.UDT.IPAddressCurrent on PortToEndpointCurrent.EndpointID = IPAddressCurrent.EndpointID

WHERE Interfaces.NodeID = ${NodeID}

ORDER BY Interfaces.Index

You can have a similar report from Network Configuration Manager, menu Reports, view Report, Inventory, select Interfaces and then Modify Report and add fields like Admin & Operational status, Last Change, etc.