Using SQL to Obtain Count of All Interfaces in a VLAN Per Group

Hey folks,

Hoping y'all can help me out a bit here - I'm a bit of a novice when it comes to databases, and I'm hitting some snags with a business request that's come in. We're looking to create a report that outputs sums of interfaces in a given VLAN, per node, grouped by their container. I can get somewhere with SWQL to at least output all the interfaces we're interested in, but it appears that I might need subqueries in order to accomplish this, and I'm a bit out of my depth.

I can at least output the interfaces we care about in SWQL with the below query:

SELECT TOP 1000 c.Container.Name, n.NodeID, n.Caption, m.Interface.Caption, m.VlanID 
FROM Orion.NodePortInterfaceMap AS m 
LEFT JOIN Orion.nodes AS n ON m.NodeID = n.NodeID 
LEFT JOIN Orion.ContainerMembers AS c ON m.NodeID=c.MemberPrimaryID
WHERE m.Interface.InterfaceType != 53 AND m.Interface.AdminStatus = 1
GROUP BY c.Container.Name

This does seem to spit out the list of interfaces per node with their corresponding VLAN ID, I'm just missing the piece to actually add 'em up with COUNT and group them correctly. Grateful for any and all advice!

Find more posts tagged with

Reports

sql

SWQL

Reporting

swql_query

sql_query

Accepted answers

All comments

drevollo

Is this what you need?

SELECT t.Caption, t.Vlan, COUNT(t.intcaption) AS [countints] FROM (
    SELECT c.Container.Name AS [Name], n.NodeID AS [NodeID], n.Caption AS [Caption], m.Interface.Caption AS [intcaption], m.VlanID AS [Vlan]
    FROM Orion.NodePortInterfaceMap AS m 
    LEFT JOIN Orion.nodes AS n ON m.NodeID = n.NodeID 
    LEFT JOIN Orion.ContainerMembers AS c ON m.NodeID=c.MemberPrimaryID
    WHERE m.Interface.InterfaceType != 53 AND m.Interface.AdminStatus = 1
    GROUP BY c.Container.Name) t
GROUP BY t.Caption, t.Vlan
ORDER BY t.Caption, t.Vlan

numinous

Hey! Sorry for the delay in getting back to you. I gave this a go but it seems to give me some bizarre and incorrect counts, and on a per-node basis. I'm trying to get "interfaces, per group, per VLAN" and despite my best tinkering, I can't seem to get the report quite right.

The above query seems to give me a per-node breakdown with wildly varying interface counts, and only reports one VLAN per node. I tried using some of the ContainerMemberSnapshots parameters to map nodes to their parent container, but after a long weekend I've forgotten what I tried haha.

I'm currently working off that as a base though, with the below query:

SELECT t.Caption, t.Vlan, COUNT(t.intcaption) AS [countints] FROM (
SELECT o.Name AS [Name], n.NodeID AS [NodeID], n.Caption AS [Caption], i.Caption AS [intcaption], m.VlanID AS [Vlan]
FROM SolarWindsOrion.dbo.NodePortInterfaceMap AS m
LEFT JOIN SolarWindsOrion.dbo.NodesData AS n ON m.NodeID=n.NodeID
LEFT JOIN SolarWindsOrion.dbo.Interfaces AS i ON i.NodeID=m.NodeID
LEFT JOIN SolarWindsOrion.dbo.ContainerMemberSnapshots AS c ON m.NodeID=c.ContainerMemberSnapshotID
LEFT JOIN SolarWindsOrion.dbo.Containers AS o ON c.ContainerID=o.ContainerID
WHERE i.InterfaceType = 6 AND i.AdminStatus = 1 AND m.PortType = 1 AND n.NodeID=c.ContainerMemberSnapshotID
) t
GROUP BY t.Caption, t.Vlan
ORDER BY t.Caption, t.Vlan

numinous

Alright, I think I'm inching a bit closer - this SWQL query is pulling up our monitored interfaces per node, but I haven't yet figured out how to summarize their count per Container. Any advice?

SELECT c.Container.Name, n.NodeID, n.Caption AS Node, m.Interface.Caption AS Interface, m.VlanID, m.IfIndex FROM Orion.NodePortInterfaceMap AS m left join Orion.nodes AS n ON m.NodeID = n.NodeID left join Orion.ContainerMembers AS c ON m.NodeID=c.MemberPrimaryID WHERE m.Interface.AdminStatus = 1 AND (m.PortType = 1 OR m.PortType = 0) ORDER BY c.Container.Name ASC