I've got some queries I need to do periodically that use the exact same base search, one with teh weekly uniques and one with the average daily uniques. I can do these seperately:
(search)
| stats dc(thing) as WeeklyCount
and
(search)
|bucket _time span=day
|stats dc(thing) as DailyCount by _time
|stats avg(DailyCount)
I've tried variations on appendpipe, but can't get it to work.
example:
(search)
| stats dc(thing) as WeeklyCount
|appendpipe [
bucket _time span=day
|stats dc(thing) as DailyCount by _time
|stats avg(DailyCount)]
returns only WeeklyCount. If I switch the order and have weeklycount in the append pipe, it gives my the correct average daily, but weekly reports as 0
Try this:
.....
| eventstats dc(thing) AS dc_weekly
| bucket _time span=1d
| stats min(_time) AS start_time max(_time) AS end_time dc(thing) AS dc_daily max(dc_weekly) AS dc_weekly BY _time
| stats min(_time) AS start_time max(_time) AS end_time avg(dc_daily) AS dc_daily max(dc_weekly) AS dc_weekly
| eval start_date=strftime(start_time, "%m-%d-%Y"), end_date=strftime(end_time, "%m-%d-%Y")
| eval dc_daily=ROUND(dc_daily)
| table start_date end_date dc_daily dc_weekly
Try this:
.....
| eventstats dc(thing) AS dc_weekly
| bucket _time span=1d
| stats min(_time) AS start_time max(_time) AS end_time dc(thing) AS dc_daily max(dc_weekly) AS dc_weekly BY _time
| stats min(_time) AS start_time max(_time) AS end_time avg(dc_daily) AS dc_daily max(dc_weekly) AS dc_weekly
| eval start_date=strftime(start_time, "%m-%d-%Y"), end_date=strftime(end_time, "%m-%d-%Y")
| eval dc_daily=ROUND(dc_daily)
| table start_date end_date dc_daily dc_weekly
That did it! Would you mind explaining it a bit?
The key here is use eventstats to calculate the distinct count over the entire week range and retain that value "max(dc_weekly)" through subsequent stats -- first stats calculate distinct daily and second stat to calculate average daily.