After a bit of trial and error I figured it out.
index=accesslog earliest=-7d@m-5m latest==-7d@m
| append [ search index=accesslog earliest=-14d@m-5m latest==-14d@m ]
| append [ search index=accesslog earliest=-21d@m-5m latest==-21d@m ]
| bucket _time span=1m
| stats count AS LastCPM by ClientIP Hostname date_mday
| stats avg(LastCPM) as LastAvg, stdev(LastCPM) as LastStdev by Hostname
| join type=outer Hostname [ search index=accesslog earliest=-5m@m latest=now@m
| bucket _time span=1m
| stats count AS NowCPM by ClientIP Hostname date_mday
| stats avg(NowCPM) as NowAvg by Hostname ]
| where NowAvg > LastAvg+LastStdev*2
The output will be something like this:
Hostname LastAvg LastStdev NowAvg
host3.domain.com 25.370370 32.720253 26.600000
host55.domain.com 10.610169 14.518736 13.900000
The logic is, look at average and stdev of events(connections) per client per host per minute. Compare that with the current average.
You can add more append s to cover additional time ranges. Every company/site/webservice have unique access profile. Some will have similar stats per specific times of the day of week (like in our case), others will have similar stats every day regardless of weekday/weekend, hence you can change the append time frames to yesterday, day before, etc, instead of going week earlier and earlier.
Now, I have 2 things I'm not sure about. Do I need to add _time to the stats count lines? I think that would be needed only if you want to compare non-equal timespans (e.g. >5 mins in the top lines and exactly last 5 minutes under the join ).
The other thing is, if I wanted to "extract" individual events from the resulting stats tables (after the where pipe), but I could not find the way to do that, the underlying logs that made the stats are lost?
... View more