Splunk Search
Highlighted

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

Path Finder

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=LineCount 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),LineCount),OrgCode),TimeZone),cc)
| mvexpand combined|eval combined=split(combined,",")
| eval OpName=mvindex(combined,0)
| eval EvType=mvindex(combined,1)
| eval state=mvindex(combined,2)
| eval LineCount=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=LineCount path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.recordCount
| spath output=Org
Code path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute1
| spath output=TimeZone path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute2
| spath output=CDCRDC path=payload.gpmGenerateEventLogs.gpmGenerateEventLog{}.attribute3
| eval combined=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(OpName,EvType),state),Line
Count),OrgCode),TimeZone),CDCRDC)
| mvexpand combined
| eval combined=split(combined,",")
| eval OpName=mvindex(combined,0)
| eval EvType=mvindex(combined,1)
| eval state=mvindex(combined,2)
| eval LineCount=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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Path Finder

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,

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

Path Finder

This what i exactly did

|where status= "hvoperror" OR status="validationerror"]
| 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

0 Karma