All Apps and Add-ons

Splunk DB Connect 2: How to generate a new event each time a field is updated in a database table?

zeophlite
New Member

I'm using Splunk DB Connect 2, and have a table with 100 devices with location_lat, location_long, location_updated fields (this is a legacy database, so I can't change the schema). How can I create a new event each time the location_updated field is changed? I assume this can be done using a rising column?

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

Yes, you are right.

All you need to do is create a DB input and specify the rising column as "location_updated".

The SQL query it runs will be something like (stolen from 1.2 Docs, but it's good enough for this)

SELECT * FROM my_table {{WHERE $rising_column$ > ?}}

What that means is the FIRST time through the table - the initial read of the table, that is - it'll skip all the stuff in the {{ ... }} brackets, basically running a wide-open query with no WHERE clause at all. It will record the "highest" value of your rising_column, but otherwise ignores it.

The second and subsequent times it runs, it'll append to the end of the query the stuff in the brackets. So if the first run had as the latest value of rising_column a "73" or "2016/02/15 15:00:00", then when it next runs it'll be...

SELECT * FROM my_table {{WHERE $rising_column$ > 73}}

or

SELECT * FROM my_table {{WHERE $rising_column$ > '2016/02/15 15:00:00'}}

Thus only grabbing new values.

Now, you might have to wrap dates with a date construct, but I've found often it'll figure it out. Let us know if you have problems with that, though!

View solution in original post

Richfez
SplunkTrust
SplunkTrust

Yes, you are right.

All you need to do is create a DB input and specify the rising column as "location_updated".

The SQL query it runs will be something like (stolen from 1.2 Docs, but it's good enough for this)

SELECT * FROM my_table {{WHERE $rising_column$ > ?}}

What that means is the FIRST time through the table - the initial read of the table, that is - it'll skip all the stuff in the {{ ... }} brackets, basically running a wide-open query with no WHERE clause at all. It will record the "highest" value of your rising_column, but otherwise ignores it.

The second and subsequent times it runs, it'll append to the end of the query the stuff in the brackets. So if the first run had as the latest value of rising_column a "73" or "2016/02/15 15:00:00", then when it next runs it'll be...

SELECT * FROM my_table {{WHERE $rising_column$ > 73}}

or

SELECT * FROM my_table {{WHERE $rising_column$ > '2016/02/15 15:00:00'}}

Thus only grabbing new values.

Now, you might have to wrap dates with a date construct, but I've found often it'll figure it out. Let us know if you have problems with that, though!

joao_amorim
Communicator

Is this working?

Because my {{WHERE $rising_column$ > ?}} clause is always giving an error at DB Con v2.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...