SWQL Query Multiple Select Problem

Question

Hello, I am trying to generate a custom SWQL query so that I can devise the total percent that a voip operation is running under 10% normal latency. (Below running in SWQL Studio)

I can run the queries separately and everything works fine.

When I combine the queries that is when it hangs that the separating comma. Is there another character I should use here?

I can run a single shorter query where I manually input the amount of rows, but the problem is sometimes the row count changes, making the percent calculated incorrect. The above query works in SQL, when I change the column names.

Here is the top query above working in SQL with the table and column names changed as needed:

mesverrum · Accepted Answer

Okay so I had time to sit down and play with this one to get it working

select ((select count(recordtime)*100 as normallatency
    from orion.ipsla.jitteroperationresultshourly
    where avglatency <143 and operationinstanceid like 91)/(count(recordtime))) as Percent_Normal
from orion.ipsla.jitteroperationresultshourly
    where operationinstanceid like 91
order by percent_normal

This is what I got that works in my lab (except i changed the specific threshold and operationid so i could get hits)

I ran into a few swql quirks that have bit me a few times in the past so I'll explain those.

I'm assuming that since SWQL is intended to be used specifically with the solarwinds database it doesnt like it when we dont define the whole query as being FROM an actual table on the database. I tried a few ways around this but that seemed to be how it is.

Another gotcha is that when doing math SWQL always sticks with whole integers and drops any remainders unless you let it know that you want it to get into floating point decimals. In simple cases you just add a .0 to the end of whatever number you want to divide/multiply by (ie 10/11.0=0.90909090909 etc), but in this case since I didnt have that option I decided to inflate the numerator by 100 to end up with a percentage. If you did it the way you originally wrote it then it was always 0 because 10 / 11 is never a whole integer. Once you get it into doing decimal math you usually have to do a round function or it will give you unnecessarily precise figures.

The other sticking point, which I suspect you already knew, is that counts() and such won't display on the web console without an "order by", even in cases where they aren't really needed. Without them the query works in the studio but fails in browser.

-Marc Netterfield

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems