How to set an alert when the number of authentication events is zero during any 1-hour interval?

[_smp_]

I am trying to match (i.e alert) on a condition when the number of authentication events is zero from any host during any 1-hour interval. I've come close with various stats, timechart and chart commands, but I can't seem to find the right search. I started with a stats search, but then came to realize there are no rows in the output when there are no events to count (obviously):

index=vpn sourcetype="cisco:asa" message_id="734001"
| eval HourOfDay = strftime(_time,"%H")
| stats count BY host, HourOfDay
| where count=0

This output of my chart search includes the zero rows I'm after, but I can't seem to figure out how to match only the count=0 rows since there is no count field in the output:

index=vpn sourcetype="cisco:asa" message_id="734001"
| eval HourOfDay = strftime(_time,"%H")
| chart count(HourOfDay) OVER HourOfDay BY host

I also had into a similar issue with timechart:

index=vpn sourcetype="cisco:asa" message_id="734001"
| eval HourOfDay = strftime(_time,"%H")
| timechart span=1h count BY host

Anyone know how to solve this?

[DalJeanis]

Try this -

index=vpn sourcetype="cisco:asa" message_id="734001" 
| eval HourOfDay = strftime(_time,"%H") 
| stats count as mycount BY host, HourOfDay 
| rename COMMENT as "add zero records for every host for every hour in the range"
| appendpipe 
    [| stats values(host) as host values(HourOfDay) as HourOfDay 
     | mvexpand host | mvexpand HourOfDay | eval mycount=0 ]
| stats sum(mycount) as mycount BY host, HourOfDay
| where mycount=0

[somesoni2 · Accepted solution]

Try like this

index=vpn sourcetype="cisco:asa" message_id="734001" | timechart span=1h count BY host
| untable _time host count | where count=0

[DalJeanis]

@somesoni2 - I really am going to have to memorize the record output format for timechart. That usage of untable is too sweet.

[_smp_]

Oh..my...this completely blew my mind. Thank you so much~