topic Re: How to pass input variable to dbxquery in Splunk Search

How to pass input variable to dbxquery

manunairadavakk — Wed, 30 Sep 2020 01:52:31 GMT

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

Re: How to pass input variable to dbxquery

manunairadavakk — Wed, 30 Sep 2020 01:52:36 GMT

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

Re: How to pass input variable to dbxquery

manunairadavakk — Mon, 26 Aug 2019 00:12:53 GMT

Hi Experts,
Request for some help/pointers for the above issue.
Been struggling for a solution

Re: How to pass input variable to dbxquery

nyc_jason — Mon, 26 Aug 2019 05:24:11 GMT

Hello, in your |rex command, you are creating a field called EMP. Did you mean to make that variable called EMPID?

Re: How to pass input variable to dbxquery

manunairadavakk — Mon, 26 Aug 2019 06:26:01 GMT

@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

Re: How to pass input variable to dbxquery

thomasroulet — Mon, 26 Aug 2019 09:58:29 GMT

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

Re: How to pass input variable to dbxquery

manunairadavakk — Tue, 27 Aug 2019 01:39:02 GMT

@thomasroulet

Thanks a lot for answering, the above query looks perfect but for some reason, i am not getting the output for PR. EMPID is getting displayed as a field which has 9 values to it. Unfortunately, for security reasons i cant publish the results.
Somehow, PR is not present as a field.

Re: How to pass input variable to dbxquery

nyc_jason — Tue, 27 Aug 2019 04:30:06 GMT

Are you extraction PR as a field automatically? it's not in the SPL above. if you just do a search for:
splunk_server=ass index="ass_main" host=*pr
does PR show as a field? If not, you may need to extract it, just like you did with EMPID. Also, the EMPID looks to take from the beginning as there is no pattern to match before (or after), so it may be grabbing too much. Is there data, or some text around the EMPID in the records? something to tell rex where to start and end?
Please take a look here for some sample rex commands: https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Rex

Re: How to pass input variable to dbxquery

manunairadavakk — Tue, 27 Aug 2019 05:12:47 GMT

@jschogel

I am using pattern EMP[0-9]{12} as part of rex and associating it to EMPID. There are no issues here and i am getting the desired valuesof EMPID.
For all the unique EMPID received from first search, i am trying to fetch the Employer name (alias:PR) from the database dbxquery search and then basically print fields EMPID,PR
PR is not a field as part of first search, the intention is to fetch it from the database for each value of EMPID.
The problem here is i am not getting PR as a field after the query is run but getting only the EMPID.

Re: How to pass input variable to dbxquery

nyc_jason — Tue, 27 Aug 2019 05:32:14 GMT

ah, gotch. just realized that. is EMPID an int in the DB? have you tried the IN clause without single quotes around the values, just the commas to separate?

Re: How to pass input variable to dbxquery

manunairadavakk — Wed, 30 Sep 2020 01:54:10 GMT

@Anonymous

I do not think there is any issue with single quotes.

I did try to print EMPID with the below query and it is coming fine as expected.
splunk_server=ass index="ass_main" host=*pr
| rex field=_raw "(?[0-9]{12})"
| dedup EMPID
| fields EMPID
| stats values(EMPID) as EMPID
| eval EMPID = "'".mvjoin(EMPID, "','")."'"
| table EMPID

Tried the below query separate, which is giving result as expected:
| dbxquery query=\"select \"Employer Name\" PR, \"Employee Number\" EMPID FROM BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" in ($EMPID$)\" connection=\"EMP-PR\"

The issue is in the combined query where field PR is not getting displayed, but EMPID is present as a field

Re: How to pass input variable to dbxquery

thomasroulet — Tue, 27 Aug 2019 06:36:54 GMT

Hello,
What versions of Spunk, Splunk DB Connect, and Database Drivers are you using?
What database are you trying to query?

Re: How to pass input variable to dbxquery

manunairadavakk — Thu, 29 Aug 2019 12:25:50 GMT

@thomasroulet
Excuse me for the delayed response. Please find below the details:

Splunk Version:7.2.6

Splunk DB Connect Details:
App Version
3.1.3
App Build
55

Database being connected:
Netezza

Re: How to pass input variable to dbxquery

thomasroulet — Wed, 30 Sep 2020 02:00:43 GMT

I made some tests with :

Splunk 7.2.6,
DBConnect 3.1.3,
mysql-connector-java-5.1.48 driver,
Mysql 5.1,
the following dataset in mysql in a table t_champs:

