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.
Top Replies
-
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… -
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.) -
Hi, Replace all occurrences of "right join" with "left join", the report will work then. Or you can try this modified query (for UDT 2.5+), it will give what you need though in a slightly different way…
Parents
-
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)
-
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. -
Thanks njoylif. I really appreciate your offer to help. I was thinking rather than start another post for the additional report, what I’m looking for is a slight variation of report you have already provided. I’m looking for a report that provides the same used/unused port stats but broken down into used/unused ports parsed by Ethernet port name (i.e., Port Name beginning with Gig, Fa, Ten) per device.Caption TotalPortCount Gig-ActivePorts Gig-UnusedPorts Fa-ActivePorts Fa-UnusedPorts Ten-ActivePorts Ten-UnusedPorts %TotalPortsUsed
-
njoylif,
I took a stab using your report as the model and created a used/unused ports by (Cisco) ethernet port name. It works to a certain extent...it runs. I call that a success.
Unfortunately, it only returns results if all 3 type ethernet ports (FastEthernet, Gigibit, TenGigibit) are present for a given switch. Since we have no single switch that has all 3 port name types, no results are returned. I came to this conclusion through process of eliminating a port name type. For instance, when I remove the TE statements and run report, all the switches with FE&GE ports are reported but switches with all GE ports are not reported in results. We have a mix of switches that have only FE only ports, FE&GE, GE only and some GE/10GE. I can't quite put my finger on it but I suspect the primary WHERE clause may be the issue although it seems correct to me. Any suggestions/thoughts?SELECT
UPPER ([N].[Caption]) AS Caption
, ISNULL([FEPortsTotal].[PortCount], 0) AS FEPortCount
, ISNULL([FEPortsActive].[PortCount], 0) AS FEActivePortCount
, (ISNULL([FEPortsTotal].[PortCount], 0) - ISNULL([FEPortsActive].[PortCount],0)) AS FEUnusedPorts
, CASE
WHEN ((ISNULL([FEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([FEPortsActive].[PortCount], 0) = 0)) THEN 0
ELSE ((ISNULL([FEPortsActive].[PortCount], 0) * 100) / ISNULL([FEPortsTotal].[PortCount], 0))
END AS [% FEPortsUsed], ISNULL([GEPortsTotal].[PortCount], 0) AS GEPortCount
, ISNULL([GEPortsActive].[PortCount], 0) AS GEActivePortCount
, (ISNULL([GEPortsTotal].[PortCount], 0) - ISNULL([GEPortsActive].[PortCount],0)) AS GEUnusedPorts
, CASE
WHEN ((ISNULL([GEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([GEPortsActive].[PortCount], 0) = 0)) THEN 0
ELSE ((ISNULL([GEPortsActive].[PortCount], 0) * 100) / ISNULL([GEPortsTotal].[PortCount], 0))
END AS [% GEPortsUsed], ISNULL([TEPortsTotal].[PortCount], 0) AS TenPortCount
, ISNULL([TEPortsActive].[PortCount], 0) AS TenActivePortCount
, (ISNULL([TEPortsTotal].[PortCount], 0) - ISNULL([TEPortsActive].[PortCount],0)) AS TenUnusedPorts
, CASE
WHEN ((ISNULL([TEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([TEPortsActive].[PortCount], 0) = 0)) THEN 0
ELSE ((ISNULL([TEPortsActive].[PortCount], 0) * 100) / ISNULL([TEPortsTotal].[PortCount], 0))
END AS [% TenPortsUsed]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) AND ([P].[Name] LIKE 'Fa%')
GROUP BY [P].[NodeID]
) AS FEPortsTotal ON ([FEPortsTotal].[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) AND ([P].[Name] LIKE 'Fa%')
GROUP BY [P].[NodeID], [P].[OperationalStatus]
HAVING ([P].[OperationalStatus] = 1)
) AS FEPortsActive ON ([FEPortsActive].[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) AND ([P].[Name] LIKE 'Gi%')
GROUP BY [P].[NodeID]
) AS GEPortsTotal ON ([GEPortsTotal].[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) AND ([P].[Name] LIKE 'Gi%')
GROUP BY [P].[NodeID], [P].[OperationalStatus]
HAVING ([P].[OperationalStatus] = 1)
) AS GEPortsActive ON ([GEPortsActive].[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) AND ([P].[Name] LIKE 'Te%')
GROUP BY [P].[NodeID]
) AS TEPortsTotal ON ([TEPortsTotal].[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) AND ([P].[Name] LIKE 'Te%')
GROUP BY [P].[NodeID], [P].[OperationalStatus]
HAVING ([P].[OperationalStatus] = 1)
) AS TEPortsActive ON ([TEPortsActive].[NodeID] = [NC].[NodeID])WHERE (ISNULL([FEPortsTotal].[PortCount], 0) > 0)
OR (ISNULL([GEPortsTotal].[PortCount], 0) > 0)
OR (ISNULL([TEPortsTotal].[PortCount], 0) > 0)
ORDER BY Caption -
-
-
-
Nice work Bedrich! My original query works great with changing the joins. However, I love your revised query. It's actually what I preferred the report to be but couldn't figure out how to write the query. My query ends up taking up way too much report landscape real estate. You da'man! I owe you a beer.
Thank you!
-
Nice work Bedrich! My original query works great with changing the joins. However, I love your revised query. It's actually what I preferred the report to be but couldn't figure out how to write the query. My query ends up taking up way too much report landscape real estate. You da'man! I owe you a beer.
Thank you!
