All Apps and Add-ons

Bug: Why does the divide by operator not work in a Splunk DB Connect 2.1.3 dbxquery?

vasanthmss
Motivator

Hi Splunkers,

Recently updated with DB Connect 2.1.3 from earlier version. I have noticed that the divide by operator is not working. sample search is as below,,,,

The below search is working:

| dbxquery query="select field1, field2 from database.table" connection="XXXXX" |table *

This search is not working,

| dbxquery query="select field1, field2/10 from database.table" connection="XXXXX" |table *

The second search is not event sent to the database. I have tried with the escape "\/" but no luck. any thoughts?

Thanks,
V

V
1 Solution

vasanthmss
Motivator

Updating the Answer....

--- permanent fix.
The new release 2.2.0 fixed the issue and now its working fine 🙂 😉

---- temp solution.
Finally My Searches are running. Here is what i found....

DB Connect Versions:

DB connect started with dbquery which is depreciated lately and URL encoding introduced with dbxquery. I was using SQL statement with URL encoding, which was very hard. So I found that the upgrade 2.1.3 fixed this, Yes it does but introduced a new bug we can't use "/" any where in the SQL statement. So I have raised a Splunk Ticket as well as raised here.

Issue:

I thought URL decoding and encoding should be handled in the python script and started looking on the python script ($SPLUNK_HOME/etc/apps/splunk_app_db_connect/bin/dbxquery.py). yes the urllib library is used to do this.

Reason
I have started looking on the phyton library options,

urllib.quote(string[, safe])

Replace special characters in string using the %xx escape. Letters, digits, and the characters '_.-' are never quoted. By default, this function is intended for quoting the path section of the URL. The optional safe parameter specifies additional characters that should not be quoted — its default value is '/'.

So all the time the quote utility thinks my divide operator as path section of the URL.

Fix

Open the phyton script (take a copy before edit) and look for the string query = urllib.quote( and update this .quote(query,safe='')

Something like this query=urllib.quote(query,safe='')

So in the script urllib.quote() is been used in the following places,

  1. def encoded_query(self): - This one is for already encoded query
  2. def build_inline_view_query(self): - This one is for the wrap the search out of defult select.. like your query is select * from schema.databast.table the query fired in database looks like this SELECT * FROM (select * from schema.databast.table) t
  3. final one is the actual SQL to URL encode section the feature.

Not necessary to break / update 1, 2 functionality so touched only 3. I guess that's it. After the update the script restart the splunkd. Works as expected.

Suggestions / Questions:

It's my suggestions

  1. Phyton urllib has another function urllib.quote_plus can we use this.
  2. why can't we try with updated library urllib2 or request
  3. urllib utilized for some reasons.

Reference: https://docs.python.org/2/library/urllib.html

I guess this may be the temp fix but it would be good to hear from Splunk DB2 Connect team because next upgrade this fix will move away / need t update again. Waiting for the permanent / Best fix...

Thanks,
V

V

View solution in original post

vasanthmss
Motivator

Updating the Answer....

--- permanent fix.
The new release 2.2.0 fixed the issue and now its working fine 🙂 😉

---- temp solution.
Finally My Searches are running. Here is what i found....

DB Connect Versions:

DB connect started with dbquery which is depreciated lately and URL encoding introduced with dbxquery. I was using SQL statement with URL encoding, which was very hard. So I found that the upgrade 2.1.3 fixed this, Yes it does but introduced a new bug we can't use "/" any where in the SQL statement. So I have raised a Splunk Ticket as well as raised here.

Issue:

I thought URL decoding and encoding should be handled in the python script and started looking on the python script ($SPLUNK_HOME/etc/apps/splunk_app_db_connect/bin/dbxquery.py). yes the urllib library is used to do this.

Reason
I have started looking on the phyton library options,

urllib.quote(string[, safe])

Replace special characters in string using the %xx escape. Letters, digits, and the characters '_.-' are never quoted. By default, this function is intended for quoting the path section of the URL. The optional safe parameter specifies additional characters that should not be quoted — its default value is '/'.

So all the time the quote utility thinks my divide operator as path section of the URL.

Fix

Open the phyton script (take a copy before edit) and look for the string query = urllib.quote( and update this .quote(query,safe='')

Something like this query=urllib.quote(query,safe='')

So in the script urllib.quote() is been used in the following places,

  1. def encoded_query(self): - This one is for already encoded query
  2. def build_inline_view_query(self): - This one is for the wrap the search out of defult select.. like your query is select * from schema.databast.table the query fired in database looks like this SELECT * FROM (select * from schema.databast.table) t
  3. final one is the actual SQL to URL encode section the feature.

Not necessary to break / update 1, 2 functionality so touched only 3. I guess that's it. After the update the script restart the splunkd. Works as expected.

Suggestions / Questions:

It's my suggestions

  1. Phyton urllib has another function urllib.quote_plus can we use this.
  2. why can't we try with updated library urllib2 or request
  3. urllib utilized for some reasons.

Reference: https://docs.python.org/2/library/urllib.html

I guess this may be the temp fix but it would be good to hear from Splunk DB2 Connect team because next upgrade this fix will move away / need t update again. Waiting for the permanent / Best fix...

Thanks,
V

V

richgalloway
SplunkTrust
SplunkTrust

As a workaround until the bug is fixed, you could do the division in an eval.

| dbxquery query="select field1, field2 from database.table" connection="XXXXX" |eval field2=field2/10 | table *
---
If this reply helps you, Karma would be appreciated.
0 Karma

vasanthmss
Motivator

Its not the one I'm looking for. Its an complex query and the workaround takes load on splunk. It would be best to calculate in d.b

V
0 Karma

vasanthmss
Motivator

It's tough to write a search without /. Time zone, fields and the data has /.

V
0 Karma

somesoni2
Revered Legend

Try keeping the field2/10 in single quotes OR double quotes.

0 Karma

vasanthmss
Motivator

I tried but No luck.

V
0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...