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

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.

Hi

If you think that the unused ports table contains wrong data, you may want to open a support case.

Regards,

Jiri

Yep, already done. Still looking/hoping for another solution, though.

Please post all the results (from support and yours).

Jiri

Will do, not much info yet. Support has reached out to the developers to find out more.

The development team found an issue with the UDT_UnUsedPorts view. They are currently looking into a patch vs. including it in the UDT v2.1 release.

Punk,

Can you please post the support case #?  I'll then open another support case and reference your info.  It might float up the stack of bugs that get fixed sooner.

Thanks,

Clint

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.

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

Thanks,

Jiri

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