topic Re: time conversion in Splunk Search

time conversion

Jananee_iNautix — Thu, 09 Jan 2014 13:28:39 GMT

I want to convert time in HH:MM:SS format to milliseconds and vice versa.Can this be possible in splunk.
For example:in my log I have time as
2013-12-09 17:58:01 :Total time to upload [1340] ms

I want to convert this to milliseconds and add to 1340 ms and convert that to HH:MM:SS format.Can anyone help me on this.

Re: time conversion

dwaddle — Mon, 28 Sep 2020 15:36:59 GMT

First of all, Splunk parses one timestamp from every event unless you configure it not to, and stores it in a field called _time. The _time field is stored as a unix time_t - or in "epoch" format. That is, the # of seconds since Jan 1 1970 00:00:00 GMT. It is highly likely that your 2013-12-09 17:58:01 was already converted. But if not there is a solution to that.

The key here is field extraction. You will need to make sure that 2013-12-09 17:58:01 and 1340 are extracted into fields. For the sake of this discussion, let's say they are extracted into fields called start_time and upload_time_ms. So

start_time=2013-12-09 17:58:01
upload_time_ms=1340

Once we have this, then we can use the 'eval' command to convert start_time into a time_t value, add the value of upload_time_ms to it, and then re-convert the resulting value into a formatted timestamp again. It will go something like this.

| eval upload_end_time=strftime( 
     (strptime(start_time,"%Y-%m-%d %H:%M:%S") + upload_time_ms/1000), "%H:%M:%S" )

If I got my parentheses and such matched up properly, this should do it. I had to adjust the scale on upload_time_ms to make it seconds instead of milliseonds.

Re: time conversion

somesoni2 — Thu, 09 Jan 2014 14:35:36 GMT

Try this.

<your base search>| rex "\[(?<MS>.*)\]" | eval mytime=_time+(MS/1000) | eval NewTime=strftime(mytime,"%Y-%m-%d %H:%M:%S")

Re: time conversion

lukejadamec — Thu, 09 Jan 2014 14:54:58 GMT

Has the time to upload value been extracted as a field value yet?

Re: time conversion

lukejadamec — Thu, 09 Jan 2014 15:28:59 GMT

First you need to extract the time to upload as a field. Try this to verify that it extracts the value correctly:

search |rex ".*upload\s\[(?P<uploadTime>\d+)\]"

Look for a new field called 'uploadTime' and verify that it has the correct value.

Once that works, then this should do the math to convert _time to milliseconds, add the uploadTime, and convert the total time to a new field called totalTime.

Here is an example using a Windows EventCode as the uploadTime:

index=main EventCode=* | rex ".*upload\s\[(?P<uploadTime>\d+)\]" | convert mktime(_time) as etime |eval mstime=(etime*1000)  |eval msttime=(mstime+EventCode)  |eval mstdtime=(msttime/1000) |convert timeformat="%H:%M:%S" ctime(mstdtime) AS totalTime | table  _time etime mstime msttime mstdtime totalTime EventCode

Here is an example where the date and time are preserved:

index=main EventCode=* | rex ".*upload\s\[(?P<uploadTime>\d+)\]" | convert mktime(_time) as etime |eval mstime=(etime*1000)  |eval msttime=(mstime+EventCode)  |eval mstdtime=(msttime/1000) |convert  ctime(mstdtime) AS totalTime | table  _time etime mstime msttime mstdtime totalTime EventCode

Once you get the extraction right, subsitute the EventCode field in the search and math with the uploadTime field.

The totalTime should include decimal seconds.

Re: time conversion

Jananee_iNautix — Mon, 13 Jan 2014 05:22:18 GMT

Can u say how to convert milliseconds to seconds without decimal point.
I tried the following search query..
eval transfertime=toNumber(t/1000).The result i got is
transfertime
0.7161
But I want the result as whole number.Is there any function in splunk to do that.

Re: time conversion

linu1988 — Mon, 13 Jan 2014 06:48:50 GMT

if you want the ceiling value then use eval ceil() function or the floor function. You can use an if statement you decide which whole number will be appropriate. or you can also do transfertime=toNumber(t%1000)...

Re: time conversion

kristian_kolb — Mon, 13 Jan 2014 06:55:06 GMT

or the round() function may be even more appropriate;

eval mytime = round(_time + MS / 1000, 0)

Re: time conversion

Jananee_iNautix — Mon, 13 Jan 2014 10:44:10 GMT

Thanks.it works