topic Re: How to transform a string to a date field? in Getting Data In

How to transform a string to a date field?

sarthakb — Mon, 08 Aug 2016 16:46:44 GMT

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.

Re: How to transform a string to a date field?

skoelpin — Mon, 08 Aug 2016 16:52:15 GMT

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

Re: How to transform a string to a date field?

somesoni2 — Mon, 08 Aug 2016 17:18:27 GMT

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

Re: How to transform a string to a date field?

somesoni2 — Mon, 08 Aug 2016 17:20:56 GMT

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

Re: How to transform a string to a date field?

gcusello — Mon, 08 Aug 2016 17:28:33 GMT

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

Re: How to transform a string to a date field?

vasanthmss — Mon, 08 Aug 2016 17:33:02 GMT

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.

Re: How to transform a string to a date field?

sarthakb — Mon, 08 Aug 2016 17:53:34 GMT

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