topic Re: best way ito index data from database in Splunk Dev

best way ito index data from database

reutc — Tue, 14 Aug 2012 14:55:05 GMT

Hey,
I'm a little bit confused about the best way to index database data..
1. First way I found is building a python script which query the db and saves the
Data in files and then the indexing is just like regular logs..
But should I save the files directly to the splunk server? In which format the files should be?
Im not sure in which directory should I save the files .. and If splunk would save the data in the db directory like it does with the othdr data so it will be save twice on the server....

Second thing is the lookups. Should I use this option? What is it for exactly?

Thanks a lot!!

Re: best way ito index data from database

tfletcher_splun — Mon, 28 Sep 2020 12:16:01 GMT

Indexing data from a database:

You could do as you have proposed and put the files anywhere you want, but you may have an easier time with a scripted input to splunk that splunk will run periodically and then dump all output into splunk directly. This way you do not have to deal with file management, check this out for more on the scripted input:

http://docs.splunk.com/Documentation/Splunk/4.3.3/Data/Setupcustominputs

If you just want access to the database data (and are using MySQL) you may want to look at the MySQL data connector, in either case it can be used as an example of how to build a data connector, though this is a big task.

http://splunk-base.splunk.com/apps/36664/splunk-mysql-connector

Using lookups:

Lookups are a very powerful tool in splunk. Essentially they are just csv files that you can either dynamically reference in searches or you can have splunk automatically use to augment your event data. The idea is that if you have some things that always mean something else you can have splunk "look up" the value. For example, if you have a log with a lot of DNS names in it you could have that log map to a lookup of DNS name to ip addresses. This way when you are searching you could enter either the ip or the DNS name and splunk would know to pull everything for you.

The rule of thumb for lookups is that if you have a set of data that doesn't change often but that you need frequently you should use a lookup. In your case of a database it is possible that you may want some of your tables to be turned into lookups and NOT indexed. Say for example you had a table of users in your database that contained uid, username and email address. You would have your scripted input pull that data and instead of sending it to std out to be indexed have it update a csv file in your lookups directory in splunk. Then register that csv in transforms.conf as a lookup so that you can augment the data from your database automatically with user information. Take a look at this for more information:

http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Addfieldsfromexternaldatasources#Set_up_a_fields_lookup_based_on_a_static_file

Happy Splunking!

Re: best way ito index data from database

reutc — Wed, 15 Aug 2012 05:39:14 GMT

Thanks!!!!
One more thing I wonder about is the script itself..
The script example I found is a script which saves the data to files..
Is there an example for a python scripted input?..

Re: best way ito index data from database

reutc — Wed, 15 Aug 2012 06:24:37 GMT

Never mind I understood that I just have to use the print
Command...
But I want to use one script for many databases. Therefore I need to use the sys.argv inputs butwhen I insert to the command line of the scripted inputs variables the get an error message that the command line is not allowed?
Any suggestions? ?

Re: best way ito index data from database

Damien_Dallimor — Wed, 15 Aug 2012 06:34:06 GMT

Have a look at Ziegfried's new DBX app :

http://splunk-base.splunk.com/apps/50803/dbx-sql-database-extension

Re: best way ito index data from database

reutc — Wed, 15 Aug 2012 06:39:58 GMT

Your answer is great..
I want to use one script for many databases. Therefore I need to use the sys.argv inputs butwhen I insert to the command line of the scripted inputs variables the get an error message that the command line is not allowed? Any suggestions? ?

Re: best way ito index data from database

tfletcher_splun — Wed, 15 Aug 2012 17:38:20 GMT

So there's at least two ways to do that.
Way 1:
Specify the script multiple times in inputs.conf with different args, this way the same script will run against multiple db's in parallel.
Way 2:
Make a conf file for your script that your script periodically reads to get the database information then iterates across all databases. Note you can also print SPLUNK sourcetype=DB1 to change sourcetypes, hosts and sources as you are printing to std out, should you want to assign a different sourcetype per db

There are fancier ways to get this done, but this should get you started.

Re: best way ito index data from database

reutc — Fri, 17 Aug 2012 09:00:14 GMT

The examples I've seen specify only arguments like sourcetype, index ec'
But what if I want simple arguments like the database connection data? Or to use sys.argv?

Re: best way ito index data from database

tfletcher_splun — Mon, 28 Sep 2020 12:17:07 GMT

Just pass the args to the script in inputs.conf:

[script://$SPLUNK_HOME/etc/apps//bin/my_db_poll.sh -u username -p password -d db]
disabled = true # change to false to start the input, requires restart
host = # enter hostname here
index = main
interval = 30 #frequency to run the script
source = my_db
sourcetype = my_db_data

Re: best way ito index data from database

reutc — Mon, 20 Aug 2012 09:16:39 GMT

Its not working ..
I've tried many formats...
I'm trying just to check that it works.
Im sending an argument which calls reut that contains string and trying to print this argument in the script. In addition I'm printing also sys.argv.
I've tried these formats and nothing works:

[Script://.\bin\nisui2.py reut="try"]

[Script://.\bin\nisui2.py "try"]

[Script://.\bin\nisui2.py -u try]

[Script://.\bin\nisui2.py -u "try"]

[Script://.\bin\nisui2.py -reut "try"]

[Script://.\bin\nisui2.py -reut try]

Re: best way ito index data from database

reutc — Mon, 20 Aug 2012 09:20:11 GMT

And of course there is a backslah before and after the bin directory..

Re: best way ito index data from database

tfletcher_splun — Mon, 20 Aug 2012 17:51:10 GMT

You likely do not have the right syntax. Look at docs for inputs.conf:

http://docs.splunk.com/Documentation/Splunk/4.3.3/admin/Inputsconf

There are special things in syntax if you are on windows. Be sure to read all of the docs. Then it is the same as passing args for any script you would run. Look at the nix app and the windows app depending on your platform for examples of how to do it.