topic Re: How do you turn a string into time format for editable stats? in Splunk Search

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

tonahoyos — Thu, 03 Jan 2019 20:51:17 GMT

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

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

skoelpin — Thu, 03 Jan 2019 21:38:38 GMT

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?

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

tonahoyos — Thu, 03 Jan 2019 21:57:33 GMT

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.

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

skoelpin — Thu, 03 Jan 2019 22:03:38 GMT

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

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

tonahoyos — Fri, 04 Jan 2019 14:33:57 GMT

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

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

nickhills — Fri, 04 Jan 2019 14:40:56 GMT

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

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

nickhills — Fri, 04 Jan 2019 14:49:39 GMT

Instead of the final "strftime" try this:

totalDuration=tostring(SumDuration, "duration")

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

harsmarvania57 — Fri, 04 Jan 2019 14:51:30 GMT

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

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

nickhills — Fri, 04 Jan 2019 14:54:50 GMT

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

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

tonahoyos — Fri, 04 Jan 2019 15:16:55 GMT

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

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

tonahoyos — Fri, 04 Jan 2019 15:17:45 GMT

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.

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

nickhills — Fri, 04 Jan 2019 15:21:14 GMT

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

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

harsmarvania57 — Fri, 04 Jan 2019 15:35:47 GMT

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)

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

tonahoyos — Fri, 04 Jan 2019 16:04:24 GMT

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

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

tonahoyos — Fri, 04 Jan 2019 16:05:53 GMT

Thank you! This was perfect!

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

skoelpin — Mon, 07 Jan 2019 15:00:41 GMT

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