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.
TIME_FORMAT strings have you tried? Have you tried "%d-%b-%y %H.%M.%S.%6N %p"?
This ended up being the solution combined with adding maxdaysago. Thanks!
Please accept the answer.
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.
The only issue I'm seeing, is that the sample it's too old. Using
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
SEDCMD-date = s/JAN/jan/g