Best way to do subqueries?Answered
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?
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
Please sign in to leave a comment.