Splunk DB Connect 2: How do I enter a WHERE clause with a rising_column in the GUI?

[laurie_gellatly]

From the GUI (DB Input), if I enable 'Advanced Query Mode' and then enter a SELECT x FROM y WHERE z, the query works fine.
If in step 3 out of 4 I also add a rising column of 'Id', then when Splunk runs the query, it produces a query like:

SELECT x FROM y WHERE z WHERE \"Id\" > ? ORDER By \"Id\" ASC 

This results in an error message:

Incorrect syntax near the keyword 'WHERE'.

Because it has 2 'WHERE's.
I also tried SELECT x FROM y {WHERE z} as well as SELECT x FROM y {{WHERE z}}, but the GUI complains about both of them.

What have I missed to be able to enter a WHERE clause with a rising_column please?

Thanks ...Laurie:{)

[bchoi_splunk · Accepted solution]

We are aware of this problem. The patch will be applied to a future version. In the meantime, please wrap your query in the following way as a workaround:

SELECT * FROM (SELECT * FROM yourtable WHERE yourcondition) AS temp

[joao_amorim]

But that doesn't solve the problem, because in DB con v1 i used {{ AND $rising_column$ > ? }} in the middle of my query and now i can't do that in DB con v2.
That's not a good solution, since it adds a WHERE at the end of the query when i want to use you explicitly $rising_column$ > ? in the middle of it.

[laurie_gellatly]

Thanks, I'll give it a go.

...Laurie:{)

[laurie_gellatly]

The workaround did the trick. Many thanks.
Look forward to seeing the fix come out.

            ...Laurie:{)

[sylim_splunk]

Thanks bchoi_splunk, can you share the SPL number?

[lbenjaminvoigt]

Did you try replacing the > with &gt? I don't think that would be the issue but it doesn't hurt to try.

Other than that, I'd try:

"SELECT x FROM y WHERE z AND 'Id' > ? ORDER BY....."

[duckdiver]

do I just need to put my SQL Statement inside the brackets?

SELECT * FROM (<"mySQL Statement">) AS temp

[laurie_gellatly]

Yep, that's all 🙂
Give it a whirl..

[duckdiver]

Thanks a lot!
I got the same case and really was wondering already if there is a BUG !!

[laurie_gellatly]

Hi,
Thanks for trying to help, but no, the '>' wasn't the issue. The GUI happily accepted the '>' and returned values.
This also shows that the search test (to go from step 2 to 3) should be redone if the user selects a rising column at step 3.

The fact that Splunk modifies the query and adds an additional 'WHERE' to the query is the problem!

To me, it looks like Splunk needs to recognise that the user has already entered a 'WHERE' and needs to update that part of the query to add in the rising column condition rather than just blindly tacking on a 'where $rising_column$ >?' at the end of the user query.
If the user already has a 'where' then splunk would need to add 'AND $rising_column$ > ?' into the 'where' instead.

Looks like a bug in the GUI.

...Laurie:{)