Splunk Search

## Find a time average?

Path Finder

I am trying to determine the mean or average time when a event occurs.

I would like to find an average of the last column if at possible.

My example data:

first --------------------last--------------------lastmode
09:16:26 -------------06:26:51---------------19:47:38
10:49:29 -------------06:43:22
11:36:55--------------07:33:50
06:19:09 ------------ 10:26:29

Tags (2)
Esteemed Legend

As I guessed, you are looking for average time of day (seconds since midnight).
It looks like you are assuming that everyone checks in and out within the same day (a reasonable assumption but there will certainly be exceptions) and you are trying to calculate an average check-out time (what time do people generally leave the office and head for home?).
You had a problem where you were using the same `BY` clause in both `stats` commands which is definitely wrong.
The second one should be one of either `BY nick` OR `BY day_of_swipe` OR nothing at all. I went with `BY nick` with the assumption that that is the door/office and you need the final answer broken out by that.

I think what you need is this:

``````... | eval date_month = strftime(_time, "%m") | eval date_mday = strftime(_time, "%d") | eval date_wday = strftime(_time, "%A")
| eval day_of_swipe=(date_month + " "+ date_mday + " "+ date_wday )
| eval Month=strftime(_time,"%b")
| eval secondsSinceMidnight_TOD=_time - relative_time(_time, "@d")
| stats first(JobTitle) AS JobTitle
count(dvc_name) AS swipes_per_day
first(Manager_Name) AS Manager_Name
first(department) AS department
min(secondsSinceMidnight_TOD) AS earliest_TOD
last(secondsSinceMidnight_TOD) AS latest_TOD
values(secondsSinceMidnight_TOD) AS TOD_values
first(Month) AS Month BY nick day_of_swipe
| stats min(earliest_TOD) AS earliest_earliest_TOD
max(latest_TOD) AS latest_latest_TOD
values(TOD_values) AS combined_TOD_values
mean(latest_TOD) AS mean_latest_TOD BY nick
| convert timeformat="%H:%M:%S" ctime(earliest_earliest_TOD)    AS earliest_earliest_TOD
| convert timeformat="%H:%M:%S" ctime(latest_latest_TOD)           AS latest_latest_TOD
| convert timeformat="%H:%M:%S" ctime(combined_TOD_values) AS combined_TOD_values
| convert timeformat="%H:%M:%S" ctime(mean_latest_TOD)          AS mean_latest_TOD
``````

P.S. NEVER use the "free" (but wrong) `date_*` fields for anything other than a quick sanity check; if you need them, calculate them yourself.

Esteemed Legend

What is `last`? Is it a duration? Is it a time? If a time, is it in epoch with a fieldformat or is it a string just like is shown? It would probably be best to show your base search.

Path Finder

searching stuff here.. finding the average time of day a person opens the front door.

| eval day_of_swipe=(date_month + " "+ date_mday + " "+ date_wday )
| eval day_of_swipe=(date_month + " "+ date_mday + " "+ date_wday ) | eval Month=strftime(_time,"%b")
| stats first(JobTitle) as JobTitle first(UserName) as UserName count(dvc_name) as swipes_per_day first(Manager_Name) as Manager_Name first(department) as department first(_time) as first_time last(_time) as last_time values(_time) as time_values values(Month) as Month by nick,date_mday
| stats first(first_time) as first_time last(last_time) as last_time mean(last_time) as last_mean values(time_values) as time_values by nick,date_mday

| convert timeformat="%H:%M:%S" ctime(first_time) AS first_time
| convert timeformat="%H:%M:%S" ctime(last_time) AS last_time
| convert timeformat="%H:%M:%S" ctime(time_values) AS time_values | convert timeformat="%H:%M:%S" ctime(last_mean) AS last_mean

Path Finder

last is the result of

stats last(_time) as last

Esteemed Legend

How does it make any sense to average an end time? On what bases can we average it? I suppose we could normalize the timestamp to numberOfseconds since midnight and average that but otherwise it makes no sense to do avg(_time)!

Path Finder

Agreed, since _time is in epoch and growing averaging the number is pointless. Weirdly enough I can do this in Excel which in this case is my unfortunate solution.

SplunkTrust

Try like this

``````your base search | eval last_epoch=strptime(last,"%H:%M:%S") | stats mean(last_epoch) as mean | eval mean=strftime(mean,"%H:%M:%S")
``````
SplunkTrust

Could you share your current query?

Explorer

I am assuming you have a search with the fields 'first' and 'last'. The following search will create one result which will be the mean of the 'last' field.

``````your_iniital_search ... | stats mean(last)
``````

There are also median(), mode(), avg()

Path Finder

I had the same idea, however it does not appear to work that way.

Get Updates on the Splunk Community!

#### Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

#### We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...

#### What’s New in Splunk Cloud Platform 9.1.2308?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2308! Analysts can ...