Splunk Search

Time Difference and Average

geekf
Path Finder

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.

Labels (2)
Tags (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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

 

geekf
Path Finder

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?

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

geekf
Path Finder

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

0 Karma

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

geekf
Path Finder

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.

0 Karma

isoutamo
SplunkTrust
SplunkTrust
Can you add the whole SPL query here not only the two last line?
0 Karma

geekf
Path Finder

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

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

 

geekf
Path Finder

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 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

 

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...