Why does Splunk DB Connect truncate data from SQL varchar columns that contain newlines?

[jhambrick]

I'm currently working on ingesting data from a large SQL database table. One of the columns is a varchar that contains line breaks. For some reason, dbx will ingest the item on the first line, but will ignore the rest of the data in this column. Can someone please provide assistance on a workaround for this issue?

example

ColumnA
string1;
string2;
string3;
string4;
string5

When the data is pushed to splunk, it is stored as an kv pair that only contains the first string. We need all of the strings to be included in this kv pair.

ColumnA = string1

Please provide feedback if possible. Thanks

[javiergn]

I don't know why that wouldn't work from DB Connect but as a workaround I would try one of the following:

• Ask your DBAs to create an SQL view that points to your table but replaces newlines with other character in that particular column. Something like:

  CREATE VIEW MyView AS
  SELECT
  Col1
  ,Col2
  ,REPLACE(ColWithNewLines, CHAR(13), 'somethingelse') AS ColWithNewLines
  FROM MyWonderfulTable

• Another option would be to apply the same logic straight from DB Connect, avoiding the SQL view in this case. So rather than selecting your fields straight from the table using the wizard, use a query instead.

Hope that makes sense.

Thanks,
J

[jhambrick]

I've already taken the query route. I'm using a query that only selects 1000 rows at a time.