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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...