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:

alt text

Thanks!

0 Karma
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

View solution in original post

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

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                   

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               

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.

0 Karma

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

Champion

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!