Count of events based on two where conditions

[bharathkumarnec]

Hello All,

I have to provide two where conditions in my query and need to count the events by individual counts and sum them up..Below is the example

Where x>y AND y>z -- need to calculate count
Where z>a -- need to calculate count

end i need to do sum of both above counts.

kindly provide some inputs on the same.

Regards,
BK

[cmerriman · Accepted solution]

something like this should work to get the counts of each:

|stats count(eval(x>y AND y>z)) as condition_1 count(eval(z>a)) as condition_2

[gcusello]

Hi bharathkumarnec,
you can run two searches and append the second to the first, something like this>

index=my_index x>y y>z
| stats count AS First
| append [ search
     index=my_index z>a
     | stats count  AS Second
     ]
| stats sum(First) AS First sum(Second) AS Second
| eval Total=First+Second
| table First Second Total

Bye.
Giuseppe

[bharathkumarnec]

Thanks Cusello for the inputs!

I need some thing like this ,count of XlesssthanY and ZlessthanY and a separate count of ZlessthanY

[gcusello]

I send you not an fixed answer but an approach that you can use in your real situation.
Bye.
Giuseppe

[bharathkumarnec]

Got it Cusello...I thought of going with that approach but wanted to check if there is any way that we can use one single query instead of appending by using two queries?

Only using summary indexing or anyother approach which is better?

[gcusello]

summary indexing is a very good approach to accelerate your searches when they are fixed and schedulable
To use a single query you could use (if possible, I don't know your data) multiple evel command,
something like this
Index=my_index
| eval count1=if(X<Y AND Z<Y,"1","0"), count2=if(Z<Y,"1","0")
| stats sum(count1) AS count1 sum(count2) AS count2
Bye.
Giuseppe

[Yunagi]

Is the second where condition (z>a) dependant on the first where condition?

Perhaps try something like this:

basesearch | eval condition1=if(x>y AND y>z,1,0) | eval condition2=if(z>a,1,0) | stats sum(condition1) as sum1 sum(condition2) as sum2 | eval sumtotal=sum1+sum2