Splunk Search

How to split count of events based on conditions?

Yaichael
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:

alt text

Thanks!

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

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

View solution in original post

somesoni2
Revered Legend

Give this a try

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

DalJeanis
SplunkTrust
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                   

DalJeanis
SplunkTrust
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               

rjthibod
Champion

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

Yaichael
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.

0 Karma

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

0 Karma

rjthibod
Champion

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

0 Karma