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
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,
select * from schema.databast.table
the query fired in database looks like this SELECT * FROM (select * from schema.databast.table) t
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
urllib
has another function urllib.quote_plus
can we use this.urllib2
or request
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
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,
select * from schema.databast.table
the query fired in database looks like this SELECT * FROM (select * from schema.databast.table) t
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
urllib
has another function urllib.quote_plus
can we use this.urllib2
or request
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
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 *
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
It's tough to write a search without /. Time zone, fields and the data has /.
Try keeping the field2/10 in single quotes OR double quotes.
I tried but No luck.