SWQL Query Help - Orion Maps Lookup

Hello ultimate goal: I want to make a SWQL custom query widget that will be able to have a user enter a node IP address and the output of the query will be links to which Orion Maps include that node.

I found in the database the information needed is at least in part contained in the Maps_Projects table. The Display Name is the title of the map and the Data field contains relevant map information including the node ID which would be searchable. So far I have been able to run the following query and get an output of the maps that contain the Node ID = 315.

SELECT TOP 1000 * FROM [dbo].[Maps_Projects] where Data like '%"id": "0_Orion.Nodes_315",%'

When I try running similar queries in SWQL in the custom query widget I get there was an error processing the request:

I have tried a number of possibilities to get an output. I referenced this article https://documentation.solarwinds.com/en/success_center/orionplatform/content/core-fusion-swql-data-model-examples.htm for some examples and they do work but when I try changing Orion.Nodes to Orion.Maps or Orion.Maps_Projects or even Orion.NodesData.

I am guessing for some reason the data in these locations is not accessible and maybe only some locations like Orion.Nodes is accessible. I would greatly appreciate any feedback what might be going wrong and if any of what I want to achieve is possible or not.

Find more posts tagged with

maps

orion

SWQL

Custom Query

Accepted answers

All comments

wluther

@shyde The basic SWQL query for this should be:

SELECT TOP 1000 AccountID, ProjectID, UpdateDateTime, CreateDateTime, Favorite, TrackHistory, Data, GraphDefinition, EntityCount, GraphOPID, CommitId, DisplayName, Description, InstanceType, Uri, InstanceSiteId
FROM Orion.Maps.Projects
WHERE Data LIKE '%Nodes_315%'

If you really wanted to put some time into it, I'd guess you could build out something neat by reading and manipulating the XML/JSON formatted data. Another option would be to use some sort of systemic naming convention for the map projects, grouping/listing/searching them that way.

I hope this helps. If not, let us know and maybe we can find a better solution.

Thank you,

-Will

wluther

@shyde

You could also join the maps.projects and nodes tables together by connecting them to the maps.edges table. (I'm not 100% on the query, but it may help point you in a better direction.)

SELECT DISTINCT
p.ProjectID
,e.SourceEntityID
,n.Caption
,n.IPAddress
,p.DisplayName
FROM Orion.Maps.Projects AS p
JOIN (SELECT ProjectID, SourceEntityID FROM Orion.Maps.Edges) AS e ON p.ProjectID=e.ProjectID
JOIN Orion.Nodes AS n ON e.SourceEntityID=n.NodeID

I'm sure there is a better query out there somewhere, but this should tie it together well enough... At least for a rough first pass.

(Sorry for not including a screenshot. Not sure why, but it does not appear to be liking any images I attempt to include.)

wluther

Sorry, again, I forgot to include the details URL for the maps, which should allow you to click into them if formatted correctly.

SELECT DISTINCT
p.ProjectID
,p.Graph.DetailsUrl AS [_LinkFor_ProjectID]
,e.SourceEntityID
,n.Caption
,n.IPAddress
,p.DisplayName
FROM Orion.Maps.Projects AS p
JOIN (SELECT ProjectID, SourceEntityID FROM Orion.Maps.Edges) AS e ON p.ProjectID=e.ProjectID
JOIN Orion.Nodes AS n ON e.SourceEntityID=n.NodeID

shyde

Wow! Thank you for all the help already! Indeed I got to a usable point but looking to make some upgrades I'll post any progress I make and if you are able to assist that would be very much appreciated!

When I ran your latest query I got:

Not sure if the ProjectID column should have had URLs or not it seems this was the project id and not the Graph.DetailsUrl that we were expecting. It does have it as blue text so I am guessing that is from the p.Graph.DetailsUrl AS [_LinkFor_ProjectID]

What I am using so far now based on what I learned from you is:

SELECT TOP 100
p.DisplayName
,n.Caption
,n.IPAddress
FROM Orion.Maps.Projects AS p
JOIN Orion.Nodes AS n ON ${SEARCH_STRING}=n.NodeID
WHERE p.Data LIKE '%Nodes_${SEARCH_STRING}%'

If the user knows the Node ID it allows them to search all of the map project data fields for any maps that include the Node ID. This way if we must remove a node to re-add to fix some kind of issue that the normal rediscovery doesn't fix for some reason. We can now type the Node ID in the search find which maps its included in and be able to easily restore the maps after delete and re-add. Also if a user knows the Node ID and wants to troubleshoot its connectivity again they can find out which maps they may want to look at for troubleshooting. Really happy with this already.

The queries you gave are very interesting I think it is largely what I am looking for but some edits may be needed.

What I hope to accomplish next is include another column next to Display which would have the URL. I haven't found the URL in the database but we dont create maps that often and I am wondering if it would be possible to use a lookup table for the URL in SWQL. Example if it would have DisplayName = 'map x' than print out a web link to map x based on a static lookup table in the query - not sure if this is at all possible.

Another goal is to have the search accept a node name instead of just the node ID. I tried going toward this goal with:

SELECT TOP 100
n.Caption
,n.IPAddress
,n.NodeID
FROM Orion.Nodes AS n
WHERE n.Caption LIKE '%${SEARCH_STRING}%'

However I can't figure out how to join the Orion.Maps.Projects from the query. I think I need to use the SourceEntityID that you had referenced earlier. Do you know if SourceEntityID = NodeID? Once joined hopefully I can use an additional WHERE command to reduce the output to only display maps where the NodeID is in the Maps.Projects.Data field.

Thanks again for all the help! If you have suggestions on the remaining two goals please let me know.