From Splunk Wiki
Database data as a Splunk Input
- utilize scripted input to run a query every x minutes
Caveats and Details
- the script must be able to connect to the database - the query must be constructed - the query must 'keep track of itself' -- this is pretty crucial. Typically, you do not want to run
select * from table
every 5 minutes. This would result in duplicated data being send to splunk. The safest bet is to use some type of unique rowid or sequence number that is pulled right from the schema.
If I had a table (a query result, actually) that looked like
seqno, time, message
then I could do this:
oldmax = readmaxfile max = select max(seqno) from table select * from table where seqno > oldmax writemaxfile (max) for each of the returned results: format nicely ( kv pairs work well here! ) write to stdout
Of course, it probably makes sense to seed this first -- unless the intention is to dump the entire query result into splunk to begin with. I have been using a file in /tmp to keep the seed, but this is probably far from optimal. Some place more controllable within the splunk 'var' tree would probably be better....
IF THERE IS NO SEQUENCE NUMBER, the next best option is probably a timestamp if available. Depending on the granularity of such, you could be duplicating (or missing) data depending on the condition....
Some databases have a built in sequence number facility, but this is not standard, and not always dependable... Oracle does not have this facility. I believe that MySQL does. I don't think that MSSQL does.
Ray has posted a script here: http://www.splunkbase.com/apps/All/Technologies/Databases/app:Script+for+database+inputs
"This script has been successfully used in a number of deployments, and should work with Oracle, MySQL, and sybase databases as-is. Other database types can be added by installing the appropriate perl DBD module, and editing the script to configure for the new dbtype.
In this version, all of the SQL code has been abstracted from the script, and all parameters including the query are passed as commandline arguments to the script."