topic Re: query MySQL from Splunk on Linux 64bit in Splunk Dev

query MySQL from Splunk on Linux 64bit

rgcox1 — Wed, 27 Apr 2011 21:02:46 GMT

This is a repeat of HOWTO: query MySQL from Splunk on Linux 64bit, but that solution did not work for me. Running an Intel processor, but tried the solution with both x86 and AMD64 packages.
Unlike the previous post, I do have root access.
Running Splunk 4.2 on RHEL 5.5. 64bit

Re: query MySQL from Splunk on Linux 64bit

gkanapathy — Wed, 27 Apr 2011 22:01:45 GMT

You my consider instead using the pymsql python module. It's pure Python, so all you need to do is stick it in the bin directory of your app under your python script, e.g. put it in $SPLUNK_HOME/etc/apps/myapplication/bin/pymysql/ and this script in $SPLUNK_HOME/etc/apps/myapplication/bin/testconnection.py:

import traceback
import pymysql
try:
    cn = pymsql.connect(host="mydbserver",port=3306,user="root",password="whatever",db="mydatabase")
    csr = cn.cursor()
    csr.execute("SELECT 1+1 FROM DUAL")

    for r in csr:
        print r
except Exception as e:
    print e
    traceback.print_exc()

Re: query MySQL from Splunk on Linux 64bit

gkanapathy — Wed, 27 Apr 2011 22:03:08 GMT

There might be some squirrellyness between Python versions on the except clause. I guess you could drop the whole try...except part for testing purposes.

Re: query MySQL from Splunk on Linux 64bit

gkanapathy — Wed, 27 Apr 2011 23:13:06 GMT

it just needs to be in the python search path. the first entry in the python search path is usually the directory in which the calling script is located.

Re: query MySQL from Splunk on Linux 64bit

rgcox1 — Wed, 27 Apr 2011 23:19:21 GMT

Any installation necessary, or just put the egg file in the directory?

Re: query MySQL from Splunk on Linux 64bit

gkanapathy — Wed, 27 Apr 2011 23:24:59 GMT

i wouldn't use an egg file or try to install it or anything. i would just drop the source pymysql directory right into the bin folder. It's pure Python, so that should be all you need.

Re: query MySQL from Splunk on Linux 64bit

jpass — Fri, 24 Aug 2012 23:06:32 GMT

Create a scripted input. The script starts by checking the primary key values of my db table and compares it to the previously recorded value from the last time the script was run. If the database maximum primary key is higher than the old value I know there are new records that need to be added to splunk. Next, the script queries for all records where the max id > old max id. Output your fields and format them the way you want which you'll have to reference in transforms.conf. The last part of the script records the new max id value to be used the next time the script runs. Lastly, in Splunk Web create a scripted input and run it every few minutes, every hour...whatever interval you want.

Another option you might try is MySQL Connector It works for me in Splunk 4.3.

Re: query MySQL from Splunk on Linux 64bit

jpass — Wed, 30 Jan 2013 15:36:37 GMT

If you can upgrade Splunk to 4.3, the DB Connect app will allow you to do this & it's really easy to use.