How to configure Splunk to parse a custom date timestamp in a column of a CSV file?

[ebailey]

I have a CSV file I need Splunk to consume every day that has a date time stamp in a column. I cannot figure out how to get Splunk to read the date time stamp properly since its Month is in all caps.

01-JAN-09 07.21.53.656000 AM

Any idea how to get this to work correctly? I do not see any options that fit this use case.

Thanks!

[gfuente]

The only issue I'm seeing, is that the sample it's too old. Using

MAX_DAYS_AGO=9999

Makes Splunk auto identify the date correctly. Anyway you could use this props

[  ]
CHARSET=UTF-8
MAX_DAYS_AGO=9999
SHOULD_LINEMERGE=true
disabled=false
TIME_FORMAT=%d-%b-%y %H.%M.%S.%6N
TIME_PREFIX=^

And, as a last option you could use SEDCMD to replace the uppercase months to lower case if finally is needed

With

SEDCMD-date = s/JAN/jan/g

For example

[somesoni2]

Following time format should would work for you.

| gentimes start=-1 | eval _time=strptime("01-FEB-09 07.21.53.656000 AM","%d-%b-%y %H.%M.%S.%N %p") | table _time | append [| gentimes start=-1 | eval _time=strptime("07-FEB-09 07.21.53.656000 AM","%d-%b-%y %H.%M.%S.%N %p") | table _time] | append [| gentimes start=-1 | eval _time=strptime("21-MAR-09 07.21.53.656000 AM","%d-%b-%y %H.%M.%S.%N %p") | table _time]

Also, since the years is more than 6 years ago, you would've to set MAX_DAYS_AGO in the sourcetype definition in props.conf to allow Splunk to parse this timestamp.

MAX_DAYS_AGO = <integer>
* Specifies the maximum number of days past, from the current date, that an extracted date
  can be valid.
* For example, if MAX_DAYS_AGO = 10, Splunk ignores dates that are older than 10 days ago.
* Defaults to 2000 (days), maximum 10951.
* IMPORTANT: If your data is older than 2000 days, increase this setting.

[richgalloway · Accepted solution]

What TIME_FORMAT strings have you tried? Have you tried "%d-%b-%y %H.%M.%S.%6N %p"?

[ebailey]

I tried that with no luck - thanks

[ebailey]

This ended up being the solution combined with adding max_days_ago. Thanks!

[richgalloway]

Please accept the answer.