How to combine multiple searches to get result

[celerickalyan11]

Ex:

index=newIndex   host="1.12.123.4*" "Field"="abcd"| stats count as totalcount | where totalcount >= 10        

and

index=newIndex   host="1.12.123.4*" "Field"="qwer"| stats count as totalcount | where totalcount >= 20  

[woodcock]

Do not use append. Do not use join. Here are a few good ways:

index=newIndex host="1.12.123.4*" AND (Field="abcd" OR Field="qwer")
| stats count as totalcount  BY Field
| where (Field="abcd" AND totalcount >= 10) OR (Field="qwer" AND totalcount >=20)

OR

index=newIndex host="1.12.123.4*" AND (Field="abcd" OR Field="qwer")
| stats count(eval(Field="abcd")) AS abcd count(eval(Field="qwer")) AS qwer
| where (abcd >= 10) OR (qwer >=20)

[mayurr98]

You may try this as well:

index=newIndex host="1.12.123.4*" "Field"="abcd" 
| stats count by totalcount 
| where count>=10 
| append 
    [ search index=newIndex host="1.12.123.4*" "Field"="qwer" 
    | stats count as totalcount 
    | where totalcount>=20 ]

OR

index=newIndex host="1.12.123.4*" (Field="abcd" OR Field="qwer") 
| stats count as totalcount by Field 
| where (Field="abcd" AND totalcount>=10) AND (Field="qwer" AND totalcount>=20)

[richgalloway · Accepted solution]

index=newIndex host="1.12.123.4*" (Field="abcd" OR Field="qwer") | stats count as totalcount by Field | where ((Field="abcd" AND totalcount >= 10) OR (Field="qwer" and totalcount >= 20))

[rafaelsalazar]

This is the right way.
Both append and join solutions not only are bad practices, but also if dataset is big enough, they will probably hit a conf limit and return less events.

[celerickalyan11]

Thank you rafael, I think we are missing something in Where clause. Without Where clause I get the result but once I add where clause it throws no results though my count number is quite small.

[rafaelsalazar]

Try instead of 10 or 20 a small number like 0 or 1

[celerickalyan11]

Without Where clause I get 6000+ count , with where clause I get no result. I tried to lower the count like 0, 1. No luck

[celerickalyan11]

Never mind, I got it. Renamed the field name as my field name has . in it

Thank you

[michael_schmidt]

Try this on for size:

index=newIndex host="1.12.123.4*" "Field"="abcd"| stats count as totalcount | where totalcount >= 10 | join type=outer host [search index=newIndex host="1.12.123.4*" "Field"="qwer"| stats count as totalcount | where totalcount >= 20 ]