topic Re: TimeFormat conversion to millisecond in Splunk Search

TimeFormat conversion to millisecond

hemendralodhi — Tue, 03 Jan 2017 11:50:27 GMT

Hello,

I have extracted field which contains application response time in below format.

Format:

00:00:00.000
00:00:00.003
00:00:00.545
00:00:01.053
00:00:29.544

I need to convert it into millisecond or second. I tried using strptime and convert function but not working as expected. Can someone please advise?

Thanks
Hemendra

Re: TimeFormat conversion to millisecond

javiergn — Tue, 03 Jan 2017 12:10:23 GMT

I would simply use rex for that (assuming your field name is myfield):

| rex field=myfield "(?<hour>\d{2}):(?<minute>\d{2}):(?<second>\d{2})\.(?<millisecond>\d{3})"
| eval durationInSeconds = hour * 3600 + minute * 60 + second + millisecond/1000
| eval durationInMilliseconds = durationInSeconds * 1000

Example:

| stats count | fields - count
| eval myfield = "01:01:29.544"
| rex field=myfield "(?<hour>\d{2}):(?<minute>\d{2}):(?<second>\d{2})\.(?<millisecond>\d{3})"
| eval durationInSeconds = hour * 3600 + minute * 60 + second + millisecond/1000
| eval durationInMilliseconds = durationInSeconds * 1000

Output:

durationInSeconds
3689.544000 

durationInMilliseconds  
3689544.000

Re: TimeFormat conversion to millisecond

hemendralodhi — Tue, 03 Jan 2017 13:45:48 GMT

Thanks javiergn for your quick response. I tried with your method but it seems I am getting 2 values and also some are missing:

Here is the actual field value:

00:00:00.000
00:00:00.002
00:00:00.003
00:00:00.005
00:00:00.006

Here is the Result:

durationInSeconds durationInMilliseconds
0.123000 123.000
0.123000 123.000
0.109000 109.000
0.109000 109.000
0.148000 148.000
0.148000 148.000
0.043000 43.000
0.043000 43.000
0.084000 84.000
0.084000 84.000
0.143000 143.000
0.143000 143.000
0.033000 33.000
0.033000 33.000

Re: TimeFormat conversion to millisecond

hemendralodhi — Tue, 03 Jan 2017 13:52:42 GMT

Look like my extracted field is behaving differently. When ran your rex search to see values in field got below. Not Sure why it is coming like this. Is it possible that it is related to string or numerical field value?

hour minute second millisecond
00 00 00 123
00 00 00 123
00 00 00 109
00 00 00 109
00 00 00 148
00 00 00 148
00 00 00 043
00 00 00 043
00 00 00 084
00 00 00 084
00 00 00 143
00 00 00 143

Re: TimeFormat conversion to millisecond

javiergn — Tue, 03 Jan 2017 15:47:54 GMT

Hi,

Sorry I'm confused. Can you post the exact query you are running please (ensure you are using the code sample button otherwise it will escape some symbols) and also how your raw data looks like?

In any case, I have tried replicating your example above and it seems to be working fine:

| stats count | fields - count
| eval myfield = split("00:00:00.000, 00:00:00.002, 00:00:00.003, 00:00:00.005, 00:00:00.006", ",")
| mvexpand myfield
| rex field=myfield "(?<hour>\d{2}):(?<minute>\d{2}):(?<second>\d{2})\.(?<millisecond>\d{3})"
| eval durationInSeconds = hour * 3600 + minute * 60 + second + millisecond/1000
| eval durationInMilliseconds = durationInSeconds * 1000

Output: see picture below

Re: TimeFormat conversion to millisecond

hemendralodhi — Thu, 05 Jan 2017 09:18:45 GMT

Great it worked now.

I was checking the result using values(Field) and this was reordering the results.

Thanks for your help.

Re: TimeFormat conversion to millisecond

javiergn — Thu, 05 Jan 2017 13:20:24 GMT

No worries.
Please don't forget to mark it as answered so that others can benefit from it.

Regards,
J