Getting Data In

How to transform a string to a date field?

Explorer

I have a regex which extracts a field with format MMM DD YYYY HH24:MM:SS, SSS GMT TIMEZONEDIFF - e.g. Aug 08 2016 10:85:49,444 GMT-0300*

Currently the extracted field is of String type.

How can I use field transformation to convert it to a date format?

End objective is - if I use

|table extractedDateField

I should see the values in a Splunk date format.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Does this field is same (or expected to be same) as your _time field? If yes then follow instructions from @skoelpin on setting _time value from this field.
If not (means this is another date time field available in your events and you just want to convert it to epoch format), then you'd need to create a calculated field using the strptime function. See this for more details

https://docs.splunk.com/Documentation/Splunk/6.4.2/Knowledge/definecalcfields

The eval expression that you can use will be like this

EVAL-extractedDateField = strptime(extractedDateField,"%b %d %Y %H:%M:%S,%N %Z")

Try this if above doesn't work

EVAL-extractedDateField = strptime(replace(extractedDateField,"^((\S+\s){4})([^-\+]+)(-|\+)(\d+)","\1\3") ,"%b %d %Y %H:%M:%S,%N %Z")

View solution in original post

Motivator

try this,

The expected token for convert your string is %b %d %Y %H:%M:%S,%N GMT%z

|stats c | eval c1="Aug 08 2016 10:25:49,444 GMT-0300" | eval c2=strptime(c1,"%b %d %Y %H:%M:%S,%N GMT%z") | eval c3=strftime(c2,"%+")

c1 : String / Extracted field
c2: Converted in Unix time (epoch)
c3: epoch to human readable format.

0 Karma

Legend

Every way, to transform your string in a date in epochtime you have to eztract a field with your date and then tranform it using eval

| eval newfield=strptime(yourfield,"%m %d %Y %H:%M:%S")

If you want it in another format instead epochtime use strftime.
Bye.
Giuseppe

0 Karma

SplunkTrust
SplunkTrust

BTW, your example date is wrong (min is 85). An actual sample would be better.

0 Karma

SplunkTrust
SplunkTrust

Does this field is same (or expected to be same) as your _time field? If yes then follow instructions from @skoelpin on setting _time value from this field.
If not (means this is another date time field available in your events and you just want to convert it to epoch format), then you'd need to create a calculated field using the strptime function. See this for more details

https://docs.splunk.com/Documentation/Splunk/6.4.2/Knowledge/definecalcfields

The eval expression that you can use will be like this

EVAL-extractedDateField = strptime(extractedDateField,"%b %d %Y %H:%M:%S,%N %Z")

Try this if above doesn't work

EVAL-extractedDateField = strptime(replace(extractedDateField,"^((\S+\s){4})([^-\+]+)(-|\+)(\d+)","\1\3") ,"%b %d %Y %H:%M:%S,%N %Z")

View solution in original post

Explorer

Thanks for your comment. i will try out both the options.

0 Karma

SplunkTrust
SplunkTrust

Why not just parse this at index time?

Put this in your props.conf on the indexer and you won't need to extract and tarnsform it

TIME_FORMAT=%Y%m%d/%H%M%S.%3N
 TIME_PREFEX=set as per your log
 MAX_TIMESTAMP_LOOKAHEAD=17
0 Karma