topic How to edit my search to show a row for each hour in my results, even if the count is zero or null? in Splunk Search

How to edit my search to show a row for each hour in my results, even if the count is zero or null?

SAPrabhakar — Mon, 07 Nov 2016 21:58:08 GMT

I have a search that looks like this:

LoginAudit message.name="LoginAudit Event" | eval HourAndMin=strftime(_time, "%H") | stats count(eval(ErrorMessage="SUCCESS"))  as LoginSuccess by HourAndMin

When this search is run, it gives me a row for each hour. What I am attempting to accomplish is make sure that there is a row for every hour regardless of if data exists or not.

In my data, I have some results returned for the 9 and 10 slot so the data looks like this

What I would like it to looks like is this:

Where 08 and 11 are set to 0 because there are no results (I have left out the remaining hours in the day for brevity)

Re: How to edit my search to show a row for each hour in my results, even if the count is zero or null?

somesoni2 — Mon, 07 Nov 2016 22:49:25 GMT

Try like this

LoginAudit message.name="LoginAudit Event" | eval HourAndMin=strftime(_time, "%H") | stats count(eval(ErrorMessage="SUCCESS"))  as LoginSuccess by HourAndMin
| append [| gentimes start=-1 | eval HourAndMin=mvrange(0,24,1) | table HourAndMin | mvexpand HourAndMin
| eval HourAndMin=substr("0".HourAndMin,-2) | eval LoginSuccess=0]
| stats max(LoginSuccess) as LoginSuccess by HourAndMin

Re: How to edit my search to show a row for each hour in my results, even if the count is zero or null?

niketn — Tue, 08 Nov 2016 05:00:45 GMT

You can try timechart with span=1h for hourly bins which are filled with 0 by default:

<Your Base Search> | timechart span=1h count(eval(user="admin")) as LoginSuccess