7 Replies Latest reply on Sep 9, 2008 2:34 PM by derek.sperry

    95th Percentile Aggregation

      My company uses Solarwinds for monitoring and reporting of over 2000 interfaces along our network backbone.  We have a few customers who have requested aggregate 95/5 billing reports from us and I'd like to figure out a way to make Solarwinds report this information.  My first thought was to utilize Report Writer, however, I don't know enough SQL code to get the 95th% report templates to perform their calculations on a group of interfaces, defined by Custom Fields.

      I'm attaching a template we currently use to poll 95/5 data for groups defined by a Custom Field called Interfaces.CustomerName.  This report currently provides 95/5 data for each seperate interface, where I'd like it to report 95/5 for all interfaces, aggregated.  If anyone could please take a look at this and point me in the proper direction to accomplish this I would be so appreciative.  Also, if anyone has an entirely different way to approach this, please feel free to let me know:

       //Current template:

      DECLARE @StartDate DateTime
      DECLARE @EndDate DateTime
       
      SET @StartDate = Cast( Cast(Year(GetDate()) AS varchar) + '-' + Cast(Month(GetDate()) AS varchar) + '-01' AS datetime)
      SET @EndDate = GetDate()
       
      SELECT Interfaces.InterfaceId,
      Nodes.Caption AS NodeName,     
      Interfaces.Caption AS Interface_Caption,
      Maxbps_In95 / 1000000.0 as Maxbps_In95,
      Maxbps_Out95 / 1000000.0 as Maxbps_Out95,
      Maxbps_95 / 1000000.0 as Maxbps_95
      FROM dbo.Nodes
      INNER JOIN dbo.Interfaces
          ON (dbo.Nodes.NodeID = dbo.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 (dbo.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 (dbo.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 (dbo.Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
                      WHERE dbo.Interfaces.CustomerName = 'TestCompany'
       
       
      ORDER BY NodeName, Interface_Caption

        • Re: 95th Percentile Aggregation

           Bump, could really use assistance if anyone has a moment.  Thank you!

            • Re: 95th Percentile Aggregation
              denny.lecompte
              There's a 95th percentile report included with NPM 8.5.x and 9.x.  Are you modifying that one or starting over?
                • Re: 95th Percentile Aggregation

                   This is a slight modification of the already existing report from Orion (95th % - Last Month, or something to that extent).  The major problem is that I can't get it to report the combined/aggregate 95th for all ports with a custom field tag as the search modifier, it wants to report the 95th for each interface that matches that search criteria.  The search modifier is working appropriately, I just don't know how to get the report to aggregate those interfaces, rather than report data for each seperate interface.

                    • Re: 95th Percentile Aggregation
                      denny.lecompte
                      So are you trying to get all the individual datapoints from all of the interfaces and then find the 95th percentile of that?  Or, are when you say "aggregate", are you trying to average or sum data in some way?
                        • Re: 95th Percentile Aggregation

                           Ok, let me try to explain this a bit more clearly.

                           We have customers who have multiple interfaces on our switches (say ports 1-4 a bought by company A and 5-8 are bought by company B, etc.)  What I need this report to do for me is to report the 95th% for 5 min samples for company A's aggregate ports.  What that means is if you take all 5 min samples for company A's ports, line them up together, you then take the 95th% for every 5 minute sample.  So in this example, you'd have 4 bandwidth measurements (corresponding to the 4 ports owned by company A) for 12:00, and then 4 more measurements at 12:05.  I need to find the 95th% bandwidth usage for those 4 ports collectively for every 5 minute sample.

                           I'm not sure how to explain this more detailed than that, so I hope that makes more sense.  As of now, the report generates 95/5 information for each of the 4 ports individually...I need it to report 95/5 for the 4 ports collectively (aggregation).