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.
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")
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.
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
BTW, your example date is wrong (min is 85). An actual sample would be better.
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")
Thanks for your comment. i will try out both the options.
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