How can I get just one avg count per day? (With time span = 3h I get 3 counts per day)

[imsidrai]

Using the below query to get the daily avg user in during biz hours: 

index=pan_logs sourcetype=json_no_timestamp metricname="field total user"
|bin _time span=3h | stats latest(metricvalue) AS temp_count by metricname _time
| stats sum(temp_count) as "Users" by _time
|eval Date=strftime(_time,"%m/%d/%y")
|eval bustime=_time, bustime=strftime(bustime, "%H")
|eval day_of_week = strftime(_time,"%A")
|where ( bustime > 8 and bustime < 18) AND NOT (day_of_week="Saturday" OR day_of_week="Sunday")
|eventstats avg(Users) as DailyAvgUsers by Date
|eval DailyAvgUsers = round(DailyAvgUsers)
|table Date day_of_week DailyAvgUsers

but the query gives 3 counts per day  while i want only 1 for a day, when i change span to 6h , it gives me one count , but since i am counting only between 8AM to 6PM , it gives me no count when i run the search at 12PM Monday with 6h span.

How I can get one avg count per day? with time span = 3h 

[isoutamo]

I think that you should 1st select events which are within your business day definition and after that calculate those values with bin 1d.
r. Ismo

[imsidrai]

But if i run this with 1d span , the report only shows data for last day , it wont show the data for current day until the span is completed.

[isoutamo · Accepted solution]

Some clarification about time entities on Splunk:

• Time period - Time period from where you are looking events
• Time bin/span - slots inside Time period to where you are dividing the whole time period.

I understood that you have longer time period than 1d and you want to divide it to 1d bins? If this is correct then just look when day is Monday to Friday and hour eg. 8 to 17 or what ever those should be. After that you have just those correct events and then you can slot it to 1d bins (which contains events on business days with business hours).

[imsidrai]

thank you @isoutamo