https://community.splunk.com/t5/Splunk-Search/How-to-search-the-average-of-a-distinct-count-by-date-hour-over/m-p/231717#M68727 <P>I'm looking to get some summary statistics by date_hour on the number of distinct users in our systems.</P> <P>Given a data set that looks like:</P> <P>OCCURRED_DATE=10/1/2016 12:01:01; USERNAME=Person1<BR /> OCCURRED_DATE=10/1/2016 12:02:01; USERNAME=Person1<BR /> OCCURRED_DATE=10/1/2016 12:02:05; USERNAME=Person2<BR /> OCCURRED_DATE=10/2/2016 12:01:01; USERNAME=Person1<BR /> OCCURRED_DATE=10/2/2016 12:02:01; USERNAME=Person1</P> <P>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)</P> <P>I've tried several different iterations of the below without any success.</P> <PRE><CODE>sourcetype=usage | timechart span=1h dc(USERNAME) as user_count | stats avg(user_count) by date_hour | sort date_hour </CODE></PRE> <P>My original attempt included the below, which also doesn't produce results.</P> <PRE><CODE>sourcetype=usage | stats avg(dc(USERNAME)) by date_hour | sort date_hour </CODE></PRE> Tue, 29 Sep 2020 11:17:20 GMT dfenko 2020-09-29T11:17:20Z https://community.splunk.com/t5/Splunk-Search/How-to-search-the-average-of-a-distinct-count-by-date-hour-over/m-p/231717#M68727 <P>I'm looking to get some summary statistics by date_hour on the number of distinct users in our systems.</P> <P>Given a data set that looks like:</P> <P>OCCURRED_DATE=10/1/2016 12:01:01; USERNAME=Person1<BR /> OCCURRED_DATE=10/1/2016 12:02:01; USERNAME=Person1<BR /> OCCURRED_DATE=10/1/2016 12:02:05; USERNAME=Person2<BR /> OCCURRED_DATE=10/2/2016 12:01:01; USERNAME=Person1<BR /> OCCURRED_DATE=10/2/2016 12:02:01; USERNAME=Person1</P> <P>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)</P> <P>I've tried several different iterations of the below without any success.</P> <PRE><CODE>sourcetype=usage | timechart span=1h dc(USERNAME) as user_count | stats avg(user_count) by date_hour | sort date_hour </CODE></PRE> <P>My original attempt included the below, which also doesn't produce results.</P> <PRE><CODE>sourcetype=usage | stats avg(dc(USERNAME)) by date_hour | sort date_hour </CODE></PRE> Tue, 29 Sep 2020 11:17:20 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-the-average-of-a-distinct-count-by-date-hour-over/m-p/231717#M68727 dfenko 2020-09-29T11:17:20Z https://community.splunk.com/t5/Splunk-Search/How-to-search-the-average-of-a-distinct-count-by-date-hour-over/m-p/231718#M68728 <P>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:</P> <PRE><CODE>sourcetype=usage | timechart span=1h dc(USERNAME) as dc | eval hour=strftime(_time, "%H") | stats avg(dc) by hour </CODE></PRE> Wed, 05 Oct 2016 16:16:34 GMT https://community.splunk.com/t5/Splunk-Search/How-to-search-the-average-of-a-distinct-count-by-date-hour-over/m-p/231718#M68728 madhack 2016-10-05T16:16:34Z