Splunk DB Connect: Why am I getting error "Invalid column index" trying to create a database input with a rising column SQL query?

[harshal_chakran]

Hi,

I have already made the database connection.
What I am trying to make is a Database input. Following is my SQL Query:

 SELECT * FROM MY_FILE {{ WHERE $rising_column$  IN (19123136031)}}

Here "MY_FILE" is my table and "FILE_ID" as my rising column.

But I am getting an error. When looked into the Splunkd.log, I found the following text msg:

ERROR AdminManagerExternal - Received malformed XML from external handler:\nFailed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://librarydb.catalog/T_FILE: Invalid column index with query = SELECT * FROM MY_FILE WHERE FILE_ID IN (1927016031)\r\r\nfalse<class 'spp.java.bridge.JavaBridgeError'>Command com.splunk.dbx.monitor.DatabaseMonitorValidator returned status code 17

What I am doing wrong here?

Kindly Help

[richgalloway]

Having a fixed value in your rising_column clause defeats the purpose of a rising column, which is to fetch rows which have been added/changed since the last fetch. The fixed value in the where clause means the same rows will be returned by every query. May I suggest the following?

SELECT * FROM MY_FILE {{ WHERE $rising_column$  > ?}} ORDER BY FILE_ID

This assumes FILE_ID values are always increasing.