Splunk DB Connect: How to pull data from sql server to Splunk indexer?

[raghu0463]

Hi,

i want to pull data from sql server to Splunk indexer using Splunk DB Connect input
for the first time i want to pull all the data at once and from next day i just want to pull only the updated rows to Splunk indexer.

what are the parameters in need to set in Splunk DB Connect input?

Thanks

[raghu0463]

@niketniley:

i'm trying to use db lookup so that i can query on it for the updated data, as i was unable to pull only the updated using db input. and i'm not trying to load the data to splunk. as i have already done a batch input for one time.

What is the difference between db lookup collect and normal db input ? i never heard of db lookup collect.

Thanks

[niketn]

Do you have updated timestamp captured in your table? How frequently do you have to put updated rows to Splunk? Can it be daily once or something more frequent?

Based on the frequency you need to index updated data to Splunk, you can actually setup your batch input itself to run repeatedly (by providing time in seconds or cron schedule during DB input setup). You would however need to ensure that your DB Query for DB Input pulls only updated record based on the updated timestamp field.

Scheduling a DB Lookup and then using Splunk's collect command would be a roundabout way for the same. So you can try DB Input with above strategy.

[raghu0463]

I don't have a time stamp column, which updates for every insertion or updation in the data.

Thanks
Raghu

[woodcock]

Do a manual pull once and then schedule a daily input with an appropriate (non-time) rising field.

[raghu0463]

if my primary key is combination of 3 columns then, what should i give in the raising column pls ?

Thanks

[niketn]

@raghu0463, I don't think Rising Column allows selection of more than one field from the table.

You will have to try some other option like:

Option 1) Scripted input to write your own Script to execute a DB stored procedure to fetch only the desired rows per day. One Scripted input example is present in Splunk documentatino: https://docs.splunk.com/Documentation/Splunk/latest/AdvancedDev/ScriptExample
PS: The option will not use DB Connect app at all.

Option 2) If you are on DB Connect 3, you can execute Stored Procedure to fetch only the required rows using dbxquery (for the first time this can be used to fetch bulk results). And then use collect command to push to required index with needed metadata (i.e. sourcetype). Second time onward the query can be scheduled (if required call a separate Stored Proc).

Option 3) If the table fields support, create a Batch DB Input with a query that fetches only the required results like WHERE modified_timestamp=GETDATE()-1 in SQL Server will fetch all results that were modified a day before.

Having said this if DB Input supported multiple fields (composite key) as Rising Column that would be a great feature. As per another thread this enhancement has already been filed: enhancement request (DBX-564)

https://answers.splunk.com/answers/142442/db-connect-rising-column-combination-of-two-columns.html

[raghu0463]

can i use db lookup as an alternative for this ???

[niketn]

dblookup can also be used in conjunction with collect command to write to index. However, if you do not have rising column your query should ensure that duplicates are not inserted whether you use Batch Input or DB Lookup with collect.

As you might be aware, only using the DB Lookup will not write to Splunk index.