Consuming logs from a database

Some applications are built to store their logs in a database. This has the advantage that the logs are centralized, but the disadvantage that it is difficult to scale beyond the limits of the database server. If the logs are pulled into Splunk, it is possible to take advantage of the Splunk interface and correlate these events with other logs.

The process to consume database logs is essentially as follows:

1. Build the query to retrieve the appropriate events; something as follows:

select date, id, log from log_table 

2. Identify the field that you will use as your pointer. This is usually either an id field or a date field.
3. Modify the query to use this pointer field; use something such as the following code:

select date,id,log from log_table where id>4567 

4. Use scripted input to run this query, capture the pointer field, and print the results. There are a number of applications in a number of languages available at https://splunkbase.splunk.com to get you started, but you can use any language and any tool that you like. The app I know best is the JDBC scripted input, which uses Java and a user-provided JDBC driver. Just to quickly illustrate how it is used, perform the following steps:
   1. Ensure Java 1.5 (or greater) is installed.
   2. Download the app.
   3. Copy your JDBC driver JAR to bin/lib.
   4. Duplicate bin/example to bin/myapp.
   5. Modify bin/myapp/query.properties to look something like the following code:

driverClass=com.mysql.jdbc.Driver 
connectionString=jdbc:mysql://mydb:3306/myapp?user=u&password=p 
iteratorField=id 
query=select date,id,log from entries where id>${id} order by id

1. 6. Add a matching stanza to inputs.conf.

[script://./bin/run.sh myapp] 
interval = 60 
sourcetype = myapp 
source = jdbc 

That should be it. iteratorField is not needed if your query handles not retrieving duplicate data through some other way.

Another popular add-on option is Splunk DB Connect 3.0. Splunk DB Connect enables powerful linkages between Splunk and the structured data world of SQL and JDBC. You can read the details here: https://www.splunk.com/blog/2017/02/20/splunk-db-connect-3-released.html