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

SELECT
Count(N.nodeid) AS [UK]
FROM Orion.Nodes N
WHERE N.CustomProperties.Country = 'UK'
AND N.Status=2

Can you expand on what you mean? Maybe with an example of the output hostname?

for example node named 9007 -  can the swql be amended so it only picks up the first number currently swql is picking up all numbers and so is alerting for NL & BE 

Is the 9 always the first character of the hostname or is it xxx907xxx etc? If it's always the 4th letter in the hostname or can it vary? Is it always 4 digits in the hostname like 9000 - 9999 or 3024 etc

the first character 

3 - UK

5 - Roi

7 - NL

9 - BE

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'

Perfect - works great thank you 

Need to make a slight amendment have found out some UK have a prefix of 4 rather than 3 how do i combined the swql to include 3 & 4 thanks 

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.

Thank you - now working with the amendments

Thank you - now working with the amendments