I found a code in Thwack that give 95th Percentile for the last month. However, our network folks want the last 30 days instead.
By the way, I know that there is a report already for business hours at 95th Percentile Circuit Report based on local site business hours
However, that report is for based on Monthly time frame instead of the "last 30 days", which what my team needs. Given that we're not SQL savvy, we could not figure out the logic that needed tweaking. Also, the report required additional changes to our setup (GMT time, Custom Properties, etc.). So, we thought a simpler report might be best. We then found another report on Thwack and tried to tweak the code. Unfortunately, when we run the script, 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
I would very much appreciate your insights on this matter.