All Apps and Add-ons

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

Communicator

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:{)

1 Solution

Splunk Employee
Splunk Employee

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

View solution in original post

Splunk Employee
Splunk Employee

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

View solution in original post

Communicator

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.

0 Karma

Communicator

Thanks, I'll give it a go.

...Laurie:{)

0 Karma

Communicator

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

            ...Laurie:{)

Splunk Employee
Splunk Employee

Thanks bchoi_splunk, can you share the SPL number?

0 Karma

Explorer

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....."
0 Karma

New Member

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

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

0 Karma

Communicator

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

0 Karma

New Member

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

0 Karma

Communicator

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:{)

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!