Comparision of hourly Log count by average log count of last 7 days.

[phanirohith97]

I have a Query need to compare hourly log count of today with the average value of last 7 days, if the count is greater then it should trigger an alert

[scelikok]

Hi @rally0321,

This can be achieved using different approach;

| tstats count where index=some_index earliest=-1d by _time span=1h 
| tstats append=t prestats=t count where index= some_index earliest=-7d@d latest=-1d@d by _time span=1h 
| table _time count psrsvd_gc 
| rename psrsvd_gc AS lastweek_counts 
| eventstats avg(lastweek_counts) as weekly_avg 
| where count>weekly_avg

[rally0321]

| tstats count where index=some_index earliest=-1d by _time span=1h 
| tstats append=t prestats=t count where index= some_index earliest=-7d@d latest=-1d@d by _time span=1h 
| table _time count psrsvd_gc 
| rename psrsvd_gc AS lastweek_counts 
| eventstats avg(lastweek_counts) as weekly_avg 
| where count>weekly_avg

Thanks. I can see result with below change.

1. add prestats=t to 1st tstats
2. where count>weekly_avg need change as lastweek_counts>weekly_avg

However I see below gap.

1. the average is the average for the past 1 week + today's count.

2. the result include past 1 week record in case any hour of last week is > average.

We expect to have below result.

1. the average is the average for the past 1 week only

2. the result include today's entry only.

[scelikok]

@phanirohith97, please try below query;

| tstats count where index=some_index earliest=-1d by _time span=1h 
| where count > 
    [| tstats count where index=some_index earliest=-7d@d latest=-1d@d by _time span=1h 
    | stats avg(count) as count 
    | rename count as search]

[rally0321]

| tstats count where index=some_index earliest=-1d by _time span=1h 
| where count > 
    [| tstats count where index=some_index earliest=-7d@d latest=-1d@d by _time span=1h 
    | stats avg(count) as count 
    | rename count as search]

Hi,

How can we show the average count in the result as well? I refer to value of search. Thanks.

_time                                                count
1 2021-03-04 09:00:00    491