topic doing sum with if condition in search query in Splunk Search

doing sum with if condition in search query

man03359 — Fri, 14 Jul 2023 11:16:42 GMT

Hi All!

I want to calculate the sum of failed and declined

| eval Msg=if((Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured "),"Failed","Declined")

Now I want to calculate the sum of failed and declined in the next line. I am already doing stats count of other fields, so need to add this one with them. Here is the one I am working on, but the problem is its not giving the output for Failed and Declined -

Here is my query-

index=idx-stores-pos sourcetype=GSTR:Adyen:log
| transaction host startswith="Transaction started" maxpause=90s
| search Failure OR Success
| eval Store= substr(host,1,7)
| eval Register= substr(host,8,2)
| rex field=_raw "AdyenPaymentResponse:.+\sResult\s:\s(?<Status>.+)"
| rex field=_raw "AdyenPaymentResponse:.+\sReason\s:\s(?<Failure_Message>.+)"
| rex field=_raw "AdyenPaymentResponse:.+\sMessage\s:\s(?<Error_Message>.+)\;"
| replace "* " with * in Error_Message Failure_Message
| eval Msg=if((Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured "),"Failed","Declined")
| stats count(eval(Status="Success")) AS Success_Count count(eval(Status="Failure")) AS Failure_Count sum(eval(Msg="Failed")) AS Failed sum(eval(Msg="Declined")) AS Declined By Store Register
| eval Total_Payment= Success_Count + Failure_Count
| table Store Register Success_Count Failure_Count Total_Payment Failed Declined

Re: doing sum with if condition in search query

ITWhisperer — Fri, 14 Jul 2023 11:43:21 GMT

When posting code, it is usually best to paste it into a code block </> that way formatting, such as spaces, is preserved.

Having said that, assuming what you have posted is accurate, you appear to be removing spaces from Failure_Message (and Error_Message) but your if function appears to be assuming they are still there.

Could this be the source of the issue?

Re: doing sum with if condition in search query

man03359 — Fri, 14 Jul 2023 12:08:11 GMT

Yes, because I observed when filtering out that the regex did not extract till the end of the string, since there were only 2 errors I wanted to monitor I manually gave a space there. I want to do the sum of Failed and Declined from this query.

| eval Msg=if((Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured "),"Failed","Declined")

Here's my lame attempt to get it working..

| stats count(eval(Status="Success")) AS Success_Count count(eval(Status="Failure")) AS Failure_Count sum(eval(match(Msg="Failed"))) AS Failed sum(eval(match(Msg="Declined"))) AS Declined By Store Register

Re: doing sum with if condition in search query

PickleRick — Fri, 14 Jul 2023 12:13:47 GMT

I suppose you have a problem with

| stats sum(eval(condition))

Not giving expected results. That's because your expectations are wrong (and the docs, to which I already posted feedback long time ago, are misleading).

People often think about the stats with eval as a form of filtering data for the stats command. And yes, it does effectively work this way for the count aggregation. But it's a neat side effect.

In fact

| stats aggregation(eval(expression_A))

is equivalent to

| eval temporary_field=expression_A | stats aggregation(temporary_field)

With the one small quirk that if your expression is a boolean operation it's getting silently cast from true/false to 1/0 so that count with evaled condition does work "as expected".

So if you want to, for example, sum all values higher than a given threshold, you'd want somehting like this:

| eval temporary_field=if(myfield>threshold,myfield,0) | stats sum(temporary_field)

Which can in turn be a bit shortened to

| stats sum(eval(if(myfield>threshold,myfield,0)))

Re: doing sum with if condition in search query

man03359 — Mon, 17 Jul 2023 09:43:24 GMT

Hi,

I tried it like this, as you suggested,

| stats sum(eval(if(Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured ","Failed","Declined")))

But the error I am getting is -

Error in 'stats' command: You must specify a rename for the aggregation specifier on the dynamically evaluated field 'sum(eval(if(Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured ",Failed,Declined)))'.

Re: doing sum with if condition in search query

PickleRick — Mon, 17 Jul 2023 09:53:05 GMT

Ahhh, sure. You have to add "AS some_destination_column_name" so that it gets a reasonable name.

Re: doing sum with if condition in search query

man03359 — Mon, 17 Jul 2023 10:02:41 GMT

I want to sum both the values, Failed as well as Declined.

Earlier I was using

| eval Failed=if((Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured "),1,0)
| eval Declined=if((Failure_Message!="200 Emv error " OR Failure_Message!="NoAcquirerFoundConfigured "),1,0)

But when I filter for search Declined=1, if shows 200 Emv error as well. So I modified the query to - All I need is the no of times I got Failed and Declined both

| eval Msg=if((Failure_Message="200 Emv error" OR Failure_Message="NoAcquirerFoundConfigured"),"Failed","Declined")

Re: doing sum with if condition in search query

PickleRick — Mon, 17 Jul 2023 10:43:15 GMT

| eval Failed=if((Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured "),1,0)
| eval Declined=if((Failure_Message!="200 Emv error " OR Failure_Message!="NoAcquirerFoundConfigured "),1,0)

I don't get this. You will _always_ get your Declined field set to 1 unless there is no Failure_Message set at all. Basics of boolean logic.

Re: doing sum with if condition in search query

man03359 — Mon, 17 Jul 2023 12:23:48 GMT

How do I proceed. Even this is not giving any output

| stats sum(eval(if(Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured ","Failed","Declined"))) AS Failed_Count

Re: doing sum with if condition in search query

PickleRick — Mon, 17 Jul 2023 12:32:27 GMT

OK. What are you trying to do? Look into this command.

You're dynamicaly evaluating a condition and based on that setting the aggregated field to one of two strings. And then you're trying to do a sum, which is a numerical operation, on strings. It doesn't work like that.

If you just want a count (not sum) of values either fulfilling one condition or the other, you have to simply do two separate aggregations.

| stats count(eval(my_first_condition)) as first_aggregation count(eval(my_second_condition)) as second_aggregation

Re: doing sum with if condition in search query

man03359 — Tue, 18 Jul 2023 10:06:43 GMT

Hi,

Thanks for the help. I modified my condition part from -

| eval Msg=if((Failure_Message="200 Emv error" OR Failure_Message="NoAcquirerFoundConfigured"),"Failed","Declined")

to this -

| eval Failed=if((Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured "),1,0)
| eval Declined=if((Failure_Message=="200 Emv error " OR Failure_Message=="NoAcquirerFoundConfigured " OR isnull(Failure_Message)),0,1)

and then doing -

| stats sum(Failed) AS Failed sum(Declined) AS Declined By Store Register

and this worked :0

Re: doing sum with if condition in search query

PickleRick — Tue, 18 Jul 2023 10:36:04 GMT

That's one way of doing that. 🙂

Well done.

Re: doing sum with if condition in search query

man03359 — Tue, 18 Jul 2023 11:56:24 GMT

Thanks :0

Happy Splunking 🙂