Splunk Enterprise Security

## 2 Stats Queries Within A Single Search?

Explorer

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?

Tags (1)
1 Solution
Builder

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!

Builder

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!

Explorer

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?

Get Updates on the Splunk Community!

#### Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

#### Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

#### Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...