Nodes by Country - Modern Dashboard

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

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')

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? 

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

this is the error i am getting - 

this is the error i am getting - 

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.

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

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')

if i wanted to include 3 countries in one widget how would the swql look ?

One widget, just add values with its own SWQL 



Configured like this: 

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

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

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. 

hi - im not sure of your question 

Since you have location information in the name, I assume that you have a format or pattern that you follow when a device is named.

What is the whole format? Can we locate the number more specifically to reduce the errors you talked about? If not, we may want to set a location name in a custom property instead of using the the name.