This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Port usage report per device

FormerMember over 12 years ago

I created this basic SQL and published it via report writer for UDT

select n.Caption, u.PortCount, u.ActivePortCount, u.PortCount - u.ActivePortCount as [UnusedPorts], ROUND((cast(u.ActivePortCount AS float)/u.PortCount) * 100, 1) as [% FreePorts] from dbo.UDT_Switch u, dbo.Nodes n
where u.NodeID = n.NodeID

Kind Regards

james

Top Replies

dclick over 10 years ago in reply to Bedrich.Michalek +1

AS newkidd2 stated, looks great in report writer when I test it, but I also get the Missing field notice when saving. Im not a SQL guy, so I dont know how to rebuild your query to add the missing Nod

0 dclick over 11 years ago

Thanks for sharing. Quesiton - I dont have a table "UDT_Switch". Could that be something else?
Cancel
Vote Up 0 Vote Down

Cancel
0 Bedrich.Michalek over 11 years ago in reply to dclick

Database schema changed between UDT 1.0 (1.0.1) and UDT 2.0. UDT_Switch and UDT_Router tables were replaced by UDT_NodeCapability table.
Following query should give you the same result for UDT 2.0 though I didn't test it too much so it's possible you'll have to do some changes. Remove the [IsMonitored] condition to include all ports (both monitored and unmonitored), and remove the [Flag] condition to include deleted (1), missing (2) or deleted and missing (3) ports - or change it as you need.
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
Cancel
Vote Up 0 Vote Down

Cancel
0 mr.e over 10 years ago in reply to Bedrich.Michalek

Hello,
First of all, many thanks for posting the SQL script. I ran the SQL report that you posted and it seemed to work. However, when I attempted to save the report, I received the following error:
Detected usage of a macro, which links to a non-existent field in the report. Therefore WebURL, which uses this macro, will not be generated correctly.
Missing fields: NodeID
After this, I checked and confirmed that the report does show up in the Reports page. Unfortunately, I am not very skilled in SQL scripting so I did not know how to correct the script in order to avert the problem.
Any ideas????
Cancel
Vote Up 0 Vote Down

Cancel
0 dclick over 10 years ago in reply to Bedrich.Michalek

AS newkidd2 stated, looks great in report writer when I test it, but I also get the Missing field notice when saving. Im not a SQL guy, so I dont know how to rebuild your query to add the missing NodeID
Cancel
Vote Up +1 Vote Down

Cancel
0 michalB over 10 years ago in reply to dclick

Thanks for reporting the issue. It is caused by the fact that the field Caption in Field Formatting tab has a Web URL set and the Web URL contains a macro ${NodeID}. The Web URL converts the text to a hyperlink with the NodeID, but the NodeID is not in the SQL query, and therefore the value cannot be provided. You can either delete the Web URL for the Caption column, or you can add the NodeID column in the SQL query like this:
SELECT
    [N].[NodeID]
    , [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
If you do not want the NodeID column to be visible in the report, you can hide it in the Field Formatting tab by checking the "Hidden Field" checkbox. I tested the report with the query above and it works on my machine
Cancel
Vote Up 0 Vote Down

Cancel