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!

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...