I have an IDM DB in my organization that is connected to Splunk by DB Connect app.
The DB holds data about workflows in the system, their status (in process, completed, etc..), request ID and so on..
Today I noticed that the data inputs are added when the system has new requests, but when a request gets updated in the system (for example the status turns from in process to completed), the data does not change in Splunk.
So the status in my DB is completed, but in Splunk, it's still in process.
I tried running the SQL query again through the DATA LAB INPUTS, Splunk tells me that everything is updated.
Would love to get some help,
This will happen if the DB replaces a field in an existing record without changing the rising column value. Maybe you just have the wrong rising column. What are you using for the rising column field and does it change when a row is updated?
The data I have includes info about requests.
That means that I have 3 main values:
1. requestId (the ID of the request in the system)
2.status (completed, failed, in_process...)
3.statusUpdate (the date that the status changed)
So I have one row per every requestId. The input of the status and statusUpdate update according to the actions taken in each request, or creates new rows when new requests are made.
The update of each request does not create a new row, it updates the input in the existing row of the relevant requestId.
the input type is rising, and the rising column I picked is statusUpdate (which again, creates new rows only in new events, but only updates in existing ones).
Since you have a good candidate for a rising column (statusUpdate) you next need to ensure it's used in your query. The query should look something like
SELECT * FROM foo WHERE statusUpdate > bar ORDER BY statusUpdate. This will ensure the rows are returned in the order in which they were updated and then Splunk will index the changes.
When using a rising column, Splunk expects the data in that column to be updated when rows are updated.
If your database does not update the statusUpdate with a new (more recent) date when the row is changed, Splunk will not be able to identify the row has changed.
In that case, you will need to have the database logic amended so that statusUpdate IS modified, or you will not be able to use rising column - instead you may need to revert to a batch process.
Check that statusUpdate is a valid date, and that DBX is correctly configured to interpret it and it has a valid checkpoint.