This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL Query resource - gives error in website but works in studio

I am trying to get info from this query which works in studio but breaks in webpage...I think because of the where ...(select) statement

SELECT de.ComponentStatusID, de.ColumnSchemaID, de.ColumnType, de.ColumnName, de.MaxNumericData, (SELECT StringData FROM Orion.APM.DynamicEvidence WHERE ComponentStatusID=de.ComponentStatusID and ColumnName=de.ColumnName and columnType=0 and (StringData NOT LIKE '0' and StringData is not NULL) ) as StringData FROM Orion.APM.DynamicEvidence de WHERE de.ComponentStatusID  IN (SELECT top 1 ID FROM Orion.APM.ComponentStatus where ComponentID=27154 order by TimeStamp desc) and Columntype=1

How do I best get the "last component status ID" from one table and apply to where clause of select on another table for the web?

  • I think this is what I was going for...

    SELECT de.ComponentStatusID, de.ColumnSchemaID, de.ColumnType, de.ColumnName, de.MaxNumericData, de2.StringData

    FROM Orion.APM.DynamicEvidence de

        JOIN (select top 1 ID FROM Orion.APM.ComponentStatus where ComponentID=27154 order by TimeStamp desc) cs ON (cs.ID=de.ComponentStatusID and de.columntype=1)

        JOIN Orion.APM.DynamicEvidence de2 on (de2.ComponentStatusID=de.ComponentStatusID and de2.ColumnName=de.ColumnName and de2.columntype=0)

  • Try putting an ORDER BY clause on it.

  • Yea, had that in several iterations of the test.

    My last post errored out as well...jumped the gun on the post. emoticons_confused.png

    it works if I just use order by timestamp desc, but I only want the "last results" for a given component ID which boils down to the top ComponentStatusID.

    I'm just struggling with how to limit it in that fashion.

    it seems I am not able to use a select statement in the WHERE clause nor within a join within the SWQL query resource...

  • I should have included more detail in my previous reply.

    The Custom Query resource always adds a "WITH ROWS x TO y" clause to the end of your query to support paging through the resultset. In SWQL, the WITH ROWS clause can only be used in combination with "ORDER BY", since paging doesn't make any sense when there isn't a defined sort order.

    So if you give Custom Query resource a query that does not have an ORDER BY (on the outer query - ORDER BY on an inner subquery doesn't count), then you will get the rather vague "Error: A query to the SolarWinds Information Service failed." error in that resource on the view.

    The solution is to add an ORDER BY clause to the query. When I tried that with either of the two queries you posted in this thread, they work fine in the Custom Query resource.

  • most Eggggcellent!  Thanks for the quick responses!