nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Nodes by Country - Modern Dashboard

jasonf1971

Hi - is there a way nodes can be identified via Country via swql on a modern dashboard

so if a node contains the letter 3 it is UK based etc as per below

3 - UK

5 - Roi

7 - NL

9 - BE

thanks in advance

Find more posts tagged with

Accepted answers

jm_sysadmin

I'd filter results with the where added to first, and count them after, something like this.

Select Count(t0.Caption) AS KPI_Numbe, t0.Location
From 
(SELECT 
N.Caption, 
Case 
    when N.Caption like '%3%' Then 'UK'
    when N.Caption like '%5%' Then 'Roi'
    when N.Caption like '%7%' Then 'NL'
    when N.Caption like '%9%' Then 'BE'
Else 'unknown'
End as [Location]
-- ,
-- Case
--    when SubString(N.Caption, 2, 1) like '3' Then 'UK'
--    when SubString(N.Caption, 2, 1) like '5' Then 'Roi'
--    when SubString(N.Caption, 2, 1) like '7' Then 'NL'
--    when SubString(N.Caption, 2, 1) like '9' Then 'BE'
-- Else 'unknown'
-- End as [Location2]

FROM Orion.Nodes N 
WHERE 
    Status=2
    AND (nodes.customproperties.AssetTag='Store Router')
) as t0
Group by t0.Location

I commented out the column for location2 with -- since I didn't know what made more sense, I just picked one.

Does this help?

dgsmith80

Excellent then yes it's much easier. Basically '9%' becomes any word beginning with 9.

So '3%' for UK, '5%' for ROI etc

SELECT
Count(N.NodeID) as [NL]
FROM Orion.Nodes N
WHERE N.Caption like '9%'
AND N.Status = '2'
AND N.CustomProperties.AssetTag like 'Store Router'

jasonf1971

Perfect - works great thank you

dgsmith80

SELECT
Count(N.NodeID) AS [UK]
FROM Orion.Nodes N
WHERE N.Status = '2'
AND N.CustomProperties.AssetTag LIKE 'Store Router'
AND (N.Caption LIKE '3%' OR N.Caption LIKE '4%')

As your doing an OR as well as an AND in your WHERE statement you just need to break it up. The (brackets) means it first looks for the 3% OR 4% in the Caption as well as Status 2 AND CP Store Router.

All comments

jm_sysadmin

Sure, below I have a query, the first column is the node caption, and I try to find the location using a case statement in 2 ways. Location1 looks for the numeral anywhere in the name, Location2 looks for it as the second character with the substring function.

SELECT N.Caption, 
Case 
    when N.Caption like '%3%' Then 'UK'
    when N.Caption like '%5%' Then 'Roi'
    when N.Caption like '%7%' Then 'NL'
    when N.Caption like '%9%' Then 'BE'
Else 'unknown'
End as [Location1]
,
Case
    when SubString(N.Caption, 2, 1) like '3' Then 'UK'
    when SubString(N.Caption, 2, 1) like '5' Then 'Roi'
    when SubString(N.Caption, 2, 1) like '7' Then 'NL'
    when SubString(N.Caption, 2, 1) like '9' Then 'BE'
Else 'unknown'
End as [Location2]

FROM Orion.Nodes N

jasonf1971

Sorry should have been more clearer also needs it to contain the following -

SELECT COUNT(1) AS KPI_Numbe from Orion.Nodes

WHERE Status=2

AND (nodes.customproperties.AssetTag='Store Router')

jm_sysadmin

I'd filter results with the where added to first, and count them after, something like this.

Select Count(t0.Caption) AS KPI_Numbe, t0.Location
From 
(SELECT 
N.Caption, 
Case 
    when N.Caption like '%3%' Then 'UK'
    when N.Caption like '%5%' Then 'Roi'
    when N.Caption like '%7%' Then 'NL'
    when N.Caption like '%9%' Then 'BE'
Else 'unknown'
End as [Location]
-- ,
-- Case
--    when SubString(N.Caption, 2, 1) like '3' Then 'UK'
--    when SubString(N.Caption, 2, 1) like '5' Then 'Roi'
--    when SubString(N.Caption, 2, 1) like '7' Then 'NL'
--    when SubString(N.Caption, 2, 1) like '9' Then 'BE'
-- Else 'unknown'
-- End as [Location2]

FROM Orion.Nodes N 
WHERE 
    Status=2
    AND (nodes.customproperties.AssetTag='Store Router')
) as t0
Group by t0.Location

I commented out the column for location2 with -- since I didn't know what made more sense, I just picked one.

Does this help?

jasonf1971

i have tried it but it does not like the swql im not sure why not

jasonf1971

this is the error i am getting -

jm_sysadmin

I didn't have that custom property so I tested with it comment out, I didn't edit your line to use my alias for nodes. We may also want to replace '=' with 'like' since its a string comparison.

Find: AND (nodes.customproperties.AssetTag='Store Router')

Replace with: AND (N.customproperties.AssetTag like 'Store Router')

I hope that fixes it.

jasonf1971

Yes that works a treat thank you, is there a way that this can be output to the kpi widget for each country ?

jm_sysadmin

Each box in the kpi widget is its own SWQL statement, so just filter the where with the same logic as the case statement, and use the label. Something like:

SELECT 
Count(N.nodeid) as [UK]
FROM Orion.Nodes N 
WHERE     
    N.Caption like '%3%' 
    and Status=2   
    AND (n.customproperties.AssetTag like 'Store Router')

jasonf1971

I Have tried the above but its picking up nodes that are down even though they are not mainly for ROI & BE

jasonf1971

This is the swql i am using for UK & NL and it works fine

SELECT
Count(N.nodeid) as [NL]
FROM Orion.Nodes N
WHERE
N.Caption like '%7%'
and Status=2
AND (n.customproperties.AssetTag like 'Store Router')

SELECT
Count(N.nodeid) as [UK]
FROM Orion.Nodes N
WHERE
N.Caption like '%3%'
and Status=2
AND (n.customproperties.AssetTag like 'Store Router')

But when using it for ROI & BE it flagging 1 node down in each Country which is incorrect

jm_sysadmin

What's the pattern for your naming format? If we know where to look for the number, it may iron out the other stuff. when you look for '%3%' the 3 can be any character in the string.

Nattapong.ng

I use SWQL in modern dashboard, But I save my city data in Table Orion.NodesCustomProperties

SELECT TOP 10 ncpg_province, count(ncpg_province) as num_province
FROM Orion.NodesCustomProperties
GROUP BY ncpg_province
ORDER BY num_province DESC

Fbuttaro

Yes thank you