10 Replies Latest reply on Mar 16, 2012 4:43 AM by Ciag

    95% Percentile Reporting Help

    goofyzig

      Hi there, would appreciate some help.  2 part question on 95th percentile reporting.

        The company needs to reduce bandwidth at as many sites as possible to save money.  So we've been tasked with providing to management a number for each site that represents how much bandwidth to keep from the current bandwidth allocation.  To do this, our Netops team is using the 95% line on the traffic graphs.  But there is a problem with that value because it includes all the samples from the off-hours, when there is almost zero traffic.   I need a "smarter" 95-Percentile line, one that only includes values from 8am - 6pm, for example, over the span of 30, 60 days.  

      Q1)  I wonder EVEN IF i can have only business hours, if the 95th-percentile is a calculated value, or if it's somethign out of the database that cannot be changed (thus inherently flawed no matter what I do)?

       

      Q2) I called support and they gave me a possible way to do it using custom SQL query.  It didnt work.  Basically, they said to edit the canned 95% Percentile report, and include a time limitation in it from another report.  I did that, pasted in the time limitation from another report (in red below), but that generates an error that says        “Invalid column name DateTime”.   .  Is anyone here familiar with how to fix the SQL query below in order to get the 95-Percentile calculation to exclude non-business hours? 

      Thank you much!!

             

      ----------------From a known-working 95% Percentile Report for the Last 30 days, in RED is what I added that broke it------------------------------

       

      DECLARE @StartDate DateTime

      DECLARE @EndDate DateTime

       

      SET @StartDate = CAST((ROUND(CAST(GetDate() - 30 AS FLOAT), 0, 1)) as datetime)

      SET @EndDate = GetDate()

       

      SELECT

      Interfaces.InterfaceId,

      Interfaces.SiteName,

      Interfaces.Site_Type,

      Nodes.Caption AS NodeName,    

      Interfaces.Caption AS Interface_Caption,

      Interfaces.Carrier,

      Maxbps_In95,

      Maxbps_Out95,

      Maxbps_95,

      Interfaces.InterfaceSpeed

      FROM Nodes

      INNER JOIN Interfaces

                      ON (Nodes.NodeID = Interfaces.NodeID)

      ---------------------

      INNER JOIN       

      (

                      SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95

                      FROM (

                                      SELECT DISTINCT A.InterfaceID

                                      FROM dbo.InterfaceTraffic A

                                      WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate

                      ) AS AA

      ) as RESULT_IN

                      ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)

      ---------------------

      INNER JOIN       

      (

                      SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95

                      FROM (

                                      SELECT DISTINCT A.InterfaceID

                                      FROM dbo.InterfaceTraffic A

                                      WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate

                      ) AS AA

      ) as RESULT_OUT

                      ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)

      ---------------------

      INNER JOIN       

      (

                      SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95

                      FROM (

                                      SELECT DISTINCT A.InterfaceID

                                      FROM dbo.InterfaceTraffic A

                                      WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate

                      ) AS AA

      ) as RESULT_MAX

                      ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)

      ---------------------

       

      WHERE 

       ( Interfaces.PRINT_FLAG LIKE  'xxx'  )

       

        AND

      (

        (DATEPART(weekday, DateTime) > 1) AND

        (DATEPART(weekday, DateTime) < 7) AND

        (Convert(Char, DateTime,108) > '070000') AND

        (Convert(Char, DateTime,108) < '190000')

      )

       

       ORDER BY  Maxbps_95 DESC

      ---------------------------------------------------------------------------------------------------------------------------------

       

        • Re: 95% Percentile Reporting Help
          goofyzig

          Basically looking for a way to do 95% Percentile Reports for 30 days (or longer) that exclude night-time hours and weekends.   Is there any way to do this in Orion?  Thanks.

            • Re: 95% Percentile Reporting Help
              Andy McBride

              Here is a product blog on just that. Working 9 to 5: Limiting Your Reports to Business Hours

              Also, I would highly recommend getting New to Networking Volume 2 - Basics of IP SLA operations set up on the WAN links so that you can track the performance impact of reducing bandwidth.

                • Re: 95% Percentile Reporting Help
                  goofyzig

                  Thanks mcbridea, but the 95% Percentile report is custom SQL and does not have that tab available.  I am really having a hard time with this, so are MANY OTHERS on Thwack, based on searches I've done about this problem.   Why isnt anyone from Solarwinds answering them, or me on this thread.  Surely there are lots of us who need accurate 95% Percentile information to make good business decisions, and surely someone at Solarwinds who knows the database can help craft up the report so it has a date/time adjustable setting.  Question is why isn't anyone helping??

                    • Re: 95% Percentile Reporting Help
                      Andy McBride

                      Custom SQL is not something we create for users but if there are ways I know to get past a SQL hurdle, we won't let you drown. I'm double checking an idea I have to solve this with a SQL guru here.

                      I'll post as soon as I have something.

                        • Re: 95% Percentile Reporting Help
                          goofyzig

                          Thanks mcbridea.  Because Solarwinds already provides a canned 95% Percentile report in Report Writer I dont see how this qualifies as "custom SQL".  All other reports provide the ability to limit the time frame and specific hours for which the report should run, why can't this report have the same options?    We need to run the already existing report but only for M-F business hours, so we can get an accurate picture of utilization at all sites, instead of the false reading we're getting using the overnight hours in the calculations.  Solarwinds support in Ireland pointed out this possible solution in the content exchange area:

                             95th Percentile Circuit Report based on local site business hours

                          However, I cannot seem to get it to work on NPM version 10 SP1, as I detailed there.   I am trying all I can think of to get this information, your help would be very appreciated.   Thanks again.

                            • Re: 95% Percentile Reporting Help
                              Andy McBride

                              I took a look at your post in content exchange and the comments there. It looks like there is a problem opening the functions to wordpad. If you copy them to Program Files -> Solarwinds -> Orion -> Reports then open report writer you can open the function sql there and copy over to SQL management to execute. It all worked for me but I had to follow the readme twice to get it right. Let me know if this works for you.

                                • Re: 95% Percentile Reporting Help
                                  goofyzig

                                  Thanks mcbridea.  Opening the files in Report Writer failed each time, no matter what I did.  So I ended up cutting and pasting just the SQL part of each of the files, eliminating the HTML-looking code from the beginning and end, and that seemed to execute, with the exception of one of the files that used the "gt;"  and "lt;" nomenclature, which gave me a SQL error, so I tried changing these to their respective signs ( > and < ) which seems was accepted by Report Writer.  

                                  Some kind of output came from that, finally, a report showing something, but not without first having to edit the output so it was readable (format strings, etc.).  I learned more about SQL than I really wanted to, and that's alright.  But frankly, I am shooting in the dark here, no way to verify that the results are correct, up the proverbial creek without much support from Solarwinds (almost as if capacity planning is not a network-related issue).   

                                  After all the work to get unverifiable, unsupported results from the Business-Hours 95% Percentile reports, vs regular 95% Percentile reports (that include night-time hours), vs Average/Peak utilization, 30-day, 90-day, accounting for the larger average time periods as time goes further back,  manually pulling up traffic graphs, cut/paste to compare 30 vs 60 day, etc.  and then further MANUALLY compiling ALL those data points into an excel spredsheet to evaluate each result for each site,  a team of 3 people spent nearly the last two weeks  "eyeballing" what we thought was the right utilization for each given circuit.  We added 20% for a fudge/growth factor, and that's the number we're going with to management.   It just seems arcane, and guesstimating at best.   With all the data in the database, Solarwinds NPM should have a tool to perform such evaluations, to help customers predict and assess utilization as intelligently as possible.

                                  I am hoping Solarwinds realizes that in the realm of CAPACITY PLANNING, that is, helping companies assess and plan for bandwidth costs in a smart manner, they are not being very helpful.  The frustration is surmounted by the fact that all the fundamental data is at Solarwinds fingertips, it's already stored in the database,  but there are no tools to calculate, slice, dice, simulate, and intelligently bring out this information.  There should be a bandwidth cost table that gets integrated into this, all sorts of things I can think of.  I dont know if a separate application is needed, or if it's just a matter of more intelligent reports in Report Writer.  The fact is that average and peak reports are just not good enough.  They're WAY too simplistic for today's complexity of varied providers, bursting rates, port size, etc.  But for the 95% Percentile, these reports are not what the Telcos use to bill customers.   I imagine this is why 95% Percentile reports were created at some point in the development process, after clamoring from users like me, but the developers didn't go nearly far enough.    It's like they peered over the possibility of creating truly smart reports, then backed off.  

                                  We will need these reports moving forward, as costs for ethernet bandwidth vary from provider to provider, bursting costs are accounted and simulated, risks assessed, how much can be saved, how much is underutilized, how much overutilized, how much is risked in bursting costs, etc.  We still need to run these reports, now more frequently than ever.  I sincerely hope SW puts some effort into capacity planning in the near future.     Thanks.

                                    • Re: 95% Percentile Reporting Help
                                      Andy McBride

                                      I hear you. Everytime I start doing custom SQL reports I learn how much I have to learn about SQL. I'll forward this thread to Product Management for a feature enhancement request.

                                        • Re: 95% Percentile Reporting Help
                                          Andy Robert

                                          Hi,

                                          I am new member to this Forum and have the requirements mentioned by "goofyzig" in Nov 2010.

                                          Would like to know, if any report for 95th percentile is available now in SolarWinds.

                                          I am using NPM v10.1 HF1.

                                          Thanks

                                          Andy

                                           

                                                                                          goofyzig                                                                                                                                                                                                                          replied on                                                                                                                                                                                      Sat, Nov 20 2010 3:20 AM                                                                                                                                                                                                                                                                                                                                             

                                          PoorPoorFairFairAverageAverageGoodGoodExcellentExcellent

                                           

                                          Thanks mcbridea.  Opening the files in Report Writer failed each  time, no matter what I did.  So I ended up cutting and pasting just the  SQL part of each of the files, eliminating the HTML-looking code from  the beginning and end, and that seemed to execute, with the exception of  one of the files that used the "gt;"  and "lt;" nomenclature, which  gave me a SQL error, so I tried changing these to their respective signs  ( > and < ) which seems was accepted by Report Writer.  

                                          Some kind of output came from that, finally, a report showing  something, but not without first having to edit the output so it was  readable (format strings, etc.).  I learned more about SQL than I really  wanted to, and that's alright.  But frankly, I am shooting in the dark  here, no way to verify that the results are correct, up the proverbial  creek without much support from Solarwinds (almost as if capacity  planning is not a network-related issue).   

                                          After all the work to get unverifiable,  unsupported results from the Business-Hours 95% Percentile reports, vs  regular 95% Percentile reports (that include night-time hours), vs  Average/Peak utilization, 30-day, 90-day, accounting for the larger  average time periods as time goes further back,  manually pulling up traffic graphs, cut/paste to compare 30 vs 60 day, etc.  and  then further MANUALLY compiling ALL those data points into an excel  spredsheet to evaluate each result for each site,  a team of 3 people  spent nearly the last two weeks  "eyeballing" what we thought was the  right utilization for each given circuit.  We added 20% for a  fudge/growth factor, and that's the number we're going with to  management.   It just seems arcane, and guesstimating at best.   With  all the data in the database, Solarwinds NPM should have a tool to  perform such evaluations, to help customers predict and assess  utilization as intelligently as possible.

                                          I am hoping Solarwinds realizes that in the realm of CAPACITY  PLANNING, that is, helping companies assess and plan for bandwidth costs  in a smart manner, they are not being very helpful.  The frustration is  surmounted by the fact that all the fundamental data is at Solarwinds  fingertips, it's already stored in the database,  but there are no tools  to calculate, slice, dice, simulate, and intelligently bring out this  information.  There should be a bandwidth cost table that gets  integrated into this, all sorts of things I can think of.  I dont know  if a separate application is needed, or if it's just a matter of more  intelligent reports in Report Writer.  The fact is that average and peak  reports are just not good enough.  They're WAY too simplistic for  today's complexity of varied providers, bursting rates, port size, etc.   But for the 95% Percentile, these reports are not what the Telcos use  to bill customers.   I imagine this is why 95% Percentile reports were  created at some point in the development process, after clamoring from  users like me, but the developers didn't go nearly far enough.    It's  like they peered over the possibility of creating truly smart reports,  then backed off.  

                                          We will need these  reports moving forward, as costs for ethernet bandwidth vary from  provider to provider, bursting costs are accounted and simulated, risks  assessed, how much can be saved, how much is underutilized, how much  overutilized, how much is risked in bursting costs, etc.  We still need  to run these reports, now more frequently than ever.  I sincerely hope  SW puts some effort into capacity planning in the near future.      Thanks.

                                            • Re: 95% Percentile Reporting Help
                                              Ciag

                                              Hi Andy,

                                              there is a report created by Brian Duvall which I think might be just what you are looking for. you can check it out 95th Percentile Circuit Report based on local site business hours

                                              The report filters 95th percentile interface traffic for business hours monday to friday, you can adjust the range of business hours also; for example I'm running this 9-4 Monday to Friday.

                                              There is an SQL update to the report which you can see posted on the 'linked' page in my 3rd comment. The original report was including the weekends, with my update weekends are excluded. I have thoroughly tested the accuracy of this report and can confirm it is accurate.

                                              HTH