Splunk DB Connect: How to configure the checkpoint value?

[templier]

Hello all.
Have a trouble with configure DB inputs with using Checkpoint column.
Maybe you can solve my trouble?

SQL:

SELECT * FROM "myDB"."dbo"."myDBAuditLog" where [RecordId] BETWEEN (select max([RecordId]) from "myDB"."dbo"."muDBAuditLog") - 10 AND (select max([RecordId]) from "myDB"."dbo"."myDBAuditLog")

In a result, i have column RecordId and i want using it for download to Splunk only new strings.
But i can't understand what i must write in Checkpoint Value?

Thanks.

[woodcock]

When using the checkpoint option, you may need to use an SQL ORDER BY clause because even though the database may be being written with a rising column, the data may not be so ordered in the database. Each time DBConnect queries the database without the ORDER BY clause it can cause the checkpoint value to be updated with whatever value was at the end of the column (which may or may not have been in rising order).

[vsilchev · Accepted solution]

As I understand, "RecordId" increases with every new record. If it is so, then you can use "Rising Column" input type. When creating/editing DB Input, on "Choose and Preview Table" step you should set Input Type to "Rising Column". Then you can choose "Automatic Mode" and just set the appropriate table or switch to editor and use the following SQL:

SELECT * FROM "myDB"."dbo"."myDBAuditLog" WHERE RecordId > ? ORDER BY RecordId ASC

Finally, don't forget to choose Rising Column ("RecordId" in your case). Then you can leave "Checkpoint Value" field empty if you want DBConnect to load all the records during the first request. Otherwise you can specify the Checkpoint Value to start with, so DBConnect will load only those records, which "RecordId" is greater then the Checkpoint Value.

After that, every time DBConnect will use the Checkpoint Value for requesting only new records and update it using the greatest RecordId received from database.

[templier]

Have a trouble:
In Editor Mode - > Rising Column

 SELECT * FROM "myDB"."dbo"."myDBAuditLog" WHERE RecordId > ? ORDER BY RecordId ASC

I have an error:
External search command 'dbxquery' returned error code 1. Script output = "RuntimeError: Failed to run query: "SELECT FROM (SELECT FROM "myDB"."dbo"."myDBAuditLog" WHERE RecordId > ? ORDER BY RecordId ASC) t", params: "None", caused by: Exception(' java.sql.SQLException: Parameter #1 has not been set..',). "

[templier]

UDP: Work!

[templier]

Thanks, now i understand.
The only thing I can check only on Monday.

[muebel]

Hi templier, You don't necessarily have to specify an initial checkpoint value. Splunk will get as many records as it can, and then whatever the rising column is, it will take the latest value and set that as the checkpoint.

If you want to set a starting checkpoint, then you can specify that latest value manually. Behind the scenes Splunk will modify the query to swap in a WHERE clause to return only records whose RecordId is great than the checkpoint.

This is slightly different depending on which version of DB Connect you are using, but this is the general idea.

Please let me know if this answers your question!