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

[zeophlite]

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?

[Richfez · Accepted solution]

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]

Is this working?

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