topic Re: Time Difference and Average in Splunk Search

Time Difference and Average

geekf — Tue, 26 Jan 2021 21:05:58 GMT

I am trying to get the average of a time difference by using

| stats avg(time_dur) by type

and since I am using this search

| eval time_dur=tostring(strptime(LastSeen,"%d/%m/%y %H:%M:%S")-strptime(FirstSeen,"%d/%m/%y %H:%M:%S"),"duration")

I think the data is coming in a string, and because of that, I am not getting any results. If I use

eval time_dur=(strptime(LastSeen,"%d/%m/%y %H:%M:%S")-strptime(FirstSeen,"%d/%m/%y %H:%M:%S"))

and then do the eval I get the results; however, it comes up in some number. I would like to get the difference in HH:MM.

Re: Time Difference and Average

isoutamo — Tue, 26 Jan 2021 21:20:30 GMT

You are right, tostring converts this to string and then you couldn’t calculate average.
you could first calculate (as your 2nd example shows) difference as seconds then after stats wit avg use eval to format it as you try to do in your 1st example.
r. Ismo

Re: Time Difference and Average

bowesmana — Tue, 26 Jan 2021 21:27:19 GMT

The number is in seconds, so you can use either of the last two statements from this example

| makeresults | eval LastSeen="27/01/21 07:01:00", FirstSeen="27/01/21 05:11:22" | eval time_dur=(strptime(LastSeen,"%d/%m/%y %H:%M:%S")-strptime(FirstSeen,"%d/%m/%y %H:%M:%S")) | eval time_dur_str1=tostring(time_dur, "duration") | eval time_dur_str2=printf("%02d:%02d", floor(time_dur / 3600), (time_dur % 3600) / 60)

printf assumes that the hours is not more than 99 as it's only two places.

You could always use the tostring variant and trim the seconds off if you just want HH:MM

Re: Time Difference and Average

geekf — Wed, 27 Jan 2021 09:04:59 GMT

I am still unable to run avg

In your search, if I do

| stats avg(time_dur_str2)

I don't see any results, isn't it still a string?

Re: Time Difference and Average

isoutamo — Wed, 27 Jan 2021 11:46:59 GMT

As i said earlier you must run stats avg(xxx) over numeric fields not a string.

.... | stats avg(time_dur) as tDur_as_seconds | eval time_duration_as_string = tostring(tDur_as_seconds, "duration")

Re: Time Difference and Average

geekf — Wed, 27 Jan 2021 14:00:16 GMT

If I use | table it works fine, if I use | stats avg(time_dur) it doesn't show the right values.

Re: Time Difference and Average

isoutamo — Wed, 27 Jan 2021 14:09:32 GMT

| stats avg(time_dur) AS time_dur_as_seconds

shows average time duration in seconds. You must use

| eval time_dur_as_string = tostring(time_dur_as_seconds, "duration")

to get the real duration as human readable string (days+)hour:min:sec.
And if you want see it only as HH:MM then you could use:

| eval time_dur_as_string =printf("%02d:%02d", floor(time_dur_as_seconds / 3600), (time_dur_as_seconds % 3600) / 60)

as @bowesmana propose.
And to present only it, please use

| table time_dur_as_string
| rename time_dur_as_string AS "Time duration as (HH:SS)"

Re: Time Difference and Average

geekf — Wed, 27 Jan 2021 14:19:56 GMT

Thank you for promptly responding to my question, I appreciate it.

I am attaching both the table and stats results, kindly let me know if there is something wrong here.

Re: Time Difference and Average

isoutamo — Wed, 27 Jan 2021 14:40:13 GMT

Can you add the whole SPL query here not only the two last line?

Re: Time Difference and Average

geekf — Wed, 27 Jan 2021 15:09:08 GMT

Here are both of them:

index=main field=value1 | rex field=user "\w+\\\(?<user>.*)" | eval time=strftime(_time, "%d/%m/%y %H:%M:%S") | stats earliest(time) as FirstSeen, latest(time) as LastSeen by user | eval time_dur=(strptime(LastSeen,"%d/%m/%y %H:%M:%S")-strptime(FirstSeen,"%d/%m/%y %H:%M:%S")) | lookup some_user_lookup user AS user OUTPUT type | stats avg(time_dur) by type | eval time_dur=printf("%02d:%02d", floor(time_dur / 3600), (time_dur % 3600) / 60)

index=main field=value1 | rex field=user "\w+\\\(?<user>.*)" | eval time=strftime(_time, "%d/%m/%y %H:%M:%S") | stats earliest(time) as FirstSeen, latest(time) as LastSeen by user | eval time_dur=(strptime(LastSeen,"%d/%m/%y %H:%M:%S")-strptime(FirstSeen,"%d/%m/%y %H:%M:%S")) | lookup some_user_lookup user AS user OUTPUT type | eval time_dur=printf("%02d:%02d", floor(time_dur / 3600), (time_dur % 3600) / 60) | table type time_dur

Re: Time Difference and Average

bowesmana — Thu, 28 Jan 2021 06:49:54 GMT

Your problem is that when you do this

| stats avg(time_dur) by type

the field name is no longer time_dur, it is avg(time_dur), so the next line will do nothing as there is no longer such a field.

You need to do this

| stats avg(time_dur) as time_dur by type

i.e. use the 'as' to rename the avg field name to the original.

then you can format it as needed.

Re: Time Difference and Average

geekf — Thu, 28 Jan 2021 06:27:49 GMT

Thank you! This did the trick. If I may bother you again with one more question, how can I do the avg over a period of days? I tried this query but it didn't seem to work:

| bucket _time span=1d | stats count by _time

Re: Time Difference and Average

bowesmana — Thu, 28 Jan 2021 06:58:09 GMT

It's probably worth posting another question on this, with some clearer details on what you want, for example, how do you want to handle FirstSeen as 2021-01-10 23:59 and LastSeen as 2021-01-11 00:01. If you bin by 1d, then there will be no LastSeen.

Also when you use 'count by', did you mean the average.

And your question of 'average' by day, is that also by type.