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

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