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!
What TIME_FORMAT
strings have you tried? Have you tried "%d-%b-%y %H.%M.%S.%6N %p"?
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
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.
What TIME_FORMAT
strings have you tried? Have you tried "%d-%b-%y %H.%M.%S.%6N %p"?
I tried that with no luck - thanks
This ended up being the solution combined with adding max_days_ago. Thanks!
Please accept the answer.