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

[SAPrabhakar]

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)

[niketn]

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

[somesoni2 · Accepted solution]

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