2 Stats Queries Within A Single Search?

[giventofly08]

Currently, my stats command is done by both the Computer Field and the Group field. This allows me to create an eval statement to obtain a percentage of events on each specific machine and the group they're in. It looks as follows:

Computer | Group | Percent | Failures
Name1 | A | 95 | 0
Name 2 | B | 80 | 0
Name 3 | A | 75 | 2
Name 4 | A | 90 | 0
Name 5 | C | 75 | 1
Name 6 | B | 95 | 0
Name 7 | B | 90 | 0
Name 8 | C | 60 | 0
Name 9 | A | 100 | 0
Name 10 | C | 95 | 1

Now, I need to go one step further, and make a percentage of each group using the results from the above table with the condition "if (Percent>"90" AND Failures="0")". Basically, so that it creates the average for computers that are 90% or higher, and have 0 failures so that it would look like this: (As you can see there are 3 times where the criteria is met and 1 failure for Group A above, ergo the result is Group A = 75)

Group | Percent
A | 75
B | 67
C | 0

I've built out all the fields necessary to do it, but because I have used my stats command earlier in the query by both Computer and Group, I'm unable to use the stats command again to simply count by Group. How would I go about accomplishing this?

[aberkow · Accepted solution]

This took me a long time to figure out what you were trying to get haha.

This should work:

| eval goodMachines= if(Percent>90 AND Failures=0, 1, 0)
| stats sum(goodMachines) as sumOfGoodMachines, count(goodMachines) as countOfMachines by Group
| eval percentGoodMachines = sumOfGoodMachines / countOfMachines # optional wrap it in parenthesis and add ."%" for formatting 

You essentially want to "pass through" the count of what machines are good, but also keep track of how many machines there were total. This can be achieved by using different functions in the stats command (distinct_count vs count vs sum, etc). In this case I'm passing through the sum (since it's booleanized 1's/0's) as the numerator for my percentage and the count (how many times does group appear at all) as the denominator. Does this make sense?

Hope this helps!

[giventofly08]

This works great, very interesting to know how the sum works (I had actually gotten as far as the Boolean values when declaring all my fields), but didn't know that was how the sum feature worked.

Now, is there a way to show this final value as well as the (for instance) the percentage value of the first example I provided so it would look like:

Group | Percent | Percent w/ No Failures?