Scripted Input

[sushma6]

Hi,

I have an SQL query which queries the database and generated an html file as output. I run this query for every half an hour. Need to do the same using SPLUNK. Please help me on the below questions:

-- How can I give SQL query as input to the SPLUNK?
-- How can I automate it to run it for every half an hour and generate an excel sheet?

Thanks in advance for your help!

Regards,
Sushma.

[jpass]

I'd use a scripted input that queries your db and prints the return to the screen. Schedule this scripted input to run every 30 mins. I do this for 20 inputs and it works perfectly.

1. make sure your splunk machine can talk to your db server
2. create a perl or python script in $splunk_home$/bin/scripts. I use PERL: 'get_my_data.pl'
3. this script is very simple. I selects any new records from the database and prints them to screen
4. each time the script runs it needs to record some value that allows your script to know what is 'new' versus what is 'old' aka...already indexed.
5. All the databases I maintain have a 'auto_incremented primary key' which I use in my scripts to figure out what is 'new data' that should be indexed. My script always ends by recording the new 'max primary key id'. Then, each time it runs, it only selects records that have a primary key id greater then the previously recorded maximum id.
6. Since you're printing your output to the screen, you can format it any way you please. You can then configure Splunk to notice this format if it doesn't do so automatically (csv etc).

[linu1988]

Hello Sushma,
You need to use the Splunk app for DB Connect. You can configure your database according to the database type and then configure the savedsearch to run the queries for you using |dbquery command. You will find all the documentation on splunk site itself.

You also have the option to get the results in CSV file or xml format. But to get that in an excel file you need to use some scripting with the savedsearch result parameters.

_http://docs.splunk.com/Documentation/DBX/1.1.3/DeployDBX/Configuredatabasemonitoring

Thanks

[linu1988]

Yes it will work, then you need to format it properly. The excel file output you need to a script.

In inputs.conf you need to make a bat/cmd file entry and have a sql file as input.

[sushma6]

Rather than using Splunk app for DB Connect, if I install Splunk agent on the DB box and under inputs.conf file if I include my script location, won't that work? I dont want to use any apps