6 Replies Latest reply on Aug 14, 2017 12:42 PM by sean.tucker

    95th Percentile Traffic Rate - Last 30 Days - WAN Circuits Only

    haydanb

      The built-in report that shows 95th percentile traffic rate - last month works fine, but I manage over 3000 nodes and would like to parse this report to only show WAN Circuits, of which there are about 50.  Anyone know how to do this?

      Thanks,

        • Re: 95th Percentile Traffic Rate - Last 30 Days - WAN Circuits Only
          pratikmehta003

          You can have your WAN circuits in one group and then try to run the report.

          • Re: 95th Percentile Traffic Rate - Last 30 Days - WAN Circuits Only
            hanif.solarwinds

            Hi haydanb

            Or you can create an account with permission to devices and run report when login using that account.

             

            Regards

             

            Hanif

            • Re: 95th Percentile Traffic Rate - Last 30 Days - WAN Circuits Only
              haydanb

              I created a group that has all the WAN circuit interfaces.  Where can the group be defined in the report?  I'm using NPM 11.01 and the 95th Percentile Traffic Rate report is a historical report and can only be edited within the Orion Report Writer.  I saw this post, but looks like its for Nodes | SQL or VIEW to select group-members

              Here's the SQL from the existing report for the SELECT and FROM (see below).  The name of the group is called 'WAN Circuits'  Thanks

               

               

              SELECT    Interfaces.InterfaceId,

                      Nodes.NodeID,

                      Nodes.Caption AS NodeName,

                      Nodes.VendorIcon AS Vendor_Icon,

                      Interfaces.Caption AS Interface_Caption,

                      Interfaces.InterfaceIcon AS Interface_Icon,

                      Maxbps_In95,

                      Maxbps_Out95,

                      Maxbps_95

              FROM Nodes

              INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

              INNER JOIN (

                  SELECT    InterfaceID,

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

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

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

                  FROM InterfaceTraffic

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

                  GROUP BY InterfaceID

              ) TrafficStat

              ON Interfaces.InterfaceID = TrafficStat.InterfaceID

              WHERE ${LIMITATION}

              ORDER BY NodeName, Interface_Caption

                • Re: 95th Percentile Traffic Rate - Last 30 Days - WAN Circuits Only
                  orionfan

                  I am also interested in this.  However, I have a custom property called WAN_ID.  We have three WAN's so I would like to create a report where it just polls interfaces with WAN_ID = 1, WAN_ID=2.....  Any suggestions?

                    • Re: 95th Percentile Traffic Rate - Last 30 Days - WAN Circuits Only
                      haydanb

                      Hey orionfan,

                       

                      Working with my DBA, we modified the report in my original post to use a custom field called WANCircuits which in my case is a simple no/yes (0/1) field.  The code is below; look at the WHERE statement.  You can see that it will include only interfaces where the WANCircuit = 1.   If your custom field WAN_ID is a similar type field, then you just need to make sure the interface that is one of your WAN circuits has this field checked so the query can pick it up as a "yes" (1).

                       

                      ==============================================================================

                       

                      SET NOCOUNT OFF

                      SET ROWCOUNT 0

                       

                      DECLARE @StartDate DateTime

                      DECLARE @EndDate DateTime

                       

                      SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

                      SET @EndDate   = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

                       

                      SELECT          Interfaces.InterfaceId            AS InterfaceID,

                                    Nodes.NodeID                      AS NodeID,                       

                                    Nodes.Caption                     AS NodeName, 

                                    Interfaces.Caption                AS Interface_Caption,

                                    Interfaces.InBandwidth            AS Interface_Bandwidth,

                                   

                                    -- RAW NUMBERS

                                    Maxbps_In95 / 1000000.0           AS Maxbps_In95,

                                    Maxbps_Out95 / 1000000.0   AS Maxbps_Out95,

                                    Maxbps_95 / 1000000.0             AS Maxbps_95,

                       

                                    -- PERCENT CALCULATION

                                    Maxbps_In95 / Interfaces.InBandwidth  * 100     AS ReceivePCT,

                                    Maxbps_Out95 / Interfaces.InBandwidth * 100     AS TransmitPCT,

                                    Maxbps_95 / Interfaces.InBandwidth     * 100     AS MaxPCT

                                    

                      FROM Nodes

                      INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

                      INNER JOIN (

                             SELECT InterfaceID,

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

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

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

                             FROM InterfaceTraffic

                             WHERE

                                           InterfaceTraffic.DateTime >= @StartDate

                                           AND InterfaceTraffic.DateTime <= @EndDate

                                           AND InterfaceID in (SELECT InterfaceID FROM dbo.Interfaces WHERE    WANCircuit = 1)

                             GROUP BY InterfaceID

                      ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

                      WHERE

                             (1=1)

                      ORDER BY

                             NodeName,

                             Interface_Caption

                  • Re: 95th Percentile Traffic Rate - Last 30 Days - WAN Circuits Only
                    sean.tucker

                    I used what I found on this thread to help me modify my reports.  I need to report the utilization on our internet circuits.  However, I would like to set a second condition so that I can have 4 reports for North America, South America, EMEA and APAC.

                     

                    I want to set a second condition so that the report will only pull the information on the specified internet interface and then also I can also set a second condition to only poll the specified region.  Can anyone help?

                     

                    SET NOCOUNT OFF

                    SET ROWCOUNT 0

                     

                    DECLARE @StartDate DateTime

                    DECLARE @EndDate DateTime

                     

                    SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

                    SET @EndDate   = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

                     

                    SELECT          Interfaces.InterfaceId            AS InterfaceID,

                                  Nodes.NodeID                      AS NodeID,                       

                                  Nodes.Caption                     AS NodeName, 

                                  Interfaces.Caption                AS Interface_Caption,

                                  Interfaces.InBandwidth            AS Interface_Bandwidth,

                                 

                                  -- RAW NUMBERS

                                  Maxbps_In95 / 1000000.0           AS Maxbps_In95,

                                  Maxbps_Out95 / 1000000.0   AS Maxbps_Out95,

                                  Maxbps_95 / 1000000.0             AS Maxbps_95,

                     

                                  -- PERCENT CALCULATION

                                  Maxbps_In95 / Interfaces.InBandwidth  * 100     AS ReceivePCT,

                                  Maxbps_Out95 / Interfaces.InBandwidth * 100     AS TransmitPCT,

                                  Maxbps_95 / Interfaces.InBandwidth     * 100     AS MaxPCT

                                  

                    FROM Nodes

                    INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

                    INNER JOIN (

                           SELECT InterfaceID,

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

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

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

                           FROM InterfaceTraffic

                           WHERE

                                         InterfaceTraffic.DateTime >= @StartDate

                                         AND InterfaceTraffic.DateTime <= @EndDate

                                         AND InterfaceID in (SELECT InterfaceID FROM dbo.Interfaces WHERE    Internet_Interface = 1)

                           GROUP BY InterfaceID

                    ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

                    WHERE

                           (1=1)

                    ORDER BY

                           NodeName,

                           Interface_Caption