Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Help with SWQL query to calculate (SUM) total amount of data from multiple interfaces

Good morning,

Apologies but this will very likely be a very newbie question, as I am not a DBA...

I have a SWQL query that works well for single interfaces (access switches) for calculating the total amount of data (in and out):

----------------------
--CALCULATE TOTAL AMOUNT OF DATA IN GB - TODAY
SELECT
ROUND((SUM(TotalBytes)/1024/1024/1024),1) AS [Total GB]
FROM Orion.NPM.InterfaceTraffic i
WHERE i.Interface.Name = 'GigabitEthernet1/0/12'
AND NodeID = '305'
AND (DAY(TOLOCAL(DATETIME)) = (DAY(TOLOCAL(GETUTCDATE()))))
----------------------

I use this on a specific modern dashboard to represent how much Wireless traffic has passed through a certain Wireless Access Point (today) - ie. the switchport that the WAP is patched into.

Anyway, I want to SUM 3 of these ROUND queries together to represent the TOTAL amount of wireless traffic passing through a particular access switch, the queries are:

-----------------------

--CALCULATE TOTAL AMOUNT OF DATA IN GB - TODAY
SELECT
ROUND((SUM(TotalBytes)/1024/1024/1024),1) AS [Total GB]
FROM Orion.NPM.InterfaceTraffic i
WHERE i.Interface.Name = 'GigabitEthernet1/0/12'
AND NodeID = '305'
AND (DAY(TOLOCAL(DATETIME)) = (DAY(TOLOCAL(GETUTCDATE()))))

--CALCULATE TOTAL AMOUNT OF DATA IN GB - TODAY
SELECT
ROUND((SUM(TotalBytes)/1024/1024/1024),1) AS [Total GB]
FROM Orion.NPM.InterfaceTraffic i
WHERE i.Interface.Name = 'GigabitEthernet1/0/1'
AND NodeID = '305'
AND (DAY(TOLOCAL(DATETIME)) = (DAY(TOLOCAL(GETUTCDATE()))))

--CALCULATE TOTAL AMOUNT OF DATA IN GB - TODAY
SELECT
ROUND((SUM(TotalBytes)/1024/1024/1024),1) AS [Total GB]
FROM Orion.NPM.InterfaceTraffic i
WHERE i.Interface.Name = 'GigabitEthernet1/0/8'
AND NodeID = '305'
AND (DAY(TOLOCAL(DATETIME)) = (DAY(TOLOCAL(GETUTCDATE()))))

-----------------------

But I am not sure how to do this? Maybe it would be better to SUM all 3 together and then do a single ROUND afterwards? Perhaps there is a 'better' or 'more efficient' way to get what I am after?

Any help would be much appreciated. I've already found this community forum priceless so that you to everyone - it's great to see :-D

Cheers

Find more posts tagged with

Accepted answers

adam.beedell

There's a few ways to do this

You can use SWQL Studio and the WITH QUERYSTATS/QUERYPLAN verb to see the efficiency if you want

(It would be best to SUM then ROUND usually)

I think the easiest way to do what you want is just to put the 3 interface names in an OR in the WHERE

SELECT
ROUND((SUM(TotalBytes)/1024/1024/1024),1) AS [Total GB]
FROM Orion.NPM.InterfaceTraffic i
WHERE

(
i.Interface.Name like 'GigabitEthernet1/0/8'

or
i.Interface.Name like 'GigabitEthernet1/0/1'
or
i.Interface.Name like 'GigabitEthernet1/0/12'
)
AND NodeID = '305'
AND (DAY(TOLOCAL(DATETIME)) = (DAY(TOLOCAL(GETUTCDATE()))))

I think that works as an IN too

WHERE
i.Interface.Name in ('GigabitEthernet1/0/8', 'GigabitEthernet1/0/1', 'GigabitEthernet1/0/12')

Havnt tried.

Often for this sort of thing you end up needing a GROUP BY, I dont think it's necessary here but if that doesnt look as expected, try

SELECT
ROUND((SUM(TotalBytes)/1024/1024/1024),1) AS [Total GB]
FROM Orion.NPM.InterfaceTraffic i
WHERE

i.Interface.Name in ('GigabitEthernet1/0/8', 'GigabitEthernet1/0/1', 'GigabitEthernet1/0/12')
AND NodeID = '305'
AND (DAY(TOLOCAL(DATETIME)) = (DAY(TOLOCAL(GETUTCDATE()))))
GROUP BY NodeID

Or something like that. Grouping is best explained on a SQL tutorial, but usually you have everything you're selecting except the thing being aggerated grouped up

