2 Replies Latest reply on Jul 19, 2016 7:28 PM by mesverrum

    SWQL Query Multiple Select Problem

    alphabits

      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:

       

        • Re: SWQL Query Multiple Select Problem
          mesverrum

          SWQL is a little less flexible with complex queries than SQL, you can do them but sometimes the formatting needs more than just tweaking the table names. 

           

          In this case you need to do that as a nested select, or join the selections, what you have is more a series of separate queries.  I'm about to start working with my client again so I can't rewrite it for you right now to show, but the methods mentioned in this article would basically work, but there are other valid ways to pull it off

          Using nested SELECT - SQLZOO

           

          -Marc Netterfield

              Loop1 Systems: SolarWinds Training and Professional Services

           

            • Re: SWQL Query Multiple Select Problem
              mesverrum

              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