topic Re: Find a time average? in Splunk Search

Find a time average?

omgwut56k — Tue, 31 May 2016 19:05:05 GMT

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

Re: Find a time average?

cb_usps — Tue, 31 May 2016 19:32:05 GMT

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

Re: Find a time average?

somesoni2 — Tue, 31 May 2016 19:41:25 GMT

Could you share your current query?

Re: Find a time average?

omgwut56k — Tue, 31 May 2016 19:51:27 GMT

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

Re: Find a time average?

somesoni2 — Tue, 31 May 2016 20:31:27 GMT

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

Re: Find a time average?

woodcock — Tue, 31 May 2016 21:23:05 GMT

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.

Re: Find a time average?

omgwut56k — Tue, 31 May 2016 21:44:33 GMT

last is the result of

stats last(_time) as last

Re: Find a time average?

omgwut56k — Tue, 29 Sep 2020 09:50:06 GMT

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

Re: Find a time average?

woodcock — Wed, 01 Jun 2016 00:58:43 GMT

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

Re: Find a time average?

omgwut56k — Wed, 01 Jun 2016 13:19:35 GMT

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.

Re: Find a time average?

woodcock — Wed, 01 Jun 2016 14:32:51 GMT

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
            first(UserName) AS UserName
            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.