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 - 

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: