Splunk Enterprise

aggregate count based on multiple conditions

rizwan0683
Path Finder

I have an index with events containing a field foo that can carry multiple numeric values 1,2,3

Looking to count all events where foo is either 1 or 2.

have tried couple of options with eval and stats count but not getting there

|stats count (eval(foo=1 OR foo=2)) as Foo_combined

Labels (1)
Tags (1)
0 Karma
1 Solution

rnowitzki
Builder

Hi @rizwan0683 ,

This should do it:

 

|  makeresults 
|  eval foo="1 2 3 2 5 2 1 5"
|  makemv foo
|  mvexpand foo
|  eval foo_counter=if(foo=1 OR foo=2, 1, 0)
|  stats sum(foo_counter) as foo_counter


You'll just nned the last 2 lines. The others are just to make up some sample data.

Hope it helps.

BR
Ralph

--
Karma and/or Solution tagging appreciated.

View solution in original post

rizwan0683
Path Finder

thanks that seems to work. can you break down the eval cmd please, what does the 1,0 following foo=2 perform?

 

IF foo=1 or foo=2 then add 1 to the value of foo_counter, else don't add anything (0) ?

0 Karma

rnowitzki
Builder

Hi @rizwan0683 ,

Glad I could help.

To see what it does, just run the query without the last line, like this:

|  makeresults 
|  eval foo="1 2 3 2 5 2 1 5"
|  makemv foo
|  mvexpand foo
|  eval foo_counter=if(foo=1 OR foo=2, 1, 0)

=> As you can see, it adds an field called "foo_counter" to all the rows and evals it to "1" if foo is "1" or  "2". The "0" is basically the "else" value, so everything but 1 and 2 will be evaled to 0. 

When you sum foo_counter up (which the stats command does),  you get the count of 1s and 2s.

Ralph

--
Karma and/or Solution tagging appreciated.
0 Karma

rizwan0683
Path Finder

thanks I see the new fields now.

What I need to do next is to chart this along another series (bar), where bar is another field found in the event. I have the following

| eval foo_counter=if(foo=1 OR foo=2, 1, 0)
| eval bar_counter=if(bar="x",1,0)
|bin span=1d _time
|stats count(foo_counter) as Total_Foo count(bar_counter) as Total_Bar by _time

Looking to see the number of foo and bar per day for a period of time. Ideally this would be plotted as overlay chart so Total_Foo as bar chart and Total_Bar as a line chart with its separate y-axis 

0 Karma

rnowitzki
Builder

Hi @rizwan0683,

I prefer timechart over bin ... _time

 

|  timechart sum(foo_counter) as foos, sum(bar_counter) as bars

 

Then you select "column bar" on the visualization tab -> click on "format" -> "chart overlay" and in the text field at the top you type in "bars".

Now you have foos as columns, overlayed by bars as line chart.

Cheers
Ralph

--
Karma and/or Solution tagging appreciated.
0 Karma

rnowitzki
Builder

Hi @rizwan0683 ,

This should do it:

 

|  makeresults 
|  eval foo="1 2 3 2 5 2 1 5"
|  makemv foo
|  mvexpand foo
|  eval foo_counter=if(foo=1 OR foo=2, 1, 0)
|  stats sum(foo_counter) as foo_counter


You'll just nned the last 2 lines. The others are just to make up some sample data.

Hope it helps.

BR
Ralph

--
Karma and/or Solution tagging appreciated.
Get Updates on the Splunk Community!

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...

Enterprise Security Content Update (ESCU) | New Releases

In October, the Splunk Threat Research Team had one release of new security content via the Enterprise ...