Getting Data In

How to transform a string to a date field?

sarthakb
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

somesoni2
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

vasanthmss
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.

V
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

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

sarthakb
Explorer

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

0 Karma

skoelpin
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...