topic Re: Unable to set stats count value as 0 in case of 0 events for a particular day in Splunk Search

Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Tue, 29 Sep 2020 12:17:01 GMT

Hello,
a
In my search query below, I am unable to set the value of stats count as 0 in case there are no events for today or on previous day, i am getting NA as output in
splunk, I am trying to plot a single unit output which will show a trend between today and yesterday with % trend.

Can you guys pls help

[| gentimes start=-1
| eval earliest=if(lower(strftime(now(),"%A"))="monday", "-3d@d", "-1d@d")
| eval latest=if(lower(strftime(now(),"%A"))="monday","-3d@s", "-1d@s")
| table earliest, latest | format "" "" "" "" "" ""] index=yy sourcetype=zz
| search "xx"
| spath output=OpName path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.operationName
| spath output=EvType path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.eventTypeCode
| spath output=state path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.state
| spath output=Line_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.recordCount
| spath output=Org_Code path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
| spath output=TimeZone path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
| spath output=ccpath=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute3
| eval combined=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(OpName,EvType),state),Line_Count),Org_Code),TimeZone),cc)
| mvexpand combined|eval combined=split(combined,",")
| eval OpName=mvindex(combined,0)
| eval EvType=mvindex(combined,1)
| eval state=mvindex(combined,2)
| eval Line_Count=mvindex(combined,3)
| eval Org_Code =mvindex(combined,4)
| eval TimeZone =mvindex(combined,5)
| eval cc=mvindex(combined,6)
| where OpName="abc"|append
[search earliest=@d index=yy sourcetype=zz
| search "xx"
| spath output=OpName path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.operationName
| spath output=EvType path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.eventTypeCode
| spath output=state path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.state
| spath output=Line_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.recordCount
| spath output=Org_Code path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
| spath output=TimeZone path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
| spath output=CDC_RDC path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute3
| eval combined=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(OpName,EvType),state),Line_Count),Org_Code),TimeZone),CDC_RDC)
| mvexpand combined
| eval combined=split(combined,",")
| eval OpName=mvindex(combined,0)
| eval EvType=mvindex(combined,1)
| eval state=mvindex(combined,2)
| eval Line_Count=mvindex(combined,3)
| eval Org_Code =mvindex(combined,4)
| eval TimeZone =mvindex(combined,5)
| eval cc=mvindex(combined,6)
| where OpName="abc"]
| bucket _time span=1d
| stats sum(Line_Count) AS Requests by _time

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

martin_mueller — Sat, 31 Dec 2016 02:49:55 GMT

Append this to the end of your search:

... | append [gentimes start=-8 end=1 | eval _time = starttime | eval Requests = 0 | where _time >= relative_time(now(), if(lower(strftime(now(),"%A"))="monday", "-3d@d", "-1d@d"))]
| stats max(Requests) as Requests by _time

This will generate a week's worth of "today, requests are zero" events, filter down to the days you need, and the max() will display the real request counts if there are any.

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sat, 31 Dec 2016 03:16:50 GMT

Hello Martin,

Thanks for your reply, sorry i am still not able to figure out the correct SPL for my need, i apologise for the same, but my need is that if i compare today and yesterdays events and if both the days have 0 events then i should get output like

date requests
29-12-2016 0
30-12-2016 0

Can you pls help me with this

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

martin_mueller — Sat, 31 Dec 2016 12:09:49 GMT

To see if we're on the right track, leave off the where and see if you get the empty days added correctly.

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sat, 31 Dec 2016 16:22:56 GMT

Hello Martin,

The days are getting added but I am seeing an issue with that also, there are 2 parts in my query, first one gives me event details for yesterday and second one gives me details for today both.

When the days are added since we are doing gentimes -1, i only get days added till yesterday like when i tried i was getting from 12-22-2016 to 12-29-2016, i was not getting the day from 12-23-2016 to 12-30-2016 which i don't think is going to work

Regards

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

martin_mueller — Sat, 31 Dec 2016 16:25:40 GMT

That's why my answer doesn't use start=-1, it generates more days.

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sat, 31 Dec 2016 16:39:41 GMT

Hello Martin,

Yeah even if I do gentimes start = -8 its the same it does not add events for today it always goes a day back.

Regards,

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

martin_mueller — Sat, 31 Dec 2016 17:30:08 GMT

Ah, I see. Just add end=1 to end a day later than the default zero.

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sat, 31 Dec 2016 18:01:01 GMT

Thanks Martin for helping me with this, I am getting close it seems, now i am able to get all the days from gentimes=-8 and end = 1, but how should i handle the comparison now, the moment i add earliest=-1d@d and latest=@d for yesterday in first query the search gives me no results.

Regards

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Tue, 29 Sep 2020 12:14:25 GMT

This what i exactly did

Now how should i pass my earliest and latest values here, the way i need to do is if you see my original query I need this appended data for yesterday and today so for that I beleive i need to pass the earliest and latest but the moment i pass them it does not give any output

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sat, 31 Dec 2016 21:29:04 GMT

Guys can someone pls help on this issue, I am kind of stuck and all my searches need this kind of feature

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

