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.

Help Converting SQL Query to SWQL For Custom Query

Hello, I am trying to convert a query I have confirmed working in the database using SQL into a SWQL query in order for me to place it as a custom query resource on the page.

I am trying to gather specific data from the VOIPJitterOperationResults_Detail, Hourly, and Daily tables.

SELECT COUNT (RecordTimeUtc)*100/3759.0

AS 'SVG1_Weekly'

FROM [SolarWindsOrion].[dbo].[VoipJitterOperationResults_Detail]

WHERE Latency > 120 AND VoipOperationInstanceID like 105

SELECT COUNT (RecordTimeUtc)/552.0*100

AS 'SVG1_Monthly'

FROM [SolarWindsOrion].[dbo].[VoipJitterOperationResults_Hourly]

WHERE AvgLatency > 120 AND VoipOperationInstanceID like 105

SELECT COUNT (RecordTimeUtc)/80.0*100

AS 'SVG1_Quarterly'

FROM [SolarWindsOrion].[dbo].[VoipJitterOperationResults_Daily]

WHERE AvgLatency > 120 AND VoipOperationInstanceID like 105

I've looked all around on thwack and the success center, where people talk about the benefits of using SWQL over SQL, etc. I did not see any general guide on using it to compare specific table data though. I confirmed the COUNT operation is supported in SWQL, perhaps the changes I am doing to the data to get a percent are not working?  I'm dividing the data by the total amount of rows possible and multipkying by 100 to get the percentage of total where this value is matched.

Any help would be great.

  • alphabits​, I ran into the same problem when I first started converting things over.

    SELECT COUNT (RecordTime)*100/3759.0 AS SVG1_Weekly

    FROM Orion.IpSla.JitterOperationResultsDetail

    WHERE Latency > 120 AND OperationInstanceID like 105

    This is the SWQL version of your first query, im sure you can see the differences and match the rest up.

    Now to explain how I got that, the first and most critical part is you have to have SWQL studio installed, otherwise you will never see that the table names don't line up and won't be able to find the SWQL analog.  SWQL isn't acting directly on the SQL tables. It is based on them, but is a layer of abstraction.  It is bundled as part of the SDK here

    Releases · solarwinds/OrionSDK · GitHub

    So once you have SWQL studio loaded up you can see the changes you need to make to get to the right tables, also keep in mind that sometimes they change column names, and also from what I can recall almost everything in SWQL is already in UTC

    pastedImage_8.png

    You also run into little syntax snags here and there, but within SWQL Studio it is usually not too hard to figure out what you need to tweak to get it working.

    -Marc Netterfield

        Loop1 Systems: SolarWinds Training and Professional Services

  • Awesome thank you for the link to SWQL Studio. I'll be able to use that a lot for learning SWQL. I ran the query you posted in there and it worked successfully. I did find the tables and their naming differences. When I ran it on the "custom query" table in NPM it threw an error though "Error: A query to the SolarWinds Information Service failed."  Should I be adding anything to the query, or perhaps there is some limitation on the "custom query" resource that I am not aware of?

    When I changed it to just a select statement it pulled the values for when the criteria was matched, but when I use count it returns an error, even though it works in SWQL Studio.

    SELECT RecordTime
    FROM Orion.IpSla.JitterOperationResultsDetail 
    WHERE Latency > 120 AND OperationInstanceID like 105

    pastedImage_2.png

    Original with COUNT used:

    pastedImage_3.png

    Here ran successfully in Studio:

    pastedImage_4.png

  • I've run into this problem before, for some reason queries that use count/round/sum etc have errors when you try to load them into the website unless you put an order by clause on them, forgot about that. 

    So in this case it is an easy fix, add this as the last line

    ORDER BY SVG1_Weekly

    -Marc Netterfield

        Loop1 Systems: SolarWinds Training and Professional Services