Nodes by Country - Modern Dashboard

Question

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

jm_sysadmin · Accepted Answer

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 · Answer

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 · Answer

Perfect - works great thank you

dgsmith80 · Answer

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.

Nodes by Country - Modern Dashboard

Top Replies