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
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
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…
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…
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.…
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:
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK© online community. More than 180,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.