resolving 95th Percentile

Hi Ciaran,

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:

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?

Hi Ciaran,

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. 

Hi Ciaran,

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.

Cheers!

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,
AVG(Case OutBandwidth
    When 0 Then 0
    Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
AVG(Case InBandwidth
    When 0 Then 0
    Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
AVG(Case
    When InBandwidth+OutBandwidth=0 Then 0
    When InBandwidth=0 Then
        (Out_Averagebps/OutBandwidth) * 100
    When OutBandwidth=0 Then
        (In_Averagebps/InBandwidth) * 100
    Else
        ( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*100
End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit,

MAX(Case OutBandwidth
    When 0 Then 0
    Else (dbo.GetoutBps95th(InterfaceTraffic.InterfaceID, @StartDate, @EndDate)/OutBandwidth) * 100 End) AS Xmit_95th_Percent,

MAX(Case InBandwidth
    When 0 Then 0
     Else (dbo.GetInBps95th(InterfaceTraffic.InterfaceID, @StartDate, @EndDate)/InBandwidth) * 100 End) AS Recv_95th_Percent

FROM
(Nodes
 INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))
  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

WHERE
(  DateTime > (GetDate()-7) ) 
AND 
(
  (
   (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

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