All Apps and Add-ons

Splunk DB Connect 2: How can I get Splunk to reflect the current status of a regularly updated MySQL database versus the original indexed version?

adacpt
Explorer

I have a DB Connect 2 MySQL database that gets updated on a regular basis. How can I get Splunk to reflect the current status of the database versus the originally indexed version of the database?

0 Karma
1 Solution

Jeremiah
Motivator

If you are indexing data from your database into Splunk, you need to set a rising column that Splunk can use to find new data in your database. Your other option is to re-index the entire database periodically. Either way, you then run the input on a schedule. There's more detail in the link below.

From the docs:

Batch Input: A batch input is an input that invokes the same query each time and returns all results.

Rising Column: A rising column input finds the new records you want and returns only those records with each query. When you create a rising column input type, you must specify the rising column. You can specify as rising column any column whose value increases >over time, such as a timestamp or sequential ID. For example, a rising column could be last_update, employee_id, customer_id, transaction_id and so on. The rising column is how DB Connect keeps track of what records are new.

http://docs.splunk.com/Documentation/DBX/2.2.0/DeployDBX/Createandmanagedatabaseinputs

View solution in original post

0 Karma

Dan
Splunk Employee
Splunk Employee

Splunk's datastore is a poor place to synchronously replicate the state of a database table. That's because once an event is indexed in Splunk it cannot be updated. One possible solution is to take snapshots of the table using a batch input. You can then refer to the relevant snapshot in your searches. If it's just the most recent snapshot you need, consider the lookup or query functions of DB Connect to access the current state of the table.

Can you explain what you're trying to do at a little higher level?

0 Karma

Jeremiah
Motivator

If you are indexing data from your database into Splunk, you need to set a rising column that Splunk can use to find new data in your database. Your other option is to re-index the entire database periodically. Either way, you then run the input on a schedule. There's more detail in the link below.

From the docs:

Batch Input: A batch input is an input that invokes the same query each time and returns all results.

Rising Column: A rising column input finds the new records you want and returns only those records with each query. When you create a rising column input type, you must specify the rising column. You can specify as rising column any column whose value increases >over time, such as a timestamp or sequential ID. For example, a rising column could be last_update, employee_id, customer_id, transaction_id and so on. The rising column is how DB Connect keeps track of what records are new.

http://docs.splunk.com/Documentation/DBX/2.2.0/DeployDBX/Createandmanagedatabaseinputs

0 Karma

adacpt
Explorer

Thanks for the response. I am already using the Rising Column approach, but my issue is that this approach only looks for new records to index. Once a record has been indexed, I'm trying to figure out how to change it if a field within the corresponding record in the database gets updated by the user. For example, let's say the Rising Column was tracked to 'Invoice_ID' and there is a field within each record called 'Invoice_Number'. If someone makes a mistake on the 'Invoice_Number' entry and later corrects the mistake through a database update function, how can I get Splunk to recognize that the data within the indexed record has been changed, even though the 'Invoice_ID' is still the same for that changed record?

0 Karma

Jeremiah
Motivator

Ah. Yes that is a problem. That really would only work if you also have an "updated" column with a timestamp value in it, then you could track that instead.

0 Karma

adacpt
Explorer

Yeah, it's a tricky one. So... is there any solution for this situation. It seems to me that databases get updated all of the time, right? There should be some way of accounting for changed field values, right?

0 Karma

Jeremiah
Motivator
0 Karma

Jeremiah
Motivator

Also if you are willing to explore something beyond dbconnect, there is the mysql app. I'm not sure if it includes a binary log reader or not, but worth a look.

http://dev.mysql.com/doc/refman/5.7/en/binary-log.html

https://splunkbase.splunk.com/app/2848/

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...