Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Subqueries not supported in SWQL Orion Platform (not EOC)

I've had a recent problem with a SWQL query where there were a number of subqueries. I then wanted to include a new subquery with a SELECT bringing information from the Orion.Groups/Orion.Containers tables. As soon as I did that I got the "Subqueries not supported" message.

I validated everything and tested multiple times with no luck. It seemed to fail on this portion

I could get it to write output based on everything else, so the "when" portion and "then" was all working fine.

I ended up having to rewrite the whole query using a number of JOINS instead.

I have seen the EOC thread already https://thwack.solarwinds.com/product-forums/the-orion-platform/f/orion-sdk/97024/subqueries-are-not-supported-running-queries-in-eoc and posted in there, but we were seeing this same problem in Orion Platform.

Any thoughts? Is this a bug or just an anomaly with the conversion that goes on in the background when it comes to SWQL?

Find more posts tagged with

swql query

SWQL

subqueries

Comments

trilobite_rex

Just encountered this today. trying to get a special view set up. Just the container and containermembers tables are busted.

trilobite_rex

01459286 has been opened with support.

sum_giais

Very weird, I was able to reproduce this as well... and if I change the subquery to something on the Orion.Nodes table it's not an issue.

It's not ideal but have worked around this before with turning those subqueries into a left join of the query if it's possible. May need to rejig your query though to do so. Something like this would need to be joined in after you access/join in the asup data.

LEFT JOIN (SELECT c1.Name, c1.Uri FROM Orion.Container AS c1) AS c2 ON c2.Uri = asup.EntityUri

Then the case statement would be:

when charindex('/containerID=',asup.Entityuri) > 0 then c2.Name

trilobite_rex

This type of request is usually customizations that are not supported on our end. We only provide some guides as our best effort on customization requests.

Could you please share with us the SWQL query you created for a view to further check it on our end and test it as well?

If you have any further questions about this, don't hesitate to reply back on this thread for further assistance.

Thank you and have a great day ahead!

If one of your tables isn't accessible, then it's hardly a customization.

tobyw_loop1

I had exactly the same response...

This does sound like a bug, I will see what I can do & if we have any contacts that could potentially escalate.

tobyw_loop1

I ended up doing the exact same thing... messy, but it worked!

shuth

I had a client with a similar issue when they were upgraded to 2023.3.1. They use the All Nodes widget with queries pulling in NodeIDs from groups/containers but with SQL. Post-upgrade all of the widgets were broken with errors about the filter. Turns out that on top of converting the query from SQL to SWQL, I found that the filter didn't like native SWQL JOINs.

-----------------

I assumed it was because the widgets required SWQL I modified to the filter query from SQL to SWQL (Orion.Container instead of dbo.Containers, etc) but the query still failed. In SWQL Studio I got a "subqueries not supported" error.

E.g. from my lab environment. The basic highlighted query returns a result.

If I run the whole query I get the subquery not supported error:

However a basic subquery still works:

I found that if I manually JOINed all the Container tables rather than use the native SWQL JOINs, the query worked fine.

planglois

Thank you very much for this post... I've been raging behind my keyboard for the past hour or two, trying to have UnDP table values displayed as a real table.

tobyw_loop1

Glad it's still helping people

marcrobinson

This thread explains a few things... I have viewed these as limitations on swql. Instead I use joins that simply work, even if it is ugly. Subqueries and the inherently linked properties have been hit or miss - particularly on where you use them in your swql statements. I will start tracking them when I run into them now and add them here.

tobyw_loop1

I had asked previously whether there were any limitations with the number of subqueries SWQL could handle but didn't really get an answer.