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