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