- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Re: How to pass input variable to dbxquery
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Experts,
I am struggling to pass inputs to my dbxquery.
My intention is to display all EMPID and Employer name by passing EMPID as the parameter to my dbxquery.
QUERY:
splunk_server=ass index="ass_main" host=*pr
| rex field=_raw "(?EMP[0-9]{12})"
| dedup EMPID
| fields EMPID
| map search="| dbxquery query=\"select \"Employer Name\" PR
FROM
BIA_BA_EUL.\"View Employee Helpdesk\" where \"Employee Number\" in $EMPID$\" connection=\"EMP-PR\"" | table EMPID,PR
ERROR:
Error in 'map' command: Unable to find saved search 'search='.
Request for any inputs please
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
map command is limited by default for 10 searches.
after map command, the results (in your case) are the results of the SQL command,
so if you want to obtain EMPID and PR, you have to select this fields in your SQL command
if EMPID is extracted through rex it would be like this :
| rex field=_raw "(?<EMPID>[0-9]{12})"
You have to check the regexp.
Finally, your request may be look like this (for netezza)
splunk_server=ass index="ass_main" host=*pr
| rex field=_raw "(?<EMPID>[0-9]{12})"
| dedup EMPID
| fields EMPID
| stats values(EMPID) as EMPID
| eval EMPID = "'".mvjoin(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\""
| fields EMPID,PR
edit :
For some databases
splunk_server=ass index="ass_main" host=*pr
| rex field=_raw "(?<EMPID>[0-9]{12})"
| dedup EMPID
| fields EMPID
| stats values(EMPID) as EMPID
| eval EMPID = "'".mvjoin(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\""
| fields EMPID,PR
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, the table names have been designed by the DBA and cannot be changed. Not sure why the behaviour is different while accessing through map and ordinary dbxquery. In map the double quotes is not being recognised.
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
OK one more try escape the backslashes in the query :
splunk_server=ass index="ass_main" host=*pr
| rex field=_raw "(?<EMPID>[0-9]{12})"
| dedup EMPID
| fields EMPID
| stats values(EMPID) as EMPID
| eval EMPID = "'".mvjoin(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\""
| fields EMPID,PR
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@thomasroulet
U r a real gem, this worked like a charm. Thanks a lot for this
U were a real helping hand.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
glad to have helped.
Don't forget to vote the answer. I updated it
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@jschogel
Thanks for the response.
I tried the above query.
Values for EMPID was 'EMP1','EMP2','EMP3'
but however query_text is null
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Experts,
Request for some help/pointers for the above issue.
Been struggling for a solution
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello, in your |rex command, you are creating a field called EMP. Did you mean to make that variable called EMPID?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@jschogel
Not sure i am not able to write the rex command properly in the above problem statement
My variable is EMPID extracted through rex.
I have to pass the field EMPID as an input to the dbxquery and fetch employers name of only those EMPID obtained from first search
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The error was removed when i changed my query to the below one. I am getting the expected number of events, but i need EMPID,PR to be displayed in a table
splunk_server=ass index="ass_main" host=*pr
| rex field=_raw "(?EMP[0-9]{12})"
| dedup EMPID
| fields EMPID
| map search=" | dbxquery query=\"select \"Employer Name\" PR
FROM
BIA_BA_EUL.\"View Employee Helpdesk\" where \"Employee Number\"='$EMPID$'\" connection=\"EMP-PR\"" | fields EMPID,PR
.conf24 | Registration Open!
ICYMI - Check out the latest releases of Splunk Edge Processor
Introducing the 2024 SplunkTrust!
