Splunk Dev

HOWTO: query MySQL from Splunk on Linux 64bit

Jason
Motivator

I need to get data from a MySQL server with Splunk, either for a scripted lookup or a scripted input. I have seen a few posts mentioning getting MySQL connectivity set up in Splunk, but I am on a restricted user that can not install any server-wide packages.

What is a simple way that I can get it working by copying files into Splunk folders only?

Tags (3)
1 Solution

Jason
Motivator

This has been tested on Red Hat Enterprise Linux 5.5, 64 bit. It would probably work even easier on Ubuntu Lucid, for obvious reasons. If anyone has any suggestions for changes (especially if you know where to get the files below without stripping things out of DEBs) please comment!

(If you know where to get the proper files for your distro, feel free to use that if you can find the exact packages - MySQLdb requires libmysqlclient version 16, not 15 which comes with many distros. RPMs may be able to be found online; it looks like http://rpmfind.net/linux/RPM/ may have a version 16 from OpenSUSE. I did not test the file from the package listed there.)


Installing MySQLdb 1.2.2 python library on RHEL 64bit server, using Ubuntu parts

1. Download libmysqlclient16 DEB package from http://packages.ubuntu.com/lucid/amd64/libmysqlclient16/download

2. Use Windows 7-Zip (or any other method you can) to pull data.tar.gz/usr/lib/libmysqlclient_r.so.16.0.0 from the DEB file

3. Rename libmysqlclient_r.so.16.0.0 to libmysqlclient_r.so.16

4. Put the libmysqlclient_r.so.16 file in $SPLUNK_HOME/lib/

5. Download python-mysqldb DEB from http://packages.ubuntu.com/lucid/amd64/python-mysqldb/download

6. Use Windows 7-Zip (or any other method you can) to pull data.tar.gz from the DEB

7. Put the data.tar.gz/usr/lib/pyshared/python2.6/_mysql.so file in $SPLUNK_HOME/lib/python2.6/

8. Put the data.tar.gz/usr/share/pyshared/_mysql_exceptions.py file in $SPLUNK_HOME/lib/python2.6/

9. Put the data.tar.gz/usr/share/pyshared/MySQLdb/ folder in $SPLUNK_HOME/lib/python2.6/site-packages/


The python MySQLdb module is now installed in Splunk's python. Now you can write scripts like the following MySQLdb-based search script:

import csv,sys
import MySQLdb

#Define database connection, get cursor
db = MySQLdb.connect(host='ho.st.name',user='username',passwd='password',db='database')
cu = db.cursor(MySQLdb.cursors.DictCursor)

#Define output objects
header = ['input','output']
csv.writer(sys.stdout).writerow(header)
w = csv.DictWriter(sys.stdout, header)

#Query database for results
result = {}
result['input'] = sys.argv[1]
cu.execute("SELECT output FROM table WHERE id='%s'" % db.escape_string(result['input']))
qr = cu.fetchall()

#Output results
if qr == None:
        result['output'] = "None"
        w.writerow(result)
else:
        for row in qr:
                result['output'] = row['output']
                w.writerow(result)

View solution in original post

Jason
Motivator

This has been tested on Red Hat Enterprise Linux 5.5, 64 bit. It would probably work even easier on Ubuntu Lucid, for obvious reasons. If anyone has any suggestions for changes (especially if you know where to get the files below without stripping things out of DEBs) please comment!

(If you know where to get the proper files for your distro, feel free to use that if you can find the exact packages - MySQLdb requires libmysqlclient version 16, not 15 which comes with many distros. RPMs may be able to be found online; it looks like http://rpmfind.net/linux/RPM/ may have a version 16 from OpenSUSE. I did not test the file from the package listed there.)


Installing MySQLdb 1.2.2 python library on RHEL 64bit server, using Ubuntu parts

1. Download libmysqlclient16 DEB package from http://packages.ubuntu.com/lucid/amd64/libmysqlclient16/download

2. Use Windows 7-Zip (or any other method you can) to pull data.tar.gz/usr/lib/libmysqlclient_r.so.16.0.0 from the DEB file

3. Rename libmysqlclient_r.so.16.0.0 to libmysqlclient_r.so.16

4. Put the libmysqlclient_r.so.16 file in $SPLUNK_HOME/lib/

5. Download python-mysqldb DEB from http://packages.ubuntu.com/lucid/amd64/python-mysqldb/download

6. Use Windows 7-Zip (or any other method you can) to pull data.tar.gz from the DEB

7. Put the data.tar.gz/usr/lib/pyshared/python2.6/_mysql.so file in $SPLUNK_HOME/lib/python2.6/

8. Put the data.tar.gz/usr/share/pyshared/_mysql_exceptions.py file in $SPLUNK_HOME/lib/python2.6/

9. Put the data.tar.gz/usr/share/pyshared/MySQLdb/ folder in $SPLUNK_HOME/lib/python2.6/site-packages/


The python MySQLdb module is now installed in Splunk's python. Now you can write scripts like the following MySQLdb-based search script:

import csv,sys
import MySQLdb

#Define database connection, get cursor
db = MySQLdb.connect(host='ho.st.name',user='username',passwd='password',db='database')
cu = db.cursor(MySQLdb.cursors.DictCursor)

#Define output objects
header = ['input','output']
csv.writer(sys.stdout).writerow(header)
w = csv.DictWriter(sys.stdout, header)

#Query database for results
result = {}
result['input'] = sys.argv[1]
cu.execute("SELECT output FROM table WHERE id='%s'" % db.escape_string(result['input']))
qr = cu.fetchall()

#Output results
if qr == None:
        result['output'] = "None"
        w.writerow(result)
else:
        for row in qr:
                result['output'] = row['output']
                w.writerow(result)
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...