Splunk Search

## How to split count of events based on conditions?

Communicator

Let's say that I have the following query:

``````(...) | stats count AS Foo by X
``````

I would like to split Foo based on conditions like Bar > 5 and Bar < 5, and display it as the following:

Thanks!

Tags (5)
1 Solution
Revered Legend

Give this a try

``````your base search | eval type=if(Bar>5,"Bar>5","Bar=<5")
| chart count over Foo by type
``````
Revered Legend

Give this a try

``````your base search | eval type=if(Bar>5,"Bar>5","Bar=<5")
| chart count over Foo by type
``````
SplunkTrust

oh, you had to go and do it in two lines!

``````| makeresults | eval Foo="x1 x2 x3 x1 x4 x2 x1 x3 x5 x2"| makemv Foo | mvexpand Foo
| eval Bar="0 0 0 1 2 3" | makemv Bar | mvexpand Bar
| eval rand=random() | eval Bar = Bar + tonumber(substr(rand,1,1))

| eval type=if(Bar>5,"Bar>5","Bar=<5")
| chart count over Foo by type
``````

results in

``````Foo       Bar=<5    Bar>5
x1        14        4
x2        12        6
x3        8         4
x4        3         3
x5        2         4
``````
SplunkTrust

See somesoni2's answer as more elegant.

Try this -

``````| makeresults | eval Bar="1 3 5 7 9 2 1 5 7 8 24 5 1 3 4" | makemv Bar | mvexpand bar

| stats count AS Foo by Bar
| eval Bar4=if(Bar<5,Foo,0)
| eval Bar5=if(Bar<5,0,Foo)
| stats sum(Foo) as Foo, sum(Bar5) as "Bar>=5",sum(Bar4) as "Bar<5"
``````

...producing output that looks like this -

``````Foo       Bar>=5    Bar<5
15        8         7
``````

There is never going to be more than one line of data, though, unless you have more than one dimension.

``````| makeresults | eval x="x1 x2 x3 x1 x4 x2 x1 x3 x5 x2"| makemv x | mvexpand x
| eval Bar="0 0 0 1 2 3" | makemv Bar | mvexpand Bar
| eval rand=random() | eval Bar = Bar + tonumber(substr(rand,1,1))

| stats count AS Foo by x Bar
| eval Bar4=if(Bar<5,Foo,0)
| eval Bar5=if(Bar<5,0,Foo)
| stats sum(Foo) as Foo, sum(Bar5) as "Bar>=5",sum(Bar4) as "Bar<5" by x
``````

...producing (random) output that looks like this -

``````x         Foo       Bar>=5    Bar<5
x1        18        10        8
x2        18        2         16
x3        12        2         10
x4        6         3         3
x5        6         5         1
``````

Come to think of it, you may have meant this -

``````| makeresults | eval Foo="x1 x2 x3 x1 x4 x2 x1 x3 x5 x2"| makemv Foo | mvexpand Foo
| eval Bar="0 0 0 1 2 3" | makemv Bar | mvexpand Bar
| eval rand=random() | eval Bar = Bar + tonumber(substr(rand,1,1))

| stats count as foocount by Foo Bar
| eval Bar4=if(Bar<5,foocount,0)
| eval Bar5=if(Bar<5,0,foocount)
| stats sum(Bar5) as "Bar>=5",sum(Bar4) as "Bar<5" by Foo
``````

...producing this

``````Foo       Bar>=5    Bar<5
x1        7         11
x2        6         12
x3        2         10
x4        2         4
x5        2         4
``````
Champion

Your table doesn't seem to match your description of the query. is "Bar" really count? What does "Event" in your table represent?

Communicator

Hi rjthibod,

Thanks for the reply.

The table is for showing how would I like output of the resulting query.

No. Bar, in this case, is another numeric field that I would like to use for displaying the events.

"Event" was changed to num, meaning the count of Foo itself, Foo under Bar > 5, and Bar < 5 conditions subsequently.

Champion

Something still isn't clear. What is Foo in your new description? Is it just the sum of the columns for Bar <=5 and Bar > 5? Is there some other field not shown?

Champion

OK will post an answer below. Note, what about Bar == 5?

Get Updates on the Splunk Community!