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
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.
That's why my answer doesn't use start=-1
, it generates more days.
To see if we're on the right track, leave off the where
and see if you get the empty days added correctly.
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
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