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
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')
OK, then we shouldn't use the name to sort these out. It will just make too much work over time. It might be right today, but wrong next week.
You can use that location Property and just find the city, and using a case statement map that to the Country. Or if your subnets map well to locations, or countries, consider that as well.
You could also add a property and add in the data manually, but then use it whenever you need it.
OK thanks, that is something i have no knowledge of, thank you for your help - appreciated
I think your first task is to find a way to clearly identify the location of each device in your environment. I've found naming convention to be a very unreliable method as there are various iterations of naming standards across the years. Once you have a solid method of identifying the devices you can use that method to update the "Country" custom property automatically. Then use that Custom Property in your SWQL Filter instead of the naming convention.
Is there a way the SWQL could be amended to only pick up the first number only in the node name in theory anything with 3, 5,7 & 9 would be be displayed
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'
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.
Right all - need to amend the swql to ignore test routers - any ideas how the amendment would look thanks
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.