From Splunk Wiki

(Redirected from Apps:DatabaseCollection)
Jump to: navigation, search

Database data as a Splunk Input

General Concept

- 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.

Current State

Ray has posted a script here:

"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."

Personal tools
Hot Wiki Topics

About Splunk >
  • Search and navigate IT data from applications, servers and network devices in real-time.
  • Download Splunk