id,id_suiviflux,nom_champs,valeur_champs
"1","1","champ1","valeur 1"
"2","1","champ2","valeur - 2"
"3","1","champ3","valeur'3"
"4","2","champ1","valeur ""21"
"5","2","champ2","valeur22"""
"6","2","champ3","valeur23"
"7","1","champ6","valeur6"
"8","2","champ4","valeur24"
"9","1","champ4","valeur4"
"10","1","champ5","valeur5"
the following command :

| makeresults
| eval _raw="id
1
10
"
| multikv forceheader=1
| stats values(id) as id
| eval id = mvjoin(id, ",")
| map search="| dbxquery query=\"select * from t_champs where id in ($id$)\" connection=\"EMP-PR\""

The results are correct :
id id_suiviflux nom_champs valeur_champs
1 1 champ1 valeur 1
10 1 champ5 valeur5

So it seems that is not a splunk or db connect issue, maybe the connector or/and the SQL Interpretor in natezza.
Could you try to create the same dataset in Netezza, apply the command and post your results ?

Re: How to pass input variable to dbxquery

manunairadavakk — Wed, 30 Sep 2020 02:02:18 GMT

@thomasroulet
While my team is analysing, i tried append as well which is throwing a netezza error. There is still some problem while passing the input variable to the query.

| rex field=_raw "(?[0-9]{12})"
| dedup EMPID
| fields EMPID
| stats values(EMPID) as EMPID
| eval EMPID = "'".mvjoin(EMPID, "','")."'"
| append [| dbxquery connection="EMP-PR"
query="select \"Employee Name\" PR
FROM
BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" IN ($EMPID$)"]
| table PR

Error:
[subsearch]: org.netezza.error.NzSQLException: ERROR: 'select "Employee Name" PR FROM BIA_BA_EUL."View Employee Helpdesk" WHERE "Emplyee Number" IN ($EMPID$) ANALYZE' error ^ found "$" (at char 124) expecting an identifier found a keyword

Re: How to pass input variable to dbxquery

nyc_jason — Wed, 30 Sep 2020 02:02:21 GMT

I just realized you are not passing EMPID as a variable/token, you are passing a created field. The |eval EMPID=.... is creating a field called EMPID, not a token. Try using EMPID as a field name in the creation of the query, and just concatenate it in:
...
| append [| dbxquery connection="EMP-PR"
query="select \"Employee Name\" PR
FROM
BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" IN (".EMPID.")"]

Re: How to pass input variable to dbxquery

manunairadavakk — Wed, 30 Sep 2020 02:02:24 GMT

@Anonymous

I did not fully understand your above comment. However i tried the below query and it is still not displaying me the Employer name.

Re: How to pass input variable to dbxquery

nyc_jason — Wed, 30 Sep 2020 02:02:27 GMT

from the looks of the error returned from netezza $EMPID$ is being passed in as a literal string, and not the list of employee ids. So netezza is getting:

'select "Employee Name" PR FROM BIA_BA_EUL."View Employee Helpdesk" WHERE "Employee Number" IN $EMPID$

as opposed to something like:

'select "Employee Name" PR FROM BIA_BA_EUL."View Employee Helpdesk" WHERE "Employee Number" IN ('123','456','789')

You need to check the list of EMPIDs is proper, and the full query is being created with it.

To see the empid list and query being generated, you could do something similar to this:

| rex field=_raw "(?[0-9]{12})"
| dedup EMPID
| fields EMPID
| stats values(EMPID) as EMPID
| eval EMPID = "'".mvjoin(EMPID, "','")."'"
| append [| eval query_text="select \"Employee Name\" PR
FROM
BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" IN (".EMPID.")"]
| table EMPID, query_text

This should You should show you the SQL statement that is being generated, and will be easier to see whats wrong, such as syntax

Re: How to pass input variable to dbxquery

manunairadavakk — Mon, 02 Sep 2019 04:45:49 GMT

@jschogel

Thanks for the response.
I tried the above query.

Values for EMPID was 'EMP1','EMP2','EMP3'
but however query_text is null

Re: How to pass input variable to dbxquery

thomasroulet — Mon, 02 Sep 2019 08:44:49 GMT

The "append" command will not replace $EMPID$ with the values of $EMPID$

With the "append" command, "...".EMPID."..." will replace EMPID with the values but will be interpreted by one value in a string : "EMPID1, EMPID2, EMPID3" and not 3 values.

The map command will do this replacement and will be interpreted as 3 values.