Splunk Search

Issue with database table name with spaces in map dbxquery search

manunairadavakk
Path Finder

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

Tags (3)
0 Karma

thomasroulet
Path Finder

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\"" 

manunairadavakk
Path Finder

@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

0 Karma

thomasroulet
Path Finder

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\"" 
0 Karma

Richfez
SplunkTrust
SplunkTrust

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!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...