2 Replies Latest reply on Nov 5, 2015 6:20 AM by Richard Phillips

    Detailed historical interface utilization report

    Richard Phillips

      I'm hoping to not reinvent the wheel here.

       

      We are in the midst of a "Bandwidth Utilization Review" and I need to create reports and charts on all of the "Uplink" interfaces between our sites.

       

      I would like to pull all of the data for each interface, not just averages but everything that is available to create my own graphs. The traffic tends to be bursty at times and an average wouldn't give us accurate results.

       

      Has anyone created a report like this? I can go to each link and pull the report, but to get historical data from several interfaces across several months is quite tedious. I'd love to be able to run a report and say, "give me all the interface data for XX interface on XX device for this year"

       

      All the reports I'm finding give averages and currents, but I haven't found anything to pull it all. And I'm not a SQL guy, so I haven't explored going that route.

       

      IE I want the data shown at the bottom that is used to build this graph.

       

          

      10Howe - GigabitEthernet 2/48 · "Link to 160 Allen"
      Min/Max/Average bps of Recv 1.0 Gbps Xmit 1.0 Gbps
      Today
      DATE / TIMEMin/Max Receive bpsMin/Max Transmit bpsAverage Receive bpsAverage Transmit bps
      4-Nov-1512:00 AM39067.6757839067.6757849529.8359449529.8359439067.6757849529.83594
      4-Nov-1512:01 AM
      4-Nov-1512:02 AM
      4-Nov-1512:03 AM
      4-Nov-1512:04 AM63122.9179763122.9179753311.8359453311.8359463122.9179753311.83594
      4-Nov-1512:05 AM
      4-Nov-1512:06 AM
      4-Nov-1512:07 AM
      4-Nov-1512:08 AM33154.4726633154.4726639188.0664139188.0664133154.4726639188.06641
      4-Nov-1512:09 AM
      4-Nov-1512:10 AM
      4-Nov-1512:11 AM
      4-Nov-1512:12 AM35458.7695335458.7695340822.5312540822.5312535458.7695340822.53125
      4-Nov-1512:13 AM
      4-Nov-1512:14 AM
      4-Nov-1512:15 AM
      4-Nov-1512:16 AM42465.1757842465.1757846084.5585946084.5585942465.1757846084.55859
        • Re: Detailed historical interface utilization report
          zackm

          You're going to want to look at the InterfaceTraffic view in the database.

           

          SELECT
            n.Caption AS 'Device'
            ,i.caption AS 'Interface'
            ,t.DateTime
            ,t.In_Averagebps
            ,t.In_Minbps
            ,t.In_Maxbps
            ,t.Out_Averagebps
            ,t.Out_Minbps
            ,t.Out_Maxbps
            ,t.Weight
          FROM InterfaceTraffic t
          JOIN Interfaces i on i.InterfaceID = t.InterfaceID
          JOIN Nodes n on n.NodeID = t.NodeID
          WHERE i.InterfaceID IN ('123','456','789') --INSERT YOUR INTERFACE IDs HERE FOR YOUR UPLINKS
          ORDER BY n.Caption, i.Caption, t.DateTime
          
          

           

          One  thing to note is that you mentioned that you don't want to use data that has been averaged. That's going to be a bit of an issue with the way that data is retained by default within SolarWinds.

           

          By default, SolarWinds has 3 levels of data retention for (almost) all metrics. Specifically for interface statistics, there are Detail (7 day), Hourly (30 day), and Daily (365 day) metrics.

           

          For Instance, this:

           

          SELECT MIN(DateTime) firstDetail, MAX(DateTime) lastDetail FROM InterfaceTraffic_Detail;
          SELECT MIN(DateTime) firstHourly, MAX(DateTime) lastHourly FROM InterfaceTraffic_Hourly;
          SELECT MIN(DateTime) firstDaily, MAX(DateTime) lastDaily FROM InterfaceTraffic_Daily;
          
          

           

          Returns this:

           

          firstDetail             lastDetail
          ----------------------- -----------------------
          2015-10-28 00:00:35.917 2015-11-04 21:11:22.907
          
          
          firstHourly             lastHourly
          ----------------------- -----------------------
          2015-09-05 00:00:00.000 2015-10-27 23:00:00.000
          
          
          firstDaily              lastDaily
          ----------------------- -----------------------
          2014-11-04 00:00:00.000 2015-09-04 00:00:00.000
          
          

           

          Which shows:

           

          Detailed Statistics are held for n-7 days(every single data point)

          Hourly Statistics are held for n-8 > n-30 days (detailed statistics are averaged into hourly blocks)

          Daily Statistics are held for n-31 > n-365 days (hourly statistics are averaged into daily blocks)

           

          The takeaway here is that retention levels maintain averaged data by default, so your "raw" data is unfortunately not as raw as one might expect...

           

          Technically, you can edit these retention levels and increase (or decrease) the amount of Detail, Hourly, or Daily data that is being kept in your database. However, it should be noted and respected that dramatic increases to your retention levels will result in an exponential increase in the amount of data being retained, which has the potential to produce negative consequences to your website response times. If you need to move these numbers you need to either scale your SQL database resources for the increased demand, or you can setup a database warehouse-type scenario where you export the data you need to retain as "raw" into a separate database and use SQL Server Reporting Services to report directly against that data. This is a highly scalable, and generally free (as in: included with SQL Server licensing), architecture that allows you to maintain a highly functioning monitoring platform and still comply with your specific reporting requirements.

           

          For reference, you can view and edit the retention settings for NPM under Settings > Polling Settings, or you can run this query to get your specific interface retention settings:

           

          SELECT
            SettingID
            ,Name
            ,Description
            ,Units
            ,Minimum
            ,Maximum
            ,CurrentValue
            ,DefaultValue
          FROM Settings
          WHERE SettingID IN ('NPM_Settings_InterfaceAvailability_Retain_Detail','NPM_Settings_InterfaceAvailability_Retain_Hourly','NPM_Settings_InterfaceAvailability_Retain_Daily')
          
          

           

           

          -ZackM

          Loop1 Systems: SolarWinds Training and Professional Services