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

[vasanthmss]

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

[vasanthmss · Accepted solution]

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

[richgalloway]

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 *

[vasanthmss]

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

[vasanthmss]

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

[somesoni2]

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

[vasanthmss]

I tried but No luck.