Splunk Search
Highlighted

How can I extract a timestamp from a specific CSV field regardless of format?

New Member

Hi all,

I have a CSV file that could look like this:

Ticket-ID,User ID,Site ID,Site City,Site State,Create_date,Modified-date,...
TICKET,USERID,3331,CITY,STATE,3/23/2016,1459430427,...

(Note that I have two dates back to back)
or, it could look like this:

Ticket-ID,User ID,Site ID,Site City,Site State,Create_date,Modified-date,...
TICKET,USERID,3331,CITY,STATE,1459430427,1459430427,...

(Note that both Create_date and Modified-date are epoch timestamps in this version).

I unfortunately have very little control over whether (a) or (b) is handed to the forwarder.

When the file is indexed, all the records are using the create date of the CSV file rather than the Create_date field.

My props.conf looks like this:

[remedy_merch]
TIMESTAMP_FIELDS=Create_date
SHOULD_LINEMERGE=false
INDEXED_EXTRACTIONS=csv
NO_BINARY_CHECK=true
KV_MODE=none
disabled=false
pulldown_type=true
TRUNCATE=0

Through looking at other answers, I have determined that I probably need to add the following field:

TIME_PREFIX=^([^,]*,){5}

so that I always start the time "search" at the right spot in the file. However, I need to exclude Modified-date from the time attribute, and I cannot be sure what format the Createdate will come in as. Do I have options (other than pre-processing the CSV to give it consistent timestamps)?

Thanks!

0 Karma
Highlighted

Re: How can I extract a timestamp from a specific CSV field regardless of format?

SplunkTrust
SplunkTrust

For indexed_extractions, you should be able to use

TIMESTAMP_FIELDS = field1,field2,...,fieldn

Like, perhaps in your case

TIMESTAMP_FIELDS = Create_date

As per the docs on Extracting fields from files with structured data.