Splunk Search

How do you turn a string into time format for editable stats?

tonahoyos
Explorer

Hello,

I have been trying to use the stats command to determine the duration of a certain event. When I add the data, the start and end times upload as a string, but I need to convert it to time format in order to eval and output a total duration of the event. The output with the following code gives me 22:49:41, which should not be the final answer. What am I doing wrong?

| eval Start= strptime(StartTime,"%m/%d/%y %H:%M:%S %p"), End= strptime(EndTime,"%m/%d/%y %H:%M:%S %p")
| eval Duration=End-Start| stats sum(Duration) as SumDuration
| fieldformat SumDuration=strftime(SumDuration,"%H:%M:%S")
0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

The way you are converting into Hour, minute and Second from epoch time is converting data correctly but it is actually converting to Date 02/01/1970 04:49:41 for me because my user is in GMT (As it depends on user timezone), If I change user timezone to GMT-5 then it will give me 22:49:41 which is actually 01/01/1970 22:49:41

If you want to convert time difference or duration into string then use eval SumDuration=tostring(SumDuration, "duration")

0 Karma

tonahoyos
Explorer

I want to show the final duration in hh:mm:ss, not as a string value. Also, I don't think the answer is 22:49:41, it is a very big value. This is the same answer I am getting.

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Try this

 | makeresults 
 | eval StartTime="12/22/18 10:04:29 AM" 
 | eval EndTime="12/23/18 10:14:19 AM" 
 | eval Start= strptime(StartTime,"%m/%d/%y %I:%M:%S %p"), End= strptime(EndTime,"%m/%d/%y %I:%M:%S %p") 
 | eval Duration=End-Start 
 | stats sum(Duration) as duration
 | eval f_hr=floor(duration/3600), f_min=floor(duration/(60)) % 60, f_sec=floor(duration % 60)
 | eval f_hr=if(len(f_hr)=1, "0".tostring(f_hr),tostring(f_hr)), f_min=if(len(f_min)=1, "0".tostring(f_min),tostring(f_min)), f_sec=if(len(f_sec)=1, "0".tostring(f_sec),tostring(f_sec))
 | eval SumDuration=f_hr.":".f_min.":".f_sec
 | fields - f_hr,f_min,f_sec

EDIT: Updated above query (Found that you are using %H with %p which will not work because %H is 24 hour format so there will be no meaning of %p if you change it to AM or PM it will give you same result so I have updated %H to %I in above query)

0 Karma

tonahoyos
Explorer

Thank you! This was perfect!

0 Karma

nickhills
Ultra Champion

Instead of the final "strftime" try this:

totalDuration=tostring(SumDuration, "duration")
If my comment helps, please give it a thumbs up!
0 Karma

nickhills
Ultra Champion

as commented by @harsmarvania57 below - i think the above works - here is a run anywhere example which works:

| makeresults 
| eval StartTime="12/22/18 10:04:29 AM" 
| eval EndTime="12/23/18 15:04:29 AM" 
| eval Start= strptime(StartTime,"%m/%d/%y %H:%M:%S %p"), End= strptime(EndTime,"%m/%d/%y %H:%M:%S %p") 
| eval Duration=End-Start 
| stats sum(Duration) as SumDuration 
| eval totalDuration=tostring(SumDuration, "duration")

It should give you 1 day + 5 hours

If my comment helps, please give it a thumbs up!
0 Karma

tonahoyos
Explorer

I want to show the final duration in hh:mm:ss, not as a string value.

0 Karma

nickhills
Ultra Champion

The above will do that (if its less than 1 day).

If my comment helps, please give it a thumbs up!
0 Karma

tonahoyos
Explorer

Oh ok!! Perfect, now I just have to figure out how to subtract one day in epoch time.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Splunk uses epoch time by default so the math will be much easier and more accurate compared to doing it with a non standard human readable timestamp. I'm guessing that StartTime and EndTime are not in epoch time format. You should check this

It's also possible that your strftime is incorrect. Can you post the value?

0 Karma

tonahoyos
Explorer

Hello skoelpin,

The SumDuration answer is 22:49:41, but it should be a sum of about 5 hours. StartTime and EndTime upload in a string format, so I can not use the stats command, SPLUNK gives me an error.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

I need to see the StartTime and EndTime format. This is most likely the cause of the incorrect SumDuration.

0 Karma

tonahoyos
Explorer

The StartTime and EndTime format are a string and were uploaded as:

12/22/18 10:04:29 AM

When converted with strptime (| eval Start= strptime(StartTime,"%m/%d/%y %H:%M:%S %p")
), one event looks like:
1545483185.000000

Sum Duration is then:

100181.000000

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Splunk uses epoch time for its base time. You should do the math on epoch time rather than the string formatted time. Your sum duration is number of seconds, so if you divide by 3600, you get ~27.8 hours

0 Karma

nickhills
Ultra Champion

I don't think you can use strf on arbitrary times - its expecting to convert from epoch

If my comment helps, please give it a thumbs up!
0 Karma