How to convert a time field with multiple formats to epoch at search-time?

[landen99]

How do I take a time field with multiple human-readable formats and get the epoch time at search-time?

[landen99]

The generalized assumption in this question is that the formats cannot be known in advance or are too many to configure manually. Also that the time fields are not the ones that Splunk turns into _time or that we want to catch them before Splunk applies its own time conversion functions to the field.

[woodcock · Accepted solution]

Splunk really needs a search command to allow users to pass a timestamp through $SPLUNK_HOME/etc/system/default/datetime.xml. But since that doesn't work you will have to use coalesce, like this:

... | eval epochTime=colesce(strptime(timeStr, "<format 1>"), strptime(timeStr, "<format 2>"), ..., strptime(timeStr, "<format n>"))

[landen99]

I wasn't sure that coalesce would work with more than two.

[sideview]

If your string formatted time is of the form "2015-10-28 08:52:41", then

| eval epochTime=strptime(timeStr, "%Y-%m-%d %H:%M:%S")

If you need to convert multiple formats, you'll need multiple eval clauses.

docs for all of the functions that eval can use: http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/CommonEvalFunctions

You'll also probably find the quick reference guide handy. Note the last page which has all the common timeformat %X values.
https://www.splunk.com/web_assets/pdfs/secure/Splunk_Quick_Reference_Guide.pdf

And at pretty much any splunk event they often hand the reference cards out on 8.5"x11" cardstock.

[aholzer]

Please provide a sample of what your events look like (with the fields of interest highlighted), and what you'd like to see

[richgalloway]

What do you mean by "multiple human-readable formats"? Is the format unknown at search time?