8 Replies Latest reply on Nov 23, 2016 2:08 PM by Manilyn Ramos

    resolving 95th Percentile

    Ciag

      Hi,

      I'm editing a custom SQL report to include 95th percentile interface traffic Recv & Xmit and I'm looking for the column and the table where the 95th percentile data for interface traffic is held or does Orion resolve the 95th percentile on the fly?

      Cheers

        • Re: resolving 95th Percentile
          Andy Ng

          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.

            • Re: resolving 95th Percentile
              Ciag

              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?

                • Re: resolving 95th Percentile
                  Andy Ng

                  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

                    • Re: resolving 95th Percentile
                      Ciag

                      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. 

                        • Re: resolving 95th Percentile
                          Andy Ng

                          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!

                            • Re: resolving 95th Percentile
                              Ciag

                              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

                        • Re: resolving 95th Percentile
                          Manilyn Ramos

                          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.