I am using the below script that was supplied on this form. It works Great! However, the script is a snap shot of the network as it stands at the time of running the script. Is there a way to report on port usage for the past 30 days? For example some ports on a switch are active for a limited time at night once a week. These ports are in turn missed when the script is run for a 30 day usage report.
SELECT
UPPER ([N].[Caption]) AS Caption
, ([N].[City])
, ([N].[Country])
, ([N].[Refresh])
,([N].[Device_type])
, 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))
LEFT 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])
LEFT 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])
LEFT 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])
LEFT 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])
LEFT 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])
LEFT 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
