aggregate count based on multiple conditions

[rizwan0683]

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

[rizwan0683]

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) ?

[rnowitzki]

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

[rizwan0683]

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 

[rnowitzki]

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

[rnowitzki · Accepted solution]

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