topic Can Splunk index .gzip'd XMLs from a SQL database? in All Apps and Add-ons

Can Splunk index .gzip'd XMLs from a SQL database?

nick405060 — Wed, 02 Oct 2019 23:27:57 GMT

We have a SQL table where a binary_log_content field is a .gzip'd XML for the logs we'd like to ingest.

How can I ingest these logs, short of using Python to query and save to a disk location that Splunk then monitors and auto-extracts?

Re: Can Splunk index .gzip'd XMLs from a SQL database?

woodcock — Wed, 02 Oct 2019 23:44:13 GMT

Splunk understands how to eat gzipped files so you do not have to do anything. Because you already have this data in a database, there is no reason to archive the extracts so I suggest something like this so that Splunk deletes the files after it forwards them:

[batch:///path/to/your/files/*.xml.gz]
move_policy = sinkhole
sourcetype=foo
index=bar
host_segment=bat

Re: Can Splunk index .gzip'd XMLs from a SQL database?

nick405060 — Thu, 03 Oct 2019 17:55:42 GMT

I don't think this actually answers my question @woodcock

Re: Can Splunk index .gzip'd XMLs from a SQL database?

nick405060 — Wed, 09 Oct 2019 18:01:15 GMT

You definitely cannot do it using dbconnect. If you do, you get the non-displayable column type image message:

https://answers.splunk.com/answers/587501/splunk-db-connect-checkpoint-set-to-null.html

Re: Can Splunk index .gzip'd XMLs from a SQL database?

nick405060 — Wed, 30 Sep 2020 02:30:47 GMT

How can I ingest these logs, short of using Python to query and save to a disk location that Splunk then monitors and auto-extracts?

Okay well ignoring the "short of" part... this is how you do this. Despite being in the db_connect directory, this does not use db_connect. You can move things to system/local or another directory that you think is more correct, if desired.

crontab -e

#!/usr/bin/env python3
* * * * * python3 /opt/splunk/etc/apps/splunk_app_db_connect/HOSTNAME_query.py

/opt/splunk/etc/apps/splunk_app_db_connect/HOSTNAME_query.py

import pymssql

conn = pymssql.connect(server='HOSTNAME:PORT',user='USERNAME',password='PASSWORD',database = 'DATABASE')
cursor = conn.cursor()
cursor.execute('SELECT MYFIELD,log_date FROM DATABASE.dbo.log_history WHERE log_date > DATEADD(day, -1, GETDATE())')

row = cursor.fetchone()
count = 0
while row:
    count = count+1
    row = cursor.fetchone()
    date=str(row[1])[:str(row[1]).index(' ')]
    open('/opt/splunk/var/run/splunk/xmlballs/'+date+'.gz','wb').write(row[0])

/opt/splunk/etc/apps/splunk_app_db_connect/local/inputs.conf

[monitor:///opt/splunk/var/run/splunk/xmlballs]
disabled = 0
sourcetype=compressed_xml
index=MYINDEX

Re: Can Splunk index .gzip'd XMLs from a SQL database?

woodcock — Fri, 11 Oct 2019 00:48:19 GMT

You are correct, it was not immediately clear that it is a field in the CSV that is gzipped. #cribl can do this.