where clause over row

[bic]

I have the below query which gives me the count of alerts over period of an hour, I wanted to make it as an alert by adding a where clause and display only those host names whose value is more than 4 in the given time period of 1 hour

index="mail" alert_type="Danger" | eval firsttime=strptime(time_triggered, "%m/%d/%y %H:%M:%S") | eval hour=strftime(firsttime,"%H") | chart count(host_info) as count_of_host by hour host_info | fields - NULL

This gives below result

hour Host1 Host2
04 1 4
10 1 3

The result I want is

hour Host2
04 4

Also time_triggered is not same as _time.
Please help.

[knielsen · Accepted solution]

I think instead of your "... | chart ..." you should do:

<your base search with strptime stuff and eval hour> | stats count(host_info) as count by hour, host_info | where count>=4 | xyseries hour host_info count

Hth,
-Kai.