Getting Data In

How do I configure the CSV field extractions when a field is nested within another field?

john_dagostino
Path Finder

I'm trying to extract the field values from a CSV file where the delimiter is a comma and most field values are contained within single-quotes/apostrophes such as this:

2018-06-19T12:15:24Z,Success,'MicrosoftExchange329e71ec88ae4615bbc36ab6ce41109e@abc.com','<4c0dc4ce-a96b-4121-8170-b9e4bbd79baa@hq.abc.com>','ReportItem',40451,+2018-06-19T12:00:10:000+00:00-UTC,'ExpressServices@abc.com',+2018-06-19T12:00:10:000+00:00-UTC,'Undeliverable: Trigger Pending Files in PROD'

This works for 99% of the events, however some events have a single-quoted value within another single-quoted value, such as the event below:

2018-06-19T12:15:25Z,Success,'MicrosoftExchange329e71ec88ae4615bbc36ab6ce41109e@abc.com','<9d5f7114-a2f5-4121-ba3f-dfdeea818a56@PCMEX04.hq.abc.com>','ReportItem',173809,+2018-06-19T12:00:11:000+00:00-UTC,'user@abc.com',+2018-06-19T12:00:11:000+00:00-UTC,'Undeliverable: 'Daily Unmatched Check Report''

For whatever reason this is preventing the line break for this event, causing it to merge with the following line. I'm using the following props.conf settings for the sourcetype but can't get these events extracted correctly or prevent the linemerge from happening.

DATETIME_CONFIG =
HEADER_FIELD_LINE_NUMBER = 2
INDEXED_EXTRACTIONS = csv
LINE_BREAKER = ([\r\n]+)
KV_MODE = none
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
category = Structured
description = Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled = false
pulldown_type = true
FIELD_DELIMITER = ,
FIELD_QUOTE = '
TIMESTAMP_FIELDS = legacyxmlemail_receivedate
BREAK_ONLY_BEFORE = ([\r\n]+)
Tags (1)

richgalloway
SplunkTrust
SplunkTrust

Do you have any control over how the CSV file is written? The embedded quotes should be escaped (\').

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

john_dagostino
Path Finder

Unfortunately not. Any idea why this would cause Splunk to ignore the line break?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Splunk may ignore it if it thinks the line break is within quotes.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!