Getting Data In

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

ebailey
Communicator

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!

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

gfuente
Motivator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

ebailey
Communicator

I tried that with no luck - thanks

0 Karma

ebailey
Communicator

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Please accept the answer.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...