Splunk Search

TimeFormat conversion to millisecond

hemendralodhi
Contributor

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

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

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 

View solution in original post

javiergn
SplunkTrust
SplunkTrust

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 

hemendralodhi
Contributor

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

0 Karma

hemendralodhi
Contributor

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

alt text

0 Karma

hemendralodhi
Contributor

Great it worked now.

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

Thanks for your help.

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

Regards,
J

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...