topic Re: How can I get one avg count per day? with time span = 3h I get 3 counts per day.Just need to identify one avg user c in Splunk Search

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

imsidrai — Fri, 02 Sep 2022 15:46:54 GMT

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

Re: How can I get one avg count per day? with time span = 3h I get 3 counts per day.Just need to identify one avg user c

isoutamo — Tue, 30 Aug 2022 07:18:00 GMT

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

Re: How can I get one avg count per day? with time span = 3h I get 3 counts per day.Just need to identify one avg user c

imsidrai — Tue, 30 Aug 2022 09:56:11 GMT

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.

Re: How can I get one avg count per day? with time span = 3h I get 3 counts per day.Just need to identify one avg user c

isoutamo — Tue, 30 Aug 2022 11:14:38 GMT

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).

Re: How can I get one avg count per day? with time span = 3h I get 3 counts per day.Just need to identify one avg user c

imsidrai — Fri, 02 Sep 2022 15:21:07 GMT

thank you @isoutamo