Custom SWQL Query for Dashboards

Danielle, first step is making sure you have the SWQL Studio program installed, debugging these things is near impossible without it.  Get it as part of the SDK here Releases · solarwinds/OrionSDK · GitHub

The error messages you get in that tool are much more helpful than the query failed one the web console provides.

Assuming all you want is the node's name and physicallocation then this should work.  Feel free to play with this and add columns if they would be useful to you.  The bit in line 8 allows it to match up the query to whatever group you happen to be on the details page for, without it the query would just show all the nodes in any group.

select n.Caption, cp.PhysicalLocation
from orion.nodes n
join orion.nodescustomproperties cp on cp.nodeid=n.nodeid
join Orion.ContainerMembers c on c.memberprimaryid=n.nodeid
where MemberEntityType like 'orion.nodes'
and c.containerid like '${id}'
order by cp.owner, n.caption

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

Thanks, I will work with this.
I have the Orion SDK installed, but can't get it to connect to my database.

You should point it at the primary orion application server, not the db directly.

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

Ok, thanks. I was able to login to the SWQL Studio via the app server.

I think the reason your example statement doesn't work is because "n.Caption" and "cp.PhysicalLocation" do not live in the same table. ( I get this is why you have the JOIN statement, but they are not joining.)
"n.Caption" is in Orion.Nodes, and "cp.PhysicalLocation" is in Orion.NodesCustomProperties".

I can get Caption and IP address out of "Orion.Nodes" and directly translate that into the SWQL custom query no problem. But I need the other info joined so I can filter it by physical location.

Can you screenshot the exact error you get when you try to join those tables?  I tested that query in my lab when I posted it and use variations of it all the time so I suspect there may be a typo in the mix somewhere.

If you were trying to do the entire query i gave you from within the SWQL studio it would generate an error on line 8 because that bit uses context info from your browser to show info specific to the page you are on.  To test that kind of thing in the Studio you can either comment out the line, add -- to the start of it, or you can hard code the ${id} part to the containerid number of one of the groups you have already defined on your server.

Try this one, if it gives you an error please attach it.

1. select n.Caption, cp.PhysicalLocation 
2.  
3. from orion.nodes n 
4. join orion.nodescustomproperties cp on cp.nodeid=n.nodeid 
5. join Orion.ContainerMembers c on c.memberprimaryid=n.nodeid 
6.  
7. where MemberEntityType like 'orion.nodes' 
8. --and c.containerid like '${id}' 
9.  
10. order by cp.owner, n.caption

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

ohhhhhh gotcha, thats my mistake. testing in my lab i was using a property called owner because i didnt have physicallocation.  I forgot to remove that from the order by statement on line 10, this should clear that up for you.  The join was always working, i just referenced a column that doesnt exist in your db to sort with.  Test it in swql studio like this, and when you put it on the group details page remove the -- from line 8

1. select n.Caption, cp.PhysicalLocation
3. from orion.nodes n
4. join orion.nodescustomproperties cp on cp.nodeid=n.nodeid
5. join Orion.ContainerMembers c on c.memberprimaryid=n.nodeid
7. where MemberEntityType like 'orion.nodes'
8. --and c.containerid like '${id}'
10. order by cp.PhysicalLocation, n.caption

OMG, yes. YES! Thank you. This worked. Exactly what I was looking for. Thanks so much

So FWIW, I actually figured this out. Feel free to use and share:

select n.Caption, cp.<YOURCUSTOMPROPERTY> from orion.nodes n join orion.nodescustomproperties cp on cp.nodeid=n.nodeid join Orion.ContainerMembers c on c.memberprimaryid=n.nodeid where MemberEntityType like 'orion.nodes' and cp.<YOURCUSTOMPROPERTY> = '<YOURLOCATIONNAME>' and c.containerid like '${id}' order by n.Caption, cp.<YOURCUSTOMPROPERTY>*

That version will give you a very simple, clean list of the nodes you want based on their "location", or whatever "sub-grouping" you design via Custom Properties. However, I was annoyed by the fact that I could not get "tool-tip" hover over information in that list, like I can with the standard Group members list. So I modified it:

from orion.nodes n join orion.nodescustomproperties cp on cp.nodeid=n.nodeid join Orion.ContainerMembers c on c.memberprimaryid=n.nodeid where MemberEntityType like 'orion.nodes' and cp.<YOURCUSTOMPROPERTY> = '<YOURLOCATIONNAME>' and c.containerid like '${id}' order by n.Caption, cp.<YOURCUSTOMPROPERTY>, n.DetailsUrl, n.StatusDescription*

This should give you the tool tip hover over, as well as the pretty green (or yellow, or whatever status) LED icon. Only annoying thing I haven't figured out is how to get the LED's to format to the left of the Caption (node name). Instead they hover over the top of each name. Not a big deal though.