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
Explorer

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
alt text

What I would like it to looks like is this:
alt text

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)

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

somesoni2
Revered Legend

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
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...