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

Sum Count from two Entities

Hi, need to sum the counts of two diferent entities.

Here the queries I'm using to get the count of monitored nodes and the one used to get the total number of switches that are stacked across the network.

How to get the sum of this in a single query?

SELECT COUNT(NodeID) as value 
FROM Orion.Nodes WHERE (SysName LIKE '%branch%')

SELECT COUNT(DisplayName) as value  
FROM Orion.NPM.SwitchStackMember  
WHERE DisplayName LIKE '%branch%'

Thanks!

Find more posts tagged with

Accepted answers

All comments

marcrobinson

This is an ugly answer. I was able to use subqueries, an arithmetic function, top function and converting the result to numeric. The top and numeric conversion I cannot explain other than I received errors without multiplying by 1.0 and 4000 repeats without the Top1. I changed your queries to values that I could test, but the following may work for you. I can hope that someone with better swql skills than me will provide a better solution.

SELECT TOP 1
(SELECT COUNT(NodeID) as values
FROM Orion.Nodes WHERE SysName LIKE '%branch%')
+
(SELECT COUNT(DisplayName) as values
FROM Orion.NPM.SwitchStackMember
WHERE DisplayName LIKE '%branch%')*1.0
AS AllTotals
from orion.nodes

mesverrum

Don't have a data set to confirm this, but at a glance I would assume there is duplicates being double counted. The master unit from a switch stack would show up on both the nodes table and the switchstackmember table. Just a word of caution about accuracy of the contents that this kind of query would spit out.

3wal

Thanks @marcrobinson this worked for me. Now is juts a matter to clean the query a bit. Thanks a lot!

marcrobinson

@mesverrum Thank you, I was not thinking about that, just looking at how to get this work in SWQL.

@3wal Try adding this to the second query's where statement:

and ss.Role >1

Looks like Role = 1 matches the masternumber in the npm.switchstack entity.

adam.beedell

I'd structure this as 2 case statements:

CASE WHEN DisplayName LIKE '%branch%' then 1

ELSE 0...

, CASE WHEN SysName LIKE '%branch%' then 1
ELSE 0...

Then Sum the results

As a 3rd alternative you could add the case statement in additon to whatever information you wanted to pull

CASE WHEN DisplayName LIKE '%branch%' then 'branch'

WHEN SysName LIKE '%branch%' then 'otherbranch'
ELSE 0

end as division

then group by that division column (probably in a subquery), then you can count whatever too.

sum_giais

Jumping in a little late, but looks like SWIS does some of the work for you if I'm not mistaken with using the SwitchStack navigational property to MemberCount and then you would just need to sum it up.

Here's a way to show both those in a table with a union.

SELECT 'Total Branch Nodes' AS [metric], COUNT(n.NodeID) AS [data]
FROM Orion.Nodes AS n
WHERE n.SysName LIKE '%branch%'
UNION ALL
(
    SELECT 'Total Branch Switches' AS [metric], SUM(n.SwitchStack.MemberCount) AS [data]
    FROM Orion.Nodes AS n
    WHERE n.SwitchStack.MemberCount IS NOT NULL
    AND n.DisplayName LIKE '%branch%'
)