cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Need simple SWQL query help!

Would like to use swql to return the following:

NodeID, NodeName, followed by all custom properties that are not NULL or BLANK.

Has anyone done this with SWQL?   Reason for SWQL is so that I can create a custom SWQL resource with a search bar as I need to be able to query large sets of nodes with similar naming conventions.   

Please assist if possible.

0 Kudos
3 Replies
Level 12

1) You have to specify each custom property you want to display.  I mean there is almost always a way... but no, that would be a horrific swql.

2) the search swql is a complete and independent query.

So the custom SWQL would look like:

select n.NodeID, n.NodeName, n.CustomProperties.something, n.CustomProperties.somethingelse, n.CustomProperties.theotherthing

from Orion.Nodes n

and the search SWQL query would look like:

select n.NodeID, n.NodeName, n.CustomProperties.something, n.CustomProperties.somethingelse, n.CustomProperties.theotherthing

from Orion.Nodes n

where n.nodename like '%${SEARCH_STRING}%'

And you can decide if you want to have the %'s around the search string or not.

0 Kudos

So I tried

Select

      n.NodeID,

From

     Orion.nodes n

And I get an error message

No Viable alternative at input 'FROM' in Select clause.   

I really just dont get this SWQL - whats up with the n after Orion.Nodes

I see in the SWQL studio that there is another table called Orion.NodesCustomProperties I am afraid I need a Join command.   I can do this in SQL but not SWQL.  

0 Kudos

SWQL has almost the same conventions as T-SQL because ultimately it gets converted by the info service into T-sql.

You had an error because you put an extra comma after the nodeid and didn't specify any other columns.

The n after nodes is just an alias for the table name.

You can inner and left join in SWQL the same as SQL, but if you are looking in SWQL studio you will notice many tables have a list of values with a little chain link icon next to them, those are joins that are already completed for you by the devs, so to get from nodes to nodescustomproperties all you need to do is

select n.nodeid, n.customproperties.yourpropertyname

from orion.nodes n

- Marc Netterfield, Github