5 Replies Latest reply on Nov 29, 2018 8:52 PM by mesverrum

    Hourly Average bps- Need SWQL Help

    johnny ringo

      All,

      I have the below report that is intended to give me an hourly average bps over the last 24 hours.  However my data doesnt appear to be getting averaged over the hour, as I have multiple entries per hour for the same interface.  Can anyone clue me in to what is messed up in my query?  thank you!

       

      SELECT I.FullName, DateTrunc('Hour', NIT.DateTime) AS Date, ROUND(AVG(InAveragebps)/1000000, 1) AS AVG_IN_MBPS,  ROUND(MIN(InMinbps)/1000000, 2) AS MIN_MBPS, ROUND(MAX(InMaxbps)/1000000, 2) AS MAX_MBPS

       

      FROM Orion.NPM.InterfaceTraffic NIT

      INNER JOIN Orion.NPM.Interfaces I ON I.InterfaceID = NIT.InterfaceID

      INNER JOIN Orion.NodesCustomProperties CP ON CP.NodeID = NIT.NodeID

      WHERE I.FullName LIKE '%outside%' AND CP.City NOT LIKE '%MAIN%'

      AND DateTrunc('DAY', NIT.DateTime) >= DateTrunc('DAY', getDate()-1)

      GROUP BY I.FullName, NIT.DATETIME

      ORDER BY ROUND(AVG(InAveragebps)/1000000, 1) DESC

        • Re: Hourly Average bps- Need SWQL Help
          kyrka

          Johnny Ringo,

          I'd suggest having a look at this post on Detailed Historical Interface Utilization Report: Detailed historical interface utilization report


          In particular check out the EXCELLENT detailed description of what "Reality" is in terms of data retention within the database, in the first comment from Zack Mutchler Expert

          • Re: Hourly Average bps- Need SWQL Help
            kyrka

            Oh wait - you're already aware of this - if nothing else by proxy of roughly 160 rows returned for an hour.

            What you're looking for is the correct SWQL "math" to create one SINGLE average of all of those data points, am I correct?

              • Re: Hourly Average bps- Need SWQL Help
                johnny ringo

                sorry ust saw this.  yes im interested in the correct SWQL math

                  • Re: Hourly Average bps- Need SWQL Help
                    mesverrum

                    At glance grouping by DateTrunc('Hour', NIT.DateTime) should get you what you want unless there's something I'm skipping over

                      • Re: Hourly Average bps- Need SWQL Help
                        mesverrum

                        I vpn'ed into the lab to check, whatever you were doing with your datetime where condition, that seems weird.  Are you trying to get entries just for yesterday, or the last 24 hours? I guess what I'm seeing in your original query is a data point for every hour between the start of the day yesterday and now?

                         

                        This is what i'd do for for last 24 hours:

                        SELECT I.FullName

                        , DateTrunc('Hour', NIT.DateTime) AS Date

                        , ROUND(AVG(nit.InAveragebps)/1000000, 1) AS AVG_IN_MBPS

                        , ROUND(MIN(nit.InMinbps)/1000000, 2) AS MIN_MBPS

                        , ROUND(MAX(nit.InMaxbps)/1000000, 2) AS MAX_MBPS

                         

                        FROM Orion.NPM.InterfaceTraffic NIT

                        INNER JOIN Orion.NPM.Interfaces I ON I.InterfaceID = NIT.InterfaceID

                        INNER JOIN Orion.NodesCustomProperties CP ON CP.NodeID = i.NodeID

                         

                        WHERE I.FullName LIKE '%outside%'

                        AND CP.City NOT LIKE '%MAIN%'

                        AND nit.datetime >= addday(-1,getutcDate())

                         

                        GROUP BY I.FullName, DateTrunc('Hour', NIT.DateTime)

                         

                        ORDER BY ROUND(AVG(InAveragebps)/1000000, 1) DESC

                         

                         

                         

                         

                         

                        and for previous day

                        SELECT I.FullName

                        , DateTrunc('Hour', NIT.DateTime) AS Date

                        , ROUND(AVG(nit.InAveragebps)/1000000, 1) AS AVG_IN_MBPS

                        , ROUND(MIN(nit.InMinbps)/1000000, 2) AS MIN_MBPS

                        , ROUND(MAX(nit.InMaxbps)/1000000, 2) AS MAX_MBPS

                         

                        FROM Orion.NPM.InterfaceTraffic NIT

                        INNER JOIN Orion.NPM.Interfaces I ON I.InterfaceID = NIT.InterfaceID

                        INNER JOIN Orion.NodesCustomProperties CP ON CP.NodeID = i.NodeID

                         

                        WHERE I.FullName LIKE '%outside%'

                        AND CP.City NOT LIKE '%MAIN%'

                        AND daydiff(nit.datetime,getutcDate()) = 1

                         

                        GROUP BY I.FullName, DateTrunc('Hour', NIT.DateTime)

                         

                        ORDER BY ROUND(AVG(InAveragebps)/1000000, 1) DESC