topic Re: Timestamp extraction from CSV files on universal forwarder in Getting Data In

Timestamp extraction from CSV files on universal forwarder

jcbrendsel — Mon, 17 Dec 2012 16:17:37 GMT

I am struggling to get timestamp recognition to work for CSV files.

First, a bit about my setup. The CSV files are being processed by a Universal Forwarder and then the data is sent off to the indexer.

Here is a sample record from the csv source:

"Estimated","462819316490","050506831222","LineItem","Amazon Elastic Compute Cloud","840814","855132","191235","BoxUsage","RunInstances","us-east-1a","N","$0.065 per M1 Standard Small (m1.small) Linux/UNIX instance-hour (or partial hour)","2012-12-01 00:00:00","2012-12-01 01:00:00","23.00000000","0.0650000000","1.49500000","0.0650000000","1.49500000"

On the universal forwarder, I set a custom sourcetype,the props.conf file

[source::/var/log/billing/462819316490-aws-billing-detailed-line-items-*]
sourcetype = aws-billing-detailed
CHECK_METHOD=mod_time
SHOULD_LINEMERGE = false
TIME_FORMAT=%Y-%M-%D %H:%M:%S

The desired behavior would be that Splunk sets the timestamp to be the first of the two time columns in the csv data. (ie, 2012-12-01 00:00:00)

The problem is that Splunk is setting the timestamp to the file date.

What am I doing wrong?

Jon

Re: Timestamp extraction from CSV files on universal forwarder

Ayn — Mon, 17 Dec 2012 19:24:28 GMT

The problem likely lies in that the timestamp lies too far into the event. By default Splunk only looks at the first 150 character of each event to find a timestamp. This behaviour is configurable using the MAX_TIMESTAMP_LOOKAHEAD directive in props.conf.

Re: Timestamp extraction from CSV files on universal forwarder

jcbrendsel — Mon, 17 Dec 2012 19:47:04 GMT

That would definitely explain things. The field I was after was about 225 characters into the CSV file.

Re: Timestamp extraction from CSV files on universal forwarder

jcbrendsel — Mon, 17 Dec 2012 21:00:22 GMT

Update on this. Answer by Ayn was helpful in finding a couple of syntax errors, but the primary issue persists.

[source::/var/log/billing/462819316490-aws-billing-detailed-line-items-*]
sourcetype = aws-billing-detailed
CHECK_METHOD = modtime
SHOULD_LINEMERGE = false
MAX_TIMESTAMP_LOOKAHEAD = -1
TIME_FORMAT = %Y-%m-%d %H:%M:%S

Note: I am setting checkmethod = modtime just to make debugging easier. Once I figured this out I will remove it.

But this is still not properly extracting the time from the field showing in the original data snippet.