All Apps and Add-ons
Highlighted

Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Path Finder

I am using Splunk 6.5.1 and the DB Connect App to index data from SQL Server databases. I use the rising column on a primary key so the indexer will only load new records. The first time it runs fine, but in subsequent runs, the indexer is not loading all the columns. It usually skips 1-2 records when it runs, some runs don't skip any records though. I've tried it with a couple of databases on different servers and had the same result. I also tried the Advanced Input Type with a Checkpoint value, and it loads duplicate records. I am not sure what the issue is. I've used Splunk DB Connect 2 before and I've never had any issues like this.

Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

New Member

Hi, have you had any luck with this? I am having the same issue.

0 Karma
Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Path Finder

No, I am currently contacting Splunk support.

0 Karma
Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Path Finder

If you are having the same issue you should up vote my post.

0 Karma
Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Builder

is the rising column on an auto-increment field? If not so, there is a chance that the records don't come in order and DB Connect will ignore the events with value less than the rising column value it last saw.

You can check it in /opt/splunk/var/lib/persistantstorage/dbx//state.xml file what value it has stored last. you need to find the folder relevant to your query as the names are just given in form of random numbers.

0 Karma
Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Path Finder

Yes, the rising column is a Primary Key Identity column. You can also see the Checkpoint Value if you go to the DB Connect app, then Operations -> DB Inputs, click on your Input, then go to step 2 of 4 "Choose and Preview Table". At the bottom of that step it will show the Checkpoint Value.

0 Karma
Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Builder

Is it auto-increment or the getting its value from some other means? Just asking because I also had same problem and found out that the database was somehow not saving records in order

Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Path Finder

Yes, it's an identity column so it auto increments.

0 Karma
Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Path Finder

It seems you were right. It was because the rising column was not an identity column. I got confused because I was trying on 2 different databases, one had the identity column but it was a secondary replica so the data was being synchronized from the primary server. This is where I originally had the issue and assumed it was because of the replication. Therefore, I did the same test on another database without replication but I didn't set the primary key as an identity. After you commented I set it up as identity and so far it has been running fine without skipping any records. Can you add an answer so I can select it as a solution?

0 Karma
Highlighted

Re: Why is Splunk DB Connect not indexing all the records when using the Rising Column Input Type?

Builder

is the rising column on an auto-increment field? If not so, there is a chance that the records don't come in order and DB Connect will ignore the events with value less than the rising column value it last saw.

You can check it in /opt/splunk/var/lib/persistantstorage/dbx//state.xml file what value it has stored last. you need to find the folder relevant to your query as the names are just given in form of random numbers.

View solution in original post