All Apps and Add-ons

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

laurie_gellatly
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

bchoi_splunk
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

bchoi_splunk
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

joao_amorim
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

laurie_gellatly
Communicator

Thanks, I'll give it a go.

...Laurie:{)

0 Karma

laurie_gellatly
Communicator

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

            ...Laurie:{)

sylim_splunk
Splunk Employee
Splunk Employee

Thanks bchoi_splunk, can you share the SPL number?

0 Karma

lbenjaminvoigt
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

duckdiver
New Member

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

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

0 Karma

laurie_gellatly
Communicator

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

0 Karma

duckdiver
New Member

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

0 Karma

laurie_gellatly
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...