Splunk Search

doing sum with if condition in search query

man03359
Communicator

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

Labels (2)
Tags (3)
0 Karma
1 Solution

man03359
Communicator

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

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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

 

0 Karma

man03359
Communicator

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

0 Karma

man03359
Communicator

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")
0 Karma

PickleRick
SplunkTrust
SplunkTrust
| 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.

0 Karma

man03359
Communicator

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
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

Like

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

man03359
Communicator

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

PickleRick
SplunkTrust
SplunkTrust

That's one way of doing that. 🙂

Well done.

0 Karma

man03359
Communicator

Thanks :0

 

Happy Splunking 🙂

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

man03359
Communicator

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

 

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...