Unless I'm missing it, there isn't a OOTB UDT report (v2.5) that provides Used Ports/percentage, Free Ports/percentage, Total Ports per device like the UDT Ports in Use Overview graph below. Has anyone developed such a report they would be willing to share? I'm a UDT newbie and not strong in creating Advanced SQL reports either.
I have no idea where I got this from - I didn't write it. I'm sure someone on thwack wrote it, but here's the SQL that I think you want.
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)
The one that jspanitz posted above and attached a file for works well for me, but I can't get it to save for some reason, comes up with an error for NodeID. So as far as I can tell, I can only run it on the server itself and not save the report so it can be accessed form the web. I know I'm missing something but I'm not sure what.
I tried just copying/pasting in the text but that only returns NodeIDs and not the names of the nodes. But if I open up the report file he attached in the Orion Report Editor it looks great.
Edit: Also, the counts do not look right for stacked switches. See below, with names blurred out, these are all 48 port switches Cisco 3750x, so this doesn't look right.
Here is the image for the prior post. (Moderator, why can't I paste a graphic when creating initial post? You can paste it but it is not saved with post.)
I have no idea where I got this from - I didn't write it. I'm sure someone on thwack wrote it, but here's the SQL that I think you want.
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)
Thanks for sharing the SQL script. 
Awesome! Thank you so much njoylif! Just what I needed and works great!
P.S., watch out! Another UDT report request post forthcoming.
I'll do what I can - when I can - but bring it on; just may need to be patient.
