Deployment Architecture

How to configure Splunk DB Connect to index a SQL table with XML so each event is one row of data in the database, not per line of XML?

ezajac
Path Finder

I've got a SQL Table that contains XML Blobs that I want to index in Splunk. The XML Blob contains carriage returns. When indexing, Splunk is creating a new row for each line in the XML Blob. I'm sure this can be worked around by modifying one of the .conf files in the Splunk DB Connect App. I would like one entry in Splunk for each row of data in the database (not one entry per line of XML in all the rows in the database).

Desired:
Entry 1:
"date id=123 xml_results=contentn body /body n /content "

Entry 2:
"date id=124 xml_results= content n body /body n /content "

What is happening now:
Entry 1:
"date id=123 xml_results= content "

Entry 2:
body /body

Entry 3:
/content

Entry 4:
"date id=124 xml= content "

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

Can the dbquery command show the output properly? If so, you should continue trying to solve this in Splunk... but if not, I would look for an SQL way to do it, because your JDBC driver is probably the problem and we're not going to be able to get around that. This looks like a good starting point: https://www.simple-talk.com/sql/database-administration/converting-string-data-to-xml-and-xml-to-str...

0 Karma

maciep
Champion

Are you specifying a custom sourcetype in your database input? And what format are you using? key-value, multi-line key-value, csv, etc?

0 Karma

ezajac
Path Finder

I am creating a custom sourcetype and using key-value.

0 Karma

maciep
Champion

would you be willing to try using the default sourcetype that dbconnect would use for that format? And if that doesn't work, also try the multi-line kv format? You may also want to put dbconnect on your indexer if it isn't already. If either of those work and you still want to use a custom sourcetype, that should be doable

There are configs for those default sourcetypes in props.conf for dbconnect that tell splunk how to break up the events. If you're using your own sourcetype, it's probably just guessing. Once you find what works, then you can copy their settings for that sourcetype and drop it into your own props for you custom sourcetype.

I hope that makes sense...and is somewhat correct. Let us know how it goes.

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