How to join query with condition and comparison?

[Dharani]

Hi,

I need to show error messages for one particular service. But the challenge here is that for example ,

I need to show error messages including "withdrawal failed" error. But if this error happened because of "insufficient  balance" then this withdrawal failed should not be listed. Other error messages should be listed anyway.

e.g query:

index=abc sourcetype=payment log_level=Error |table  message.

Result would be like:

withdrawal failed
deposit failed
Error 404
customer not found

But i want something like,

index=abc sourcetype=payment log_level=Error
and
| search message="*Withdrawal failed*"
| join type=inner requestId [search index=abc
| search message ="*insufficient balance*"]  --> if this part is true then it should not list "Withdrawal failed error"

Results should be like,

deposit failed
Error 404
customer not found

Please help me with the search.

Thanks In advance.

[MYilmaz]

Hi @Dharani 

If I understood correctly, if you don't want to see "withdrawal failed" error coming with "insufficient balance", you can try with the search I mentioned. I hope it works. 

index=yourindex sourcetype=yorsourcetype ...
| stats count values(message) as message by requestId 
| where count < 2

[maciep]

Not sure if it would be more efficient, but maybe eventstats could be helpful here?

index=abc
| eventstats values(message) as all_messages by requestId
| where sourcetype="payment" AND log_level="Error" AND NOT match(all_messages,"insufficient balance")
| ...