I have successfully installed and configured DBConnect 3.11 on a search-head and I'm able to query and output data to/from an AWS RDS Aurora database.
My problem happens when I try to run a
dbxlookup against my data search results.
2017-10-15 20:01:47.139 2886@splunk-sh1 [main] INFO com.splunk.dbx.connector.logger.AuditLogger - operation= connection_name= stanza_name= state=success sql='SELECT "ip", "dns" FROM (SELECT time, ip, dns FROM `hostip_lookup`.`table_name`) dbxlookup WHERE "ip" IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,)'
I ran this query manually and I am not getting the expected results.
SELECT "ip", "dns" FROM (SELECT time, ip, dns FROM `hostip_lookup`.`table_name`) dbxlookup
I expected to see the results from my table, instead I see:
|# ip, dns |ip, dns |...<repeated>... |ip, dns |ip, dns
It appears that using double quotes for the column names (
SELECT "ip", "dns" FROM ...) is incorrect. And, instead of returning the record values, it is returning "ip" and "dns" for every record. This is generated by DBConnect and I am only able to change the query in the subselect (within the parenthesis
Taking the quotes away from the column names returns the expected results:
SELECT ip, dns FROM (SELECT time, ip, dns FROM `hostip_lookup`.`table_name`) dbxlookup
Questions to my fellow Splunk Ninjas:
1. Is DBConnect generating incorrect SQL? Or, have I configured something wrong?
2. Is there any way to not wrap my query within parentheses? Or can I change how Splunk is quoting my column names?
I have found a solution to this problem. The database must have
ANSI_QUOTES enabled in the
ANSI_QUOTES Treat " as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.
Relevant StackOverflow Post: https://stackoverflow.com/a/14123649/1440715
For AWS RDS this needs to be set within a parameter group. This cannot be set with the SQL query used on a vanilla MySQL databse. Once the parameter is set (and the group is applied) the database instance needs to be rebooted in order for this setting to take effect.
Working with DB Parameter Groups: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html
@kranzm, If your problem is resolved, please accept the answer (it's OK to accept your own answer) to help future readers.