topic Re: Not seeing anything returned when using Sum with an eval IF statement in Splunk Search

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

tdavison76 — Mon, 17 Feb 2025 21:24:57 GMT

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

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

livehybrid — Mon, 17 Feb 2025 21:38:21 GMT

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

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

livehybrid — Mon, 17 Feb 2025 21:39:35 GMT

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

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

tdavison76 — Mon, 17 Feb 2025 21:54:39 GMT

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

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

tdavison76 — Mon, 17 Feb 2025 22:10:23 GMT

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

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

livehybrid — Mon, 17 Feb 2025 22:14:01 GMT

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

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

tdavison76 — Tue, 18 Feb 2025 13:37:06 GMT

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

Thanks,

Tom