Daily and weekly discrete count in the same query?

[dantose]

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

[johnhuang · Accepted solution]

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

[dantose]

That did it! Would you mind explaining it a bit?

[johnhuang]

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.