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

[tonahoyos]

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

[harsmarvania57]

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

[tonahoyos]

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.

[harsmarvania57]

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)

[tonahoyos]

Thank you! This was perfect!

[nickhills]

Instead of the final "strftime" try this:

totalDuration=tostring(SumDuration, "duration")

[nickhills]

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

[tonahoyos]

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

[nickhills]

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

[tonahoyos]

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

[skoelpin]

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?

[tonahoyos]

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.

[skoelpin]

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

[tonahoyos]

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

[skoelpin]

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

[nickhills]

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