I want a report on port usage for 90 days is it possible to add this in the query ?
SELECT
[N].[Caption] AS Caption,
[N].[Vendor] AS Vendor,
[N].[IP_Address] as IPadress
, 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
