Best way to do subqueries?

Answered

Follow

Josh Lesch

• September 11, 2015 21:53

I am wanting to get pass percentage of our builds. So I need to do two queries, one to get the total number of builds and one to get total failed builds. From there I can get the percent of pass rate for builds.

Some contexts of what my logs look like. Each log entry is a test that has pass/fail as an outcome. There are multiple tests per build. So if one of the tests fail, we consider that build a failure.

So if i do a count_distinct(buildNumber) that gets the number of total builds pass or failed.

Then if do a where outcome="failed" and then count_distinct(buildNumber) that will get only builds that have a failed test.

From there I can subtract the failed builds from the total builds to get a count of passed builds.

How can I do this in one query?

0

• 

  David Wynn

  • September 11, 2015 22:22

  Josh,

  The best way to do this in a single query is to use an if statement to turn the pass/fails into numbers. From there you can use a dummy variable coupled with the max operator to count up the failed and passed builds. An example query I ran on some fake data is below:

  *
  | parse "* * *" as build,test,pass
  | if(pass="PASS",0,1) as pass_dummy
  // This line gives us the builds, each condensed into one record with a 1 for fails and 0 for passes
  | max(pass_dummy) by build
  | count as total_builds, sum(_max) as failed_builds
  | total_builds - failed_builds as passed_builds
  

  2

  Permalink

Please sign in to leave a comment.