Splunk DB Connect 2: Why is my SQL count(*) query not working with error "Unrecognized or ambiguous rising column name [mycol] is used."?

[snehalk]

Hello All,

I am trying to pull the Oracle table into the Splunk. Below is my SQL query:

select count(*) from mytable {{where $rising_column$ > ?}}  

And am giving the Primary ID column name to rising_column field, but am getting the below error:

Unrecognized or ambiguous rising column name [mycol] is used. For Oracle database, qualified column name cannot be used, use "<qualified name> (<alias>)" instead.

Could you please help me on this?

[vasanthmss]

Hi,

I think you are trying to pass the Splunk token into SQL select statement, If so the below will help you.

|dbxquery [|stats c | eval query="select count(*) from mytable where fieldname="+$rising_column$ | return query] connection="<connection>" | table *

You can create the query string in a sub search and pass it to the dbx query. Hope this will helps you..

Thanks,
V

[joao_amorim]

No, he is comparing the $rising_column$ field with the last value referred by ?

Did you tested what you said?

[vasanthmss]

I got it,...

Check the below links, Seems like this feature was working earlier .. Suggest to raise Support ticket with Splunk

https://answers.splunk.com/answers/68699/database-table-doesnt-have-rising-column-totally.html
https://answers.splunk.com/answers/98012/dbx-combining-where-in-custom-sql-with-rising-column.html
https://answers.splunk.com/answers/142442/db-connect-rising-column-combination-of-two-columns.html

[joao_amorim]

Ok thanks for the replies.

But here we are talking about DB Con v2 and not DB Con v1.

Those links are from 2012 and related to DB Con v1, I'm migrating from DB Con v1 to v2.

In v1 I don't have any problems with the statement {{where $rising_column$ > ?}}, only when I'm trying to migrate the Data Inputs to v2.

Regards 🙂

[joao_amorim]

Any news on this topic?

Regards

[jkat54]

I think $rising_column$ should be a non-ambiguous column name

Something like column.Name > ?