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

[luislema]

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.

[nabeel652 · Accepted solution]

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.

[luislema]

Yes, for the rising column to work the data can't be on a secondary replica, and the rising column needs to be an identity column.

[nabeel652]

Best practice I have seen so far it to make timestamp column your rising column. This will eliminate the chances of skipping events due to not matching rising column condition.

[luislema]

Yes, unless you have 2 events with the same timestamp.

[nabeel652]

It is the time row was added. It is guaranteed to be unique

[luislema]

Right, timestamp data type is unique. I was getting it confused with the datetime data type.

[thompsonsgg]

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

[nabeel652]

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.

[luislema]

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.

[nabeel652]

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

[luislema]

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?

[luislema]

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

[luislema]

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

[luislema]

No, I am currently contacting Splunk support.