All Apps and Add-ons

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

luislema
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.

1 Solution

nabeel652
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

nabeel652
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

luislema
Path Finder

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.

0 Karma

nabeel652
Builder

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.

0 Karma

luislema
Path Finder

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

0 Karma

nabeel652
Builder

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

0 Karma

luislema
Path Finder

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

0 Karma

thompsonsgg
New Member

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

0 Karma

nabeel652
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

luislema
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

nabeel652
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

luislema
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

luislema
Path Finder

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

0 Karma

luislema
Path Finder

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

0 Karma

luislema
Path Finder

No, I am currently contacting Splunk support.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!