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
+1 for best double entendre in a Splunk Answers question
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.
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.
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
Yeah I think that's the problem too Dave. Thanks
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.
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.