I am trying to create a search that will generate a report showing host by event count in the last hour and also the average 7 day hourly event count per host.
So far i have the below search that shows host by event count over the last hour - but i am struggling to get a column added showing the weekly hourly average?
| tstats count where index=* by host, index, sourcetype | addtotals | sort -Total | fields - Total | rename count as events_latest_hour
Any help on how i get a column added showing the 7 day hourly average for event count ?
Hi @scout29,
let me understand: you want:
number of events by host in the last hour and the hourly average in the last seven days, is it correct?
please try this:
| tstats count WHERE index=* BY host _time span=1h
| stats
avg(count) AS Average
values(eval(if(_time>=now()-3600,count,0))) AS "Last hour"
BY host
Ciao.
Giuseppe
Hi @scout29,
let me understand: you want:
number of events by host in the last hour and the hourly average in the last seven days, is it correct?
please try this:
| tstats count WHERE index=* BY host _time span=1h
| stats
avg(count) AS Average
values(eval(if(_time>=now()-3600,count,0))) AS "Last hour"
BY host
Ciao.
Giuseppe
@gcusello - Thanks! How could i modify this to include one more column showing the percent variance between the average count and latest hour count?
Hi @scout29,
use an eval command at the end of the search.
Ciao.
Giuseppe
P.S.: Karma Points are appreciated 😉