topic Re: Issue with database table name with spaces in map dbxquery search in Splunk Search

Issue with database table name with spaces in map dbxquery search

manunairadavakk — Wed, 30 Sep 2020 02:03:06 GMT

Hi Splunk experts,

Please help on the below issue.
When i am running a query directly with dbxquery, the table name with spaces(View Employee Helpdesk) and column name with spaces is not creating any issues. I am getting results as expected.

| dbxquery query="select \"Employee Number\",\"Manager\"
FROM
BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\"=('EMP1')" connection="EMP-PR1"

Whereas, when i am using the same table and cloumn name in map search with dbxquery it is not being recognised and throwing error.

.........
| fields EMPID
| map search="| dbxquery query=\"select \"Employee Number\"
FROM BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" IN ($EMPID$)\"
connection="EMP-PR1""

Error being thrown:
[map]: org.netezza.error.NzSQLException: ERROR: 'select FROM BIA_BA_EUL.View ANALYZE' error ^ found "FROM" (at char 9) expecting an identifier found a keyword

Re: Issue with database table name with spaces in map dbxquery search

Richfez — Wed, 04 Sep 2019 14:37:43 GMT

I'm still thinking how to resolve this, but if it helps anyone (or helps you), I think the problem is that when you wrap that in map, you are effectively removing one layer of escaping.

| map search="| dbxquery query=\"select \"Employee Number\"
FROM BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" IN ($EMPID$)\"

turns into

dbxquery query="select "Employee Number"
FROM BIA_BA_EUL."View Employee Helpdesk" WHERE "Employee Number" IN ($EMPID$)"

Is there a reason you can't use dbxlookup instead of map+dbxquery? It should not require the double-escaping shenanigans, and bonus it should be far faster too!

Re: Issue with database table name with spaces in map dbxquery search

thomasroulet — Wed, 04 Sep 2019 14:40:14 GMT

Do not surround the field name or table name with double quotes but with backticks

   | fields EMPID
   | map search="| dbxquery query=\"select `Employer Name` PR, `Employee Number` EMPID FROM BIA_BA_EUL.`View Employee Helpdesk` WHERE `Employee Number` in ($EMPID$)\" connection=\"EMP-PR\""

Re: Issue with database table name with spaces in map dbxquery search

manunairadavakk — Wed, 30 Sep 2020 02:03:34 GMT

@thomasroulet

This will not work because netezza does not recognise backticks, it recognises only double quotes.But somehow in map search it is not being recognised while it is being recognised in ordinary dbxquery.
Getting error as below:

[map]: org.netezza.error.NzSQLException: ERROR: 'select Employee Number FROM BIA_BA_EUL.View Employee Helpdesk WHERE Employee Number IN ('EMP1','EMP2') ANALYZE' error ^ found "`" (at char 24) expecting a keyword

Re: Issue with database table name with spaces in map dbxquery search

thomasroulet — Thu, 05 Sep 2019 06:59:26 GMT

Ok, escape the backslashes in the query

    | fields EMPID
    | map search="| dbxquery query=\"select \\\"Employer Name\\\" PR,  \\\"Employee Number\\\" EMPID FROM BIA_BA_EUL.\\\"View Employee Helpdesk\\\" WHERE \\\"Employee Number\\\" in ($EMPID$)\" connection=\"EMP-PR\""