All Apps and Add-ons
Highlighted

Help with connecting to DB2 database and lookup a table at search time

Path Finder

I have read the documentation and successfully extract values using static lookup from a csv file into my search results. But i would like to achieve a similar behaviour from database i.e. connect to database and extract value from table into my search results.

I use Splunk 4.1.5 and have following questions:

  1. Is their a specific DB2 driver which needs to be loaded into Splunk in order to talk to database
  2. What type of driver - Is it JDBC-ODBC driver
  3. Do we need to write lookup script to talk to database or it can be done by modifying configuration files
  4. If it has to be a lookup script , then i guess it has to be Python script. So what about Java Developers who doesn't know Python ? I guess their is a opportunity to learn Python. But whats the learning curve and where to start?
  5. how to setup a JDBC connection to DB2 database in the Splunk config files
  6. How to use the DB2 connection to lookup a table in Splunk at search time?

I wish their is a step by step tutorial for this as it involves multiple steps/vendor specific drivers. Anyways an example with a sample code to connect to DB2 database would be greatly appreciated.

Tags (1)
0 Karma
Highlighted

Re: Help with connecting to DB2 database and lookup a table at search time

SplunkTrust
SplunkTrust

What you are trying to do will require what is called a scripted lookup. Start with http://www.splunk.com/base/Documentation/latest/Knowledge/Addfieldsfromexternaldatasources and go to the section about "Set up a fields lookup based on an external command or script"

The fundamental thing to remember is that Splunk doesn't particularly care how your scripted lookup does its thing - it is calling your code expecting it to do the needful. You don't (can't) define database drivers / connections inside of Splunk proper.

You can probably do this with Java by putting the proper wrapper script around the java program into the ${SPLUNK_HOME}/bin directory. But, I'd be concerned about the JVM startup time.

If you try to do this in python, you'll want the IBM DB2 drivers for python http://code.google.com/p/ibm-db/ These come as a python 'egg' which is pretty easy to install. In your position, I would not try to install this egg into the Splunk embedded python, but would rather put it in the 'system' python and use a small wrapper script to trampoline over to it, similar to Lowell's suggestion in http://answers.splunk.com/questions/2821/python-scripted-inputs-run-with-the-wrong-version-of-python...

Highlighted

Re: Help with connecting to DB2 database and lookup a table at search time

Path Finder

If i understand correctly their is no need to install python locally instead use splunk's bundled python distro to write lookup script. I need help to write lookup script, specifically how to install/setup/configure IBM DB2 drivers. I would suggest to update the documentation with sample lookup script and configuration of DB2 drivers. I am still not clear how to go about configuration of DB2 driver and write lookup script to extract data from database

0 Karma
Highlighted

Re: Help with connecting to DB2 database and lookup a table at search time

Path Finder

Sorry i am not sure what you mean by
"I would not try to install this egg into the Splunk embedded python, but would rather put it in the 'system' python and use a small wrapper script to trampoline over to it"
What if i use splunk's bundled python distro ? Does your statement still holds good. Could you please elaborate if possible with an example

0 Karma
Highlighted

Re: Help with connecting to DB2 database and lookup a table at search time

SplunkTrust
SplunkTrust

To my knowledge no example is readily available. What I am trying to say is that best practice advice is that end users NOT be adding their own python modules into the Splunk-provided Python runtime. You could break Splunk (admittedly unlikely) or your modules could disappear during a Splunk upgrade (very likely) Please refer to http://answers.splunk.com/questions/8/can-i-add-python-modules-to-the-splunk-environment/17#17

0 Karma
Highlighted

Re: Help with connecting to DB2 database and lookup a table at search time

Path Finder

Well i read on the forum its not recommended to have local install of Python version and if its absolutely necessary to have it then it needs to match Splunk Python version 2.6.4. So if your suggestion is not to add DB2 Python module into Splunk provided Python runtime then i have to do it on local install of Python but ondering how would i ultimately make it work under Splunk environment

0 Karma
Highlighted

Re: Help with connecting to DB2 database and lookup a table at search time

Path Finder

Suprisingly there are no concrete answers to my original posting. So i thought i would share my findings for the benefit of user community

Here is the link if you are looking python extension DB2 module http://wiki.python.org/moin/ODBC

But i am not sure which driver to use. I guess "pyodbc" i.e Python ODBC Library is open source but it doesnt have support for windows 64 bit OS for Python 2.6 version to run under Splunk provided Python runtime. So the other options are to go with commercial versions or try it with local install of Python version . But not sure again how to integrate it with Splunk environment.

I wish their is sufficent documentation on Python Extension Modules like DB2 database to work under Splunk environment

0 Karma