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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...