5 Replies Latest reply on Jun 11, 2014 11:06 AM by tdanner

    Interface aggregate chart (swql)

    dkemens

      Hi guys,

       

      I'm trying to essentially recreate the Interface Aggregate Chart via the API, but am not having much luck (though I though for a while that I was). My query is

       

      SELECT DisplayName, Description, DateTime, InAveragebps, OutAveragebps, TotalBytes,

        TotalPackets, Averagebps, OutPercentUtil, InPercentUtil, PercentUtil, OutMinbps, OutMaxbps, InMinbps, InMaxbps

          FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID=".$interfaceId." AND DateTime > GetUtcDate()-1

       

      The problem is - all the data that comes back shows the same value for Min/Max/Avg for In and Out (ie. In Min = In Max = In Avg). How should I tweak this swql to get what I'm after?  Also - for the mods, you guys should consider putting together a 'cookbook' of potentially common queries and distribute it with your SDK

       

      This is an example of the chart:

      interfaceAggregateChart.gif

       

      Thanks!

        • Re: Interface aggregate chart (swql)
          tdanner

          When Orion collects data about interface traffic, it writes it into that table with Min=Max=Avg, just like you observed. When this data gets older (default = 7 days old), it gets summarized into hourly buckets. For each interface, all the data collected in a given hour gets summarized into a single row and the detailed data is removed to save space. At this point Min=the smallest value recorded that hour, Max=the largest that hour, and Avg=the average for the hour. A similar process happens again when the data is (by default) 30 days old when the hours get summarized into days.

           

          The chart does its own version of this process at display time using the "Sample Interval" setting on the chart. This aggregation happens in memory in the web server process before the data gets sent to the browser. In other words, the website queries SWIS using something not unlike your query and gets a bunch of raw points. Then it aggregates this into buckets sized according to the sample interval (looks like it might be 12 hours in your screenshot) and send it to the browser for chart rendering.

            • Re: Interface aggregate chart (swql)
              dkemens

              First, thank for you that information. It's extremely enlightening.

               

              Second... That's a rather interesting implementation. Are these aggregation algorithms available anywhere? I'm asking for consistency. Essentially, I could create my own version of these algorithms - but if anybody were to ever compare my graph to the NPM, they would very likely not match up (and trust me, my client will). This would raise some questions and I would have to find some way to explain that the NPM isn't displaying real time accurate data, but aggregating across variable time ranges.

               

              Since I imagine this is a no win situation as the real time algorithm is likely not available (nor would I want to reverse engineer it) - it would help me to make my own most accurate version if I knew what the min/max/avg value actually is. For instance - is it the average? Is it simply what was going in/out at the instant it was polled?

               

              The next question I have is - when querying something for, say, the last month; am I potentially getting data that has been aggregated in 3 different ways? The oldest data being aggregated by day, the majority of the data by hour, and the most recent simply showing min=max=avg? Disregard (see below)

               

              Thanks, again

                • Re: Interface aggregate chart (swql)
                  dkemens

                  Using the powers of my mind alone (and a cup of coffee), I have come to the realization that in an instant - there is only one value for in/out bandwidth (or anything for that matter). There is no average, min, or max in an instance. No need to read that part of my previous post. My apologies for asking a dumb question.

                • Re: Interface aggregate chart (swql)
                  dkemens

                  Actually - disregard all of my followup questions. As the coffee got in to my system, lightbulbs starting going off all over the place. I apologize for flooding this thread with lunacy. I thought about editing and removing all of it, but I decided to leave it here; warning future thwackers against posting questions before drinking their coffee.

                   

                  It still would be nice to know the parameters of the algorithm, though. For instance, does it calculate on the hour (say, all polls between 1pm and 2pm) or does it take an hour from a specific poll time (2:53pm + 1hr)?

                    • Re: Interface aggregate chart (swql)
                      tdanner

                      I'm glad coffee got you straightened out.

                       

                      The rollups (both of historical data in the database and to prepare for chart display) are independent of the polling schedule. If it's doing hourly rollup, it is on the hour (like 1pm to 2pm) regardless of where that falls in the particular device's polling schedule.