Orion NPM resolves the 95th percentile on the fly based on the data that is available within the time frame that is selected, and it is not a column that holds that information.
Hope that clarifies.
Ok thanks that makes sense, I have I got a little help with this part of the report, see the statement below:
MAX(dbo.GetInBps95th(InterfaceTraffic.InterfaceID, @StartDate, @EndDate)) AS Maxbps_In95
Am I right in assuming that this part of the statement ''dbo.GetInBps95th(InterfaceTraffic.InterfaceID'' instructs Orion to run the 95th percentile calc on the data within InBps column in the table Interface traffic?
I am not sure if it is that simple, as I do remember that the 95th percentile reports that are packaged with NPM 9.5 and above are is a whole chunk of SQL query coding.
Also, are you able to share with us where you get that statement?
Note: Frankly speaking, I am not a pro at setting up SQL queries
Your help gave me enough understanding to get the report working the way I want it to, I think lol.
I have been working on this report on and off for over a year I'd say I've had input from SQL guys in my company, I've poached a bit from the Orion prepack reports and I think one or two users on thwack also contributed.
I can post the SQL in full if you'd like to see it. It is based of of a custom property I have assisgned to interfaces. The report should show interface utilisation recv & Xmit + 95th percentile, with a time period of Mon-Fri between 9-5 over the last 30 days.
Thanks for your update ;)
Yes, I think uploading sharing the Advanced SQL report with the users of the community will be most helpful!
And I might learn a thing or 2 from you also.
Ok, so I'll post it here rather than the content sharing zone as I still need to validate the results are accurate first.
So here it is in all it's glory It's based on a custom property that I have assisgned to the interfaces I want to be included in the report, the custom property is called 'brUplink256'.
The scope of the report is as follows:
Time period = Mon - Fri from 9am- 4pm for the last 7 days. The range of the report can be increased but I find it runs quite slowly even at 7 days.
It will display the fields:
Node name - Node Location - Link Speed - Avg Xmit bps - Avg Recv bps - Peak Xmit bps
Peak recv bps - Xmit percent Util - Recv percent Util - Circuit Utilisation
Xmit 95th percentile
Recv 95th percentile
Like I mentioned I'm trying to verify the accuracy of this report and format it to my tastes. If anyone does edit it or modify it please do share with us as I'm no expert in SQL.
Anyway happy reporting, have fun
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)
SET @EndDate = GetDate()
SELECT TOP 10000
Nodes.Caption AS NodeName,
Nodes.Location AS Location,
Interfaces.InterfaceSpeed AS Interface_Speed,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
When InBandwidth+OutBandwidth=0 Then 0
When InBandwidth=0 Then
(Out_Averagebps/OutBandwidth) * 100
When OutBandwidth=0 Then
(In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit,
When 0 Then 0
Else (dbo.GetoutBps95th(InterfaceTraffic.InterfaceID, @StartDate, @EndDate)/OutBandwidth) * 100 End) AS Xmit_95th_Percent,
When 0 Then 0
Else (dbo.GetInBps95th(InterfaceTraffic.InterfaceID, @StartDate, @EndDate)/InBandwidth) * 100 End) AS Recv_95th_Percent
INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))
INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)
( DateTime > (GetDate()-7) )
(Uplinks = 'BrUplink2M') AND
NOT (DATEPART(weekday, DateTime) = 7)) AND
NOT (DATEPART(weekday, DateTime) = 1)) AND
NOT (Convert(Char,DateTime,108) <= '09:00')) AND
NOT (Convert(Char,DateTime,108) >= '16:00')))
GROUP BY Nodes.Caption, Nodes.Location, Interfaces.InterfaceSpeed
95th percentile TEST.JPG 1.1 MB
I am trying to use this syntax "dbo.Get..." for cpu but still got an error..
can you guide me on how to use this please? thank you.
never mind this post as i was able to discover it