Splunk Enterprise

aggregate count based on multiple conditions

rizwan0683
Explorer

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
Explorer

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
Explorer

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.

View solution in original post

.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!