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.
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
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
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?
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")
If I use | table it works fine, if I use | stats avg(time_dur) it doesn't show the right values.
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
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.
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
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.
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