HOWTO: query MySQL from Splunk on Linux 64bit

[Jason]

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?

[Jason · Accepted solution]

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)