I found a code in Thwack that give 95th Percentile for the last month. However, our network folks want the last 30 days instead. We tried to tweak the code but we're getting NULL values in the 95th_Percentile_IN, 95th_Percentile_Out and Max_95th columns. So, I'd appreciate if you guys could tell me what changes are needed, so the report can work. See below...
DECLARE @Today DATETIME = GETDATE()
DECLARE @startTimeT1 DATETIME;
DECLARE @endTimeT1 DATETIME;
DECLARE @intFlag INT;
SET @intFlag = 30
SET @startTimeT1 = CAST(getdate() AS DATETIME)
SET @endTimeT1 = CAST(getdate()- @intFlag AS DATETIME)
set nocount on
create table #InterfaceIds24 (
InterfaceID int NOT NULL,
CONSTRAINT [PK_#InterfaceIds24] PRIMARY KEY CLUSTERED
(
InterfaceID ASC
)
)
insert into #InterfaceIds24
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
WHERE A.DateTime >= @startTimeT1 AND A.DateTime <= @endTimeT1
SELECT TOP 20
@startTimeT1
, @endTimeT1
,Nodes.NodeID, Interfaces.InterfaceID
,Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceSpeed,
Round(dbo.GetInBps95th(InterfaceID, @startTimeT1, @endTimeT1) / Interfaces.InterfaceSpeed * 100,1) AS '95th_Percent_In',
Round(dbo.GetOutBps95th(InterfaceID, @startTimeT1, @endTimeT1) / Interfaces.InterfaceSpeed * 100,1) AS '95th_Percent_Out',
Round(dbo.GetMaxBps95th(InterfaceID, @startTimeT1, @endTimeT1)/ Interfaces.InterfaceSpeed * 100,1) AS 'Max 95th'
FROM Nodes
INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
WHERE
(Interfaces.InterfaceSpeed != 0)
AND (Nodes.Status != '9')
ORDER BY '95th_Percent_IN' DESC, Interface_Caption
DROP TABLE #InterfaceIds24
