topic Re: aggregate count based on multiple conditions in Splunk Enterprise

aggregate count based on multiple conditions

rizwan0683 — Thu, 12 Nov 2020 16:25:23 GMT

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

Re: aggregate count based on multiple conditions

rnowitzki — Thu, 12 Nov 2020 16:35:40 GMT

Hi @rizwan0683 ,

This should do it:

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

Hope it helps.

BR
Ralph

Re: aggregate count based on multiple conditions

rizwan0683 — Thu, 12 Nov 2020 17:31:08 GMT

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

Re: aggregate count based on multiple conditions

rnowitzki — Thu, 12 Nov 2020 17:34:05 GMT

Hi @rizwan0683 ,

Glad I could help.

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

=> 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

Re: aggregate count based on multiple conditions

rizwan0683 — Thu, 12 Nov 2020 17:56:13 GMT

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

Re: aggregate count based on multiple conditions

rnowitzki — Thu, 12 Nov 2020 20:11:59 GMT

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