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, Karma 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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

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

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...