At the moment you've got this written as 3 subqueries, and it is possible to have 3 subqueries put together in a few different ways as a datasource and then to SUM from that/those datasources. I dont think you need this at all, but worth mentioning in case of some later much more complicated thing

All comments

adam.beedell

There's a few ways to do this

You can use SWQL Studio and the WITH QUERYSTATS/QUERYPLAN verb to see the efficiency if you want

(It would be best to SUM then ROUND usually)

I think the easiest way to do what you want is just to put the 3 interface names in an OR in the WHERE

SELECT
ROUND((SUM(TotalBytes)/1024/1024/1024),1) AS [Total GB]
FROM Orion.NPM.InterfaceTraffic i
WHERE

(
i.Interface.Name like 'GigabitEthernet1/0/8'

WHERE
i.Interface.Name in ('GigabitEthernet1/0/8', 'GigabitEthernet1/0/1', 'GigabitEthernet1/0/12')

Havnt tried.

Often for this sort of thing you end up needing a GROUP BY, I dont think it's necessary here but if that doesnt look as expected, try

SELECT
ROUND((SUM(TotalBytes)/1024/1024/1024),1) AS [Total GB]
FROM Orion.NPM.InterfaceTraffic i
WHERE

wluther

@chris.mcmorris I do something similar (I think), but have this setup as a daily report, sent out each morning.

I have custom properties on our uplinks, and the report pulls the total in/out peaks for each of the uplinks based on those custom properties. The next part of the report breaks down each uplink, and provides the individual metrics for each.

This is the SWQL I use for the first part of the report. This uses 2 different interface custom properites, "Purpose_Primary" & "Purpose_Secondary". This allows me to easily add or remove interfaces if needed, without needing to adjust the query.

Total_Uplinks_Combined

SELECT
N.NodeID
,DATETRUNC('day', TOLOCAL(N.Interfaces.Traffic.DateTime)) as day
,MAX(N.Interfaces.Traffic.InMaxbps) AS InMaxbps
,MAX(N.Interfaces.Traffic.OutMaxbps) AS OutMaxbps

FROM Orion.Nodes AS N

WHERE
N.Interfaces.CustomProperties.Purpose_Primary = ('WAN_Uplink')
AND N.Interfaces.CustomProperties.Purpose_Secondary = ('Main')
AND TOLOCAL(N.Interfaces.Traffic.DateTime) > GETDATE() - 7
AND DAYDIFF(TOLOCAL(N.Interfaces.Traffic.DateTime),GETDATE()) >= 1
GROUP BY N.NodeID, DATETRUNC('day', TOLOCAL(N.Interfaces.Traffic.DateTime))

This is the SWQL I use for the second part of the report, where it lists out the individual metrics per uplink/interface. This query also uses the same 2 interface custom properties, and for the same reason.

Uplinks

SELECT
N.NodeID
,N.Caption
,N.Interfaces.DisplayName
,DATETRUNC('day', TOLOCAL(N.Interfaces.Traffic.DateTime)) as day
,MAX(N.Interfaces.Traffic.InMaxbps) AS InMaxbps
,MAX(N.Interfaces.Traffic.OutMaxbps) AS OutMaxbps

FROM Orion.Nodes AS N

WHERE
N.Interfaces.CustomProperties.Purpose_Primary = ('WAN_Uplink')
AND N.Interfaces.CustomProperties.Purpose_Secondary = ('Main')
AND TOLOCAL(N.Interfaces.Traffic.DateTime) > GETDATE() - 7
AND DAYDIFF(TOLOCAL(N.Interfaces.Traffic.DateTime),GETDATE()) >= 1

GROUP BY N.NodeID, N.Caption, N.Interfaces.DisplayName, DATETRUNC('day', TOLOCAL(N.Interfaces.Traffic.DateTime))

This example is not exactly the same as what you're asking, but it's what I have. Adam's example will probably give you more insight into what the query likely needs when used the way you're needing.

wluther

Just to clarify, since I'm using a report, I'm able to cheat a little bit and just query to pull a giant bucket of the required data. I then sit back and let the report magic do the brain stuff.

My brain is too shiny and smooth to be able to include all of that stuff properly in the SWQL, so I just get the data far enough and use other methods to finsih the last mile.

chris.mcmorris

Many thanks, Adam - this works very well and thank you for the detailed explanation. Clearly I have lots to learn with SWQL but I'll keep trying / experimenting

Cheers,

Chris

chris.mcmorris

Many thanks for the reply and thanks for sharing. This report looks really interesting and useful - I'll take a look at putting something similar together for our campus LAN building uplinks and our numerous WAN links.

Cheers