Not seeing anything returned when using Sum with an eval IF statement

[tdavison76]

Hello,

Thanks in advance for any help and Karma will be on the way :).

So I'm trying to create a Table that uses a "Sum" field that would show how many "Create" events exist that doesn't have a "Close" event.  

I'm doing this by using an eval IF statement

The issue I am having is when using "Sum", I get no results for Sum when there are not any events.  But, if I use "Count", I always get "1" returned.

Here's the Search I am using

index="healthcheck" integrationName="Opsgenie Edge Connector - Splunk", "alert.message"="[ThousandEyes] Alert for TMS Core Healthcheck", action IN ("Create","Close")
| eval Create=IF(action=="Create",1,0)
| eval Close=IF(action=="Close",1,0)
| stats count(Create) as isCreate, count(Close) as isClose by alert.id
| eval comparison=IF(isCreate>isClose,"1", "0")
| stats sum("comparison") as Sum count("comparison") as Count
| eval Application =  "TMS_API"
| eval test = Sum
| eval test1 = Count
| eval test2 = Application
| eval "Monitor Details" = "Performs a Health Check "
| table test, test1, test2 , "Monitor Details"

In the returned results, I get an empty "test" field and a "1" in test1 field.

Thanks again for your help, and please let me know if more details are needed, this has been a huge headache for me.

Thanks,

Tom

[livehybrid · Accepted solution]

Hi Tom,

The reason you're having an issue here is you're using "comparison" which is a string value - get rid of the double quotes and it will reference the field.

I've had a play using the below SPL to create some sample data and I think it now gives the result expected?

| makeresults 
|  eval _raw = "[{\"integrationName\":\"Opsgenie Edge Connector - Splunk\",\"alert\":{\"message\":\"[ThousandEyes] Alert for TMS Core Healthcheck\",\"id\":\"abc123xyz\"},\"action\":\"Create\"},{\"integrationName\":\"Opsgenie Edge Connector - Splunk\",\"alert\":{\"message\":\"[ThousandEyes] Alert for TMS Core Healthcheck\",\"id\":\"abc123xyz\"},\"action\":\"Close\"},{\"integrationName\":\"Opsgenie Edge Connector - Splunk\",\"alert\":{\"message\":\"[ThousandEyes] Alert for TMS Core Healthcheck\",\"id\":\"def456uvw\"},\"action\":\"Create\"},{\"integrationName\":\"Opsgenie Edge Connector - Splunk\",\"alert\":{\"message\":\"[ThousandEyes] Alert for TMS Core Healthcheck\",\"id\":\"def456uvw\"},\"action\":\"Close\"},{\"integrationName\":\"Opsgenie Edge Connector - Splunk\",\"alert\":{\"message\":\"[ThousandEyes] Alert for TMS Core Healthcheck\",\"id\":\"ghi789rst\"},\"action\":\"Create\"}]"
| eval events=json_array_to_mv(_raw)
| mvexpand events
| eval _raw=events
| fields _raw
| spath
``` Prep work complete ```
| eval Create=IF(action=="Create",1,0)
| eval Close=IF(action=="Close",1,0)
| stats sum(Create) as isCreate, sum(Close) as isClose by alert.id
| eval comparison=IF(isCreate>isClose,"1", "0")
| stats sum(comparison) as Sum count(comparison) as Count
| eval Application =  "TMS_API"
| eval test = Sum
| eval test1 = Count
| eval test2 = Application
| eval "Monitor Details" = "Performs a Health Check "
| table test, test1, test2 , "Monitor Details"

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will

[tdavison76]

So sorry, I tested in a time frame with a Create event.  I thought it was working.  If I chose a time frame with no events, I still get an empty "Sum" field.  

I've removed the Double Qoutes: (| eval comparison=IF(isCreate>isClose,1, 0))

Also, changed the Stats count(Create) and count(Close) back to "sum".  I did this during testing, thanks for catching.

Thanks again for the help.

Tom

[livehybrid]

I think this is because when no data is returned, you're trying to take a sum of no fields which is Null, however the count value is 0, if that makes sense?

You could probably fix this with:
| eval test = COALESCE(Sum,0)
instead of
| eval test = Sum

Which will mean that if Sum is Null it will use the value 0. 
Would this work for you?

Will

[tdavison76]

The COALESCE did the trick.  You are awesome.  Thanks for all of the help.  I can finally get a good nights rest. 🙂

Thanks,

Tom

[tdavison76]

You came thru again,  twice in one day!!.   Simply awesome, thank you for your help on all of this.  It's working like a charm now.

Have a good week.

Thanks,

Tom

[livehybrid]

I also noticed another issue, you're using "count" instead of "sum" for:

| stats count(Create) as isCreate, count(Close) as isClose by alert.id

Should be

| stats sum(Create) as isCreate, sum(Close) as isClose by alert.id

(I might have given you this SPL previously?? If so apologies!

Good luck!

Will