Custom Date Conversion and Parsing

[sheanineseven]

We have a field in some of the JSON that that is a string representation of a date. The date is formatted like this:

Tue, 31 Dec 2013 17:48:19 +0000

How can I convert this string into a date, so that we can parse various parts of the date out at search-time? Currently it seems that we cannot convert it at all, and the resulting fieldname comes out blank. Ex:

... | convert timeformat="%Y-%m-%d" ctime(OUR_DATE_FIELD) AS day | table OUR_DATE_FIELD, day
... | convert timeformat="%Y-%m" ctime(OUR_DATE_FIELD) AS month | table OUR_DATE_FIELD, month
... | eval day=strftime(OUR_DATE_FIELD, "%y-%m-%d") | table OUR_DATE_FIELD, day

However, if we replace OUR_DATE_FIELD with _time, it obviously resolves correctly.

Inevitably I would like to be able to easily toggle between report types like:

... | convert timeformat="%Y-%m-%d" ctime(OUR_DATE_FIELD) AS day | timechart count by day
... | convert timeformat="%Y-%m" ctime(OUR_DATE_FIELD) AS month | timechart count by month

[kristian_kolb]

Your conversions require that you use the full specification of the timestamp;

Tue, 31 Dec 2013 17:48:19 +0000

First you need to convert it from string to epoch

eval xxx = strptime(your_date_field,"%a, %d %b %Y %H:%M:%S %z")

Then you can convert it back into subparts with the strftime function

eval yyy = strftime(xxx,"%d")
eval zzz = strftime(xxx,"%m-%d")

If the timestamp field you are using for these conversion is the same that is used by Splunk for indexing the event, you can skip the first step and use _time instead. In this case, you may also have the desired subparts extracted in the various date_* fields. Beware though, that these are not adjusted for timezone differences.

see;

www.strftime.net
http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/Commontimeformatvariables
http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/CommonEvalFunctions

/K

[martin_mueller]

For converting human-readable date strings into epoch time you can use eval's strptime function. Its result can be used in strftime to get whichever part of the date you need.