UDT Switch Port Utilization Report

The built-in report for switch port utilization % is an over-time report. I can't figure out how to modify this to just show me a report of current/last polling % for each monitored device. Any hints? What I'm looking for is similar to the default UDT top 10 dashboard widget but when I edit it gives me no hints or configuration options on how to create something similar. Just looking for something like:

Device Name | # Ports Monitored | # Ports in-use | % Used

And only for specific devices. Any ideas?

TIA

  • Something like...

    SELECT
    
        [N].[Caption] AS Caption
    
        , ISNULL([PortsTotal].[PortCount], 0) AS PortCount
    
        , ISNULL([PortsActive].[PortCount], 0) AS ActivePortCount
    
        , (ISNULL([PortsTotal].[PortCount], 0) - ISNULL([PortsActive].[PortCount],0)) AS UnusedPorts
    
        , CASE
    
            WHEN ((ISNULL([PortsTotal].[PortCount], 0) = 0) OR (ISNULL([PortsActive].[PortCount], 0) = 0)) THEN 0
    
            ELSE ((ISNULL([PortsActive].[PortCount], 0) * 100) / ISNULL([PortsTotal].[PortCount], 0))
    
        END AS [% PortsUsed]
    
    FROM dbo.Nodes AS N
    
    INNER JOIN dbo.UDT_NodeCapability AS NC ON (([N].[NodeID] = [NC].[NodeID]) AND ([NC].[Capability] = 2))
    
    RIGHT JOIN (
    
        SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
    
        FROM dbo.UDT_Port AS P
    
        WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1)
    
        GROUP BY [P].[NodeID]
    
    ) AS PortsTotal ON ([PortsTotal].[NodeID] = [NC].[NodeID])
    
    RIGHT JOIN (
    
        SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
    
        FROM dbo.UDT_Port AS P
    
        WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1)
    
        GROUP BY [P].[NodeID], [P].[OperationalStatus]
    
        HAVING ([P].[OperationalStatus] = 1)
    
    ) AS PortsActive ON ([PortsActive].[NodeID] = [NC].[NodeID])
    
    WHERE (ISNULL([PortsTotal].[PortCount], 0) > 0)
    
    ORDER BY [% PortsUsed] DESC, [ActivePortCount] DESC, [UnusedPorts] ASC

    the report looks like this (caption is the first column - cropped out)

    port usage.png

  • So this works... at least the query works. But when I put it in a report it fails. I set it as an Advanced Database Query, set it as SQL, and even the PREVIEW RESULTS works great. But when I put it in the report I get an error "Report resource failed to properly initialize" and then the details have "Error setting up report cell during rendering 'Custom Table'  - Object reference not set to an instance of an object.  Any suggestions for getting past this error?