martin_mueller — Sun, 01 Jan 2017 01:59:27 GMT

Using your calculation for earliest, try this:

... | append [gentimes ... | where _time >= relative_time(now(), if(lower(strftime(now(),"%A"))="monday", "-3d@d", "-1d@d"))]
| stats max(Requests) as Requests by _time

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sun, 01 Jan 2017 02:14:29 GMT

Hello Martin,

Tried below:

|where status= "hvop_error" OR status="validation_error"]
| bucket _time span=1d  |stats sum(Line_Count) as Requests by _time|
append [gentimes start=-8 end=1 | where _time >= relative_time(now(), if(lower(strftime(now(),"%A"))="monday", "-3d@d", "-1d@d"))]
 | stats max(Requests) as Requests by _time

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sun, 01 Jan 2017 02:14:56 GMT

it did not returned any rows me , output "No results found"

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sun, 01 Jan 2017 02:40:47 GMT

It seems things are getting confused:

In my search query, I have 2 searches

1. This gives stats for today

2. This gives stats for the period entered as parameters to the panel (which is a) Last week same day/time b) Last month same day time c) Yesterday same day/time

The issue i am facing is if we have no events for 1 and 2, I need to show 0 as stats for both the mentioned criteria's along with dates

e.g.

Date Requests
12/31/2016 0
12/30/2016 0

same as

12/31/2016 --> 0
12/24/2016 ---> 0

I need the above so that i can put a single value field with trend charts

[| gentimes start=-1 
| eval earliest=if(lower(strftime(now(),"%A"))="monday", "-3d@d", "-1d@d") 
| eval latest=if(lower(strftime(now(),"%A"))="monday","-3d@s", "-1d@s")
| table earliest, latest | format "" "" "" "" "" ""] index=yy sourcetype=zz
| search "xx"
| spath output=OpName path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.operationName
| spath output=EvType path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.eventTypeCode
| spath output=state path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.state
| spath output=Line_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.recordCount
| spath output=Org_Code path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
| spath output=TimeZone path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
| spath output=ccpath=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute3
| eval combined=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(OpName,EvType),state),Line_Count),Org_Code),TimeZone),cc)
| mvexpand combined|eval combined=split(combined,",")
| eval OpName=mvindex(combined,0)
| eval EvType=mvindex(combined,1)
| eval state=mvindex(combined,2)
| eval Line_Count=mvindex(combined,3)
| eval Org_Code =mvindex(combined,4)
| eval TimeZone =mvindex(combined,5)
| eval cc=mvindex(combined,6)
| where OpName="abc"|append
[search earliest=@d index=yy sourcetype=zz
| search "xx"
| spath output=OpName path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.operationName
| spath output=EvType path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.eventTypeCode
| spath output=state path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.state
| spath output=Line_Count path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.recordCount
| spath output=Org_Code path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
| spath output=TimeZone path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
| spath output=CDC_RDC path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute3
| eval combined=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(OpName,EvType),state),Line_Count),Org_Code),TimeZone),CDC_RDC)
| mvexpand combined
| eval combined=split(combined,",")
| eval OpName=mvindex(combined,0)
| eval EvType=mvindex(combined,1)
| eval state=mvindex(combined,2)
| eval Line_Count=mvindex(combined,3)
| eval Org_Code =mvindex(combined,4)
| eval TimeZone =mvindex(combined,5)
| eval cc=mvindex(combined,6)
| where OpName="abc"]
| bucket _time span=1d
| stats sum(Line_Count) AS Requests by _time

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

martin_mueller — Sun, 01 Jan 2017 03:01:20 GMT

You forgot the two evals.

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sun, 01 Jan 2017 03:12:11 GMT

Hello Martin,

Sorry for the trouble, can you pls explain me little bit more details, I reposted my main query and how i need to show the data.

Can you pls help with this.

Regards

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sun, 01 Jan 2017 03:26:48 GMT

I hardcoded the values for earliest and latest , still no results

|where status= "hvop_error" OR status="validation_error"]
| bucket _time span=1d  |stats sum(Line_Count) as Requests by _time|
append [gentimes start=-8 end=1 | where _time >= relative_time(now(), if(lower(strftime(now(),"%A"))="monday", "-3d@d", "-1d@d")) |search earliest=-1d@d latest=-1d@s]| stats max(Requests) as Requests by _time

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

martin_mueller — Sun, 01 Jan 2017 15:10:46 GMT

I've edited the original answer to include the full SPL you need to append to the end of your search:

... | append [gentimes start=-8 end=1 | eval _time = starttime | eval Requests = 0 | where _time >= relative_time(now(), if(lower(strftime(now(),"%A"))="monday", "-3d@d", "-1d@d"))]
| stats max(Requests) as Requests by _time

Re: Unable to set stats count value as 0 in case of 0 events for a particular day

avaishsplunk — Sun, 01 Jan 2017 15:24:26 GMT

Thanks a lot Martin, this is working for comparison for today and yesterday scenario, how shall i do it for comparing

1. Todays with last week 

2, Todays with last month 

Can you pls guide on this, and wishing you and all family members a very Happy new Year :)

Regards