How to split count of events based on conditions?

[Yaichael]

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!

[somesoni2 · Accepted solution]

Give this a try

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

[DalJeanis]

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]

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]

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

[Yaichael]

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.

[rjthibod]

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?

[rjthibod]

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