cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 10

Total bandwidth consumed by node over x period

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

Tags (3)
0 Kudos
0 Replies