SWQL query for bandwidth utilization depends on interface list

Here's a query that does more or less what you are looking for. You will need to adjust it to what you mean by "bandwidth utilization" (I just used Average BPS) and things like the time limit (I used last 7 days) and rollup level (I used daily rollup). The easiest way to deal with your three kinds of devices was to just repeat the query three times and union them together.

SELECT N.NodeID, N.Caption, N.Interfaces.InterfaceName, DATETRUNC('day', N.Interfaces.Traffic.DateTime) as day, AVG(N.Interfaces.Traffic.Averagebps) AS Averagebps

FROM Orion.Nodes N

WHERE N.NodeID IN (SELECT M.NodeID FROM Orion.Nodes M WHERE M.Interfaces.InterfaceName = 'e0/0.1')

    AND N.Interfaces.Traffic.DateTime > GETUTCDATE() - 7

    AND N.Interfaces.InterfaceName IN ('e0/0.1', 'e0/2', 'e0/3')

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

UNION ALL (

SELECT N.NodeID, N.Caption, N.Interfaces.InterfaceName, DATETRUNC('day', N.Interfaces.Traffic.DateTime) as day, AVG(N.Interfaces.Traffic.Averagebps) AS Averagebps

FROM Orion.Nodes N

WHERE N.NodeID IN (SELECT M.NodeID FROM Orion.Nodes M WHERE M.Interfaces.InterfaceName = 'e0/0.2')

    AND N.Interfaces.Traffic.DateTime > GETUTCDATE() - 7

    AND N.Interfaces.InterfaceName IN ('e0/0.2', 'e0/3', 'e0/4')

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

)

UNION ALL (

SELECT N.NodeID, N.Caption, N.Interfaces.InterfaceName, DATETRUNC('day', N.Interfaces.Traffic.DateTime) as day, AVG(N.Interfaces.Traffic.Averagebps) AS Averagebps

FROM Orion.Nodes N

WHERE N.NodeID IN (SELECT M.NodeID FROM Orion.Nodes M WHERE M.Interfaces.InterfaceName = 'e0/1.1')

    AND N.Interfaces.Traffic.DateTime > GETUTCDATE() - 7

    AND N.Interfaces.InterfaceName IN ('e0/1.1', 'e0/0', 'e0/5')

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

)

Thanks, this is exactly what I want!

Could you please help me to deal with this query in Reports - when I create an report and paste query to it (it works perfectly in SWQL Studio) I get an error "Query is not valid" without any explanation

Hmm, this works when I try it. What version of NPM are you using?

I'm using NPM 12.1

I was able to track down a bug matching this description in our system. It is marked as fixed in Orion Platform 2017.3 (which is part of NPM 12.2). That explains why it worked when I tried the query in a report.