topic Re: Daily and weekly discrete count in the same query? in Splunk Search

Daily and weekly discrete count in the same query?

dantose — Wed, 05 Jan 2022 22:59:18 GMT

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

Re: Daily and weekly discrete count in the same query?

johnhuang — Wed, 05 Jan 2022 23:29:13 GMT

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

Re: Daily and weekly discrete count in the same query?

dantose — Thu, 06 Jan 2022 00:41:26 GMT

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

Re: Daily and weekly discrete count in the same query?

johnhuang — Thu, 06 Jan 2022 00:52:57 GMT

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.