Example script that polls a database

Here is an example of a scripted input that polls a database. In the configuration for the script, you specify the interval at which the script runs.

Note: No script can be a "one size fits all." The purpose of this example is to provide a basic framework that you modify and customize for your specific purposes. This script polls a database and writes the records retrieved to stdout. The data queries, connection, authentication, and processing of the query have been simplified.

This example script does the following:

• Builds a query to extract 1000 records from a database
• Connects to a database
• Stores the key to the database as an eventID.
• Writes the last eventID retrieved from the database to file to track which events have been indexed.
• Executes the query and writes the results to stdout for the Splunk platform to index.

Pseudo-code for the example script

# Script to poll a database
#
# Reads 1000 records from a database, 
# writes them to stdout for indexing by splunk,
# tracks last event read
#
# SQL Query information:
#
# Microsoft SQL Server syntax
# SELECT TOP 1000 eventID, transactionID, transactionStatus FROM table 
#    WHERE eventID > lastEventID ORDER BY eventID
#
# 
# MySQL syntax
# SELECT eventID, transactionID, transactionStatus FROM table 
#     WHERE eventID > lastEventID LIMIT 1000 ORDER BY eventID
#
#
# Oracle syntax
# SELECT eventID, transactionID, transactionStatus FROM table 
#    WHERE eventID > lastEventID AND ROWNUM <= 1000 ORDER BY eventID
# 
# ==========================
# Database Fields
# ==========================
# 
# eventID                        autoincrement unsigned
# transactionId char             8
# transactionStatus varchar     32
#
# =========================
# Sample Data
# =========================
#
# 1 A1756202    submitted
# 2 C1756213    acknowledged
# 3 A1756202    rejected
# 4 N1756754    submitted
# 5 C1756213    completed


import needed files

define SQL query

define SQL connection information
  db server address
  db user
  db pw
  db name

define path to file that holds eventID of last record read
  last_eventid_filepath

read eventID from last_eventid file

connect to database

execute SQL query

write query results to stdout

close db connection

update eventID in last_eventid file

Script example, poll a database (Python)

Here is a python version of the database poll example. The code has been simplified for readability and does not necessarily represent best coding practices. Please modify according to your needs.

The Python version of the example accesses a Microsoft SQL Server database. It assumes you have all the necessary libraries referenced in the script.

This example requires the following:

• pymssql language extension
• FreeTDS 0.63 or newer (*nix and Mac OS X platforms only)

This script has been made cross-compatible with Python 2 and Python 3 using python-future.

hello_db_poll_script.py

#!/usr/bin/python

from __future__ import print_function
from builtins import str
import _mssql
import os
import sys
from time import localtime,strftime
import time

sql_server = "SQLserver" #Address to database server
database = "hello_db_database"
sql_uname = "splunk_user"
sql_pw = "changeme"
columns = 'TOP 1000 eventID, transactionID, transactionStatus'
table = 'hello_table'

countkey = 'eventID'

last_eventid_filepath = "" # user supplies correct path

# Open file containing the last event ID and get the last record read
last_eventid = 0;
if os.path.isfile(last_eventid_filepath):
    try:
        last_eventid_file = open(last_eventid_filepath,'r')
        last_eventid = int(last_eventid_file.readline())
        last_eventid_file.close()

    # Catch the exception. Real exception handler would be more robust    
    except IOError:
        sys.stderr.write('Error: failed to read last_eventid file, ' + last_eventid_filepath + '\n')
        sys.exit(2)
else:
    sys.stderr.write('Error: ' + last_eventid_filepath + ' file not found! Starting from zero. \n')

# Fetch 1000 rows starting from the last event read
# SELECT TOP 1000 eventID, transactionID, transactionStatus FROM table WHERE eventID > lastEventID ORDER BY eventID
sql_query = 'SELECT ' + columns + ' FROM ' + table + ' WHERE ' + countkey + ' > ' + str(last_eventid) + ' ORDER BY ' + countkey

try:
    conn = _mssql.connect(sql_server, sql_uname, sql_pw, database)
    conn.execute_query(sql_query)
    # timestamp the returned data
    indexTime = "[" + strftime("%m/%d/%Y %H:%M:%S %p %Z",localtime()) + "]"
    for row in conn:
    	print("%s eventID=%s, transactionID=%s, transactionStatus=%s" % (indexTime, row['eventID'], row['transactionID'], row['transactionStatus']))

    this_last_eventid = row['eventID'] 

# Catch the exception. Real exception handler would be more robust    
except _mssql.MssqlDatabaseException as e: 
    sys.stderr.write('Database Connection Error!\n')
    sys.exit(2)
    
finally:
    conn.close()
    
if this_last_eventid > 0:
   try:
     last_eventid_file = open(last_eventid_filepath,'w')
     last_eventid_file.write(this_last_eventid)
     last_eventid_file.close()
   # Catch the exception. Real exception handler would be more robust    
   except IOError:
     sys.stderr.write('Error writing last_eventid to file: ' + last_eventid_filepath + '\n')
     sys.exit(2)