topic Re: Join query with condition and comparison in Splunk Search

How to join query with condition and comparison?

Dharani — Fri, 20 Jan 2023 15:47:28 GMT

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.

Re: Join query with condition and comparison

maciep — Fri, 20 Jan 2023 14:43:13 GMT

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") | ...

Re: How to join query with condition and comparison?

MYilmaz — Fri, 20 Jan 2023 15:48:22 GMT

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