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?
Try like this
index=vpn sourcetype="cisco:asa" message_id="734001" | timechart span=1h count BY host
| untable _time host count | where count=0
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
Try like this
index=vpn sourcetype="cisco:asa" message_id="734001" | timechart span=1h count BY host
| untable _time host count | where count=0
@somesoni2 - I really am going to have to memorize the record output format for timechart
. That usage of untable
is too sweet.
Oh..my...this completely blew my mind. Thank you so much~