0 Replies Latest reply on Aug 26, 2014 10:08 AM by oliver.grist

    Total bandwidth consumed by node over x period

    oliver.grist

      Hello,

       

      I am looking to use the data from SolarWinds to replace some of the competing monitoring systems we have. One requirement is showing total traffic over various predefined time periods (I have reduced the number of periods for this example).

       

      I have the following query, and it works ok but it is super slow to run (taking ~10seconds to return 4 numbers) . Is there a better table, or can the query be refined to enable this?

       

      SELECT Top 1

      (SELECT SUM(InTotalBytes) as x FROM Orion.NPM.InterfaceTraffic WHERE NodeID = 5347 AND ObservationTimestamp >= '26/08/14 00:00:00') as InToday,

      (SELECT SUM(OutTotalBytes) as x FROM Orion.NPM.InterfaceTraffic WHERE NodeID = 5347 AND ObservationTimestamp >= '26/08/14 00:00:00') as OutToday,

      (SELECT SUM(InTotalBytes) as x FROM Orion.NPM.InterfaceTraffic WHERE NodeID = 5347 AND ObservationTimestamp >= '01/01/14 00:00:00') as InYear,

      (SELECT SUM(OutTotalBytes) as x FROM Orion.NPM.InterfaceTraffic WHERE NodeID = 5347 AND ObservationTimestamp >= '01/01/14 00:00:00') as OutYear

      FROM Orion.Nodes WHERE Nodes.NodeID = 5347

       

      I would ideally like to have written it as follows, which I believe would have far greater performance, but I dont believe SWQL supports IF as a command.

       

      SELECT

      SUM(IF(ObservationTimestamp >= '26/08/14 00:00:00', InTotalBytes, 0)) as InToday,

      SUM(IF(ObservationTimestamp >= '26/08/14 00:00:00', OutTotalBytes, 0)) as OutToday,

      SUM(IF(ObservationTimestamp >= '01/01/14 00:00:00', InTotalBytes, 0)) as InYear,

      SUM(IF(ObservationTimestamp >= '01/01/14 00:00:00', OutTotalBytes, 0)) as OutYear

      FROM Orion.NPM.InterfaceTraffic

      WHERE NodeID = 5347

      AND ObservationTimestamp >= '01/01/14 00:00:00'

       

      Any thoughts or pointers, gratefully received!

      Oli