Splunk Search

Daily and weekly discrete count in the same query?

dantose
Explorer

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

Labels (1)
0 Karma
1 Solution

johnhuang
Motivator

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

View solution in original post

johnhuang
Motivator

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
Explorer

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

0 Karma

johnhuang
Motivator

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.

 

 

 

 

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...