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

[avaishsplunk]

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

[martin_mueller · Accepted solution]

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.

[martin_mueller]

If your question has been answered then do mark the answer as accepted.

12-2016 is the way Splunk formats a timestamp if you're looking at months, it leaves off the date. You can reformat the timestamp to any format you need if you don't like the default.

[avaishsplunk]

Thanks a lot Martin for all your help and patience with me on working upon this issue.

Regards

[avaishsplunk]

Hello Martin,

I am facing an issue with my query again, in below scenarios:

Date --- Requests
1/2/2017 --> 0
12/30/2017 --> 100

The single line value is showing as "N/A" in trend line, how to handle such scenario.

Regards,

[martin_mueller]

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

[avaishsplunk]

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

[avaishsplunk]

Just to explain more, I am trying to create a single value field which will show me the sum of requests for the day and also will show the comparison in three scenarios.

1. Today with yesterday
2. Today with last week same day time
3. Today with last month same time

[avaishsplunk]

Hello Martin,

I am now successfully able to get details for 2 Scenarios:

1. Compare Today with last week

2. Compare today with yesterday

Now when i do compare today with last month

I am getting output as

_Time   ---> Requests

12-2016   -->0
01-2017   --> 0

Although I should get as 

_Time         --> Requests
12-01-2016 -->0
01-01-2017-->0

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

[martin_mueller]

You forgot the two evals.

[avaishsplunk]

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

[avaishsplunk]

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

[martin_mueller]

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

[avaishsplunk]

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

[avaishsplunk]

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

[avaishsplunk]

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

[martin_mueller]

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

[avaishsplunk]

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

[avaishsplunk]

This what i exactly did

|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 |eval _time = starttime | eval Requests = 0]

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

[avaishsplunk]

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

[avaishsplunk]

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,