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!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...