All Apps and Add-ons

What's wrong with my rising column?

jwch
Explorer

I have a db input based on a sql query with a rising column but the results don't seem to be coming into Splunk. My rising column is a coalesce statement acting as a proxy for a last updated field and I think have everything configured correctly but I'm missing something. I parse the query and it pulls the desired rows and switching the query to a batch input also works, but that isn't quite what I want. Any ideas?

Example SQL:

SELECT A.ID, B.RC FROM TABLE A JOIN (SELECT ID, COALESCE(EndDate, StartDate) FROM TABLE) B ON A.ID = B.ID AND B.RC > {{RelevantDate}}

Parameters:
Type= Rising Column
Max Rows= 10,000
Specify Rising COlumn= B.RC
Timestamp = B.RC
Specify Timestramp=B.RC
ExecutionFrequency=60

jplumsdaine22
Influencer

+1 for best double entendre in a Splunk Answers question

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Which version of DB Connect are you using?

The SELECT statement looks a little off for DB Connect v1. It's usually something like this:

SELECT A.ID, B.RC FROM TABLE A JOIN (SELECT ID, COALESCE(EndDate, StartDate) FROM TABLE) B ON A.ID = B.ID {{AND B.RC > ?}}

DB Connect automatically replaces the '?' with the value last seen for the rising column. On the first call, when there is no previous value, the text between double braces is omitted so it's important the statement be syntactically correct without that text.

---
If this reply helps you, Karma would be appreciated.
0 Karma

jwch
Explorer

Hey thanks for the reply! I'm using dbconnect v2.

Are the "{{...}}" characters necessary? After some reading I tried to include them in my original query, but the query parser kept kicking up a fuss and not allowing the query to run with those characters included in either the where clause or the join statement.

0 Karma

davebrooking
Contributor

I don't think DB Connect 2 uses the rising column substitution in the same way as DB Connect 1 did. I think your SQL query should just be

SELECT A.ID, B.RC FROM TABLE A JOIN (SELECT ID, COALESCE(EndDate, StartDate) AS RC FROM TABLE) B ON A.ID = B.ID

I've added an alias for the COALESCE in table B, I'm not sure if that's what you originally intended.

Dave

0 Karma

joao_amorim
Communicator

Yeah I think that's the problem too Dave. Thanks

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I don't have much experience with DB Connect v2. It should be very similar to v1 so I'll answer from that perspective.
The double braces are required to use the rising column feature. However, they cannot be used in an interactive query. When testing your query interactively, you must remove the double braces, replace '$rising_column$' with the correct column name, and replace '?' with an appropriate value for your rising column.

---
If this reply helps you, Karma would be appreciated.
0 Karma

joao_amorim
Communicator

The problem is that you can't modify the Data Input with the {{AND $rising_column$ > ?}} in DB Connect v2 via GUI cause it requires you to run to search to save it.
And since it then gives you a error it doesn't let you save it.

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 ...