Splunk Search

How to search the average of a distinct count by date_hour over the course of a quarter?

dfenko
Explorer

I'm looking to get some summary statistics by date_hour on the number of distinct users in our systems.

Given a data set that looks like:

OCCURRED_DATE=10/1/2016 12:01:01; USERNAME=Person1
OCCURRED_DATE=10/1/2016 12:02:01; USERNAME=Person1
OCCURRED_DATE=10/1/2016 12:02:05; USERNAME=Person2
OCCURRED_DATE=10/2/2016 12:01:01; USERNAME=Person1
OCCURRED_DATE=10/2/2016 12:02:01; USERNAME=Person1

I know that the dc(USERNAME) for the 12:00 date_hour for 10/1/2016 is 2 and that the dc(USERNAME) for the 12:00 date_hour for 10/2/2016 is 1. I'd like to be able to have Splunk give me the average of those days worth of date_hours (i.e. 1.5)

I've tried several different iterations of the below without any success.

sourcetype=usage | timechart span=1h dc(USERNAME) as user_count | stats avg(user_count) by date_hour | sort date_hour

My original attempt included the below, which also doesn't produce results.

sourcetype=usage | stats avg(dc(USERNAME)) by date_hour | sort date_hour
0 Karma
1 Solution

madhack
Explorer

Timechart is losing the builtin special date fields. If you really want to average it across the same hour for all days, you could use eval's strftime() to re-calculate the hour:

sourcetype=usage | timechart span=1h dc(USERNAME) as dc | eval hour=strftime(_time, "%H") | stats avg(dc) by hour

View solution in original post

0 Karma

madhack
Explorer

Timechart is losing the builtin special date fields. If you really want to average it across the same hour for all days, you could use eval's strftime() to re-calculate the hour:

sourcetype=usage | timechart span=1h dc(USERNAME) as dc | eval hour=strftime(_time, "%H") | stats avg(dc) by hour
0 Karma
Get Updates on the Splunk Community!

Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...

There's No Place Like Chrome and the Splunk Platform

WATCH NOW!Malware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

Customer Experience | Join the Customer Advisory Board!

Are you ready to take your Splunk journey to the next level? 🚀 We invite you to join our elite squad ...