Splunk Search

How to pass input variable to dbxquery

Path Finder

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

Tags (3)
1 Solution

Path Finder

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

View solution in original post

Path Finder

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

View solution in original post

Path Finder

@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.

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Path Finder

@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.

0 Karma

Splunk Employee
Splunk Employee

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?

0 Karma

Path Finder

@jschogel

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

0 Karma

Path Finder

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

0 Karma

Path Finder

@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

0 Karma

Path Finder

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 ?

0 Karma

Path Finder

@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

0 Karma

Splunk Employee
Splunk Employee

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.")"]

0 Karma

Path Finder

@jschogel

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

| 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

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Path Finder

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.

0 Karma

Path Finder

@thomasroulet

Request you to please on how the final query should look like.

0 Karma

Path Finder

I have nothing better to offer than the first answer I made :

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

Path Finder

@thomasroulet

Unfortunately that is not working for us, i confirmed with the Splunk admin team.
I was also looking at JOIN command, which is also not working

0 Karma

Path Finder

@thomasroulet

Looks like a small opening for the issue, however need your guidance.

The below query gives me a warning- Unable to run query '| dbxquery connection="EMP-PR" query="select * FROM BIA_BA_EUL.View'.:
Looks it is not recognising the double quotes in the multi word table name properly.
The database is netezza where we give table name as BIA_BA_EUL."View Employee Helpdesk"

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, "','")."'"
| map search="| dbxquery connection="EMP-PR" query=\"select * FROM BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" in ($EMPID$)\" "

When i change the query to below:
[map]: org.netezza.error.NzSQLException: ERROR: 'select * FROM BIA_BA_EUL.'View Employee Helpdesk' WHERE 'Employee Number' IN ('EMP1','EMP2','EMP3') ANALYZE' error ^ found "'" expecting an identifier found a keyword
However, it looks like Employee Number is being properly passed to the query as input.
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, "','")."'"
| map search="| dbxquery connection="EMP-PR" query=\"select * FROM BIA_BA_EUL.'View Employee Helpdesk' WHERE 'Employee Number' in ($EMPID$)\" "

0 Karma

Path Finder

@thomasroulet

Just curious if you have any pointers for the above issue.

0 Karma

Path Finder

Ok it is not a good idea to use blanks in a table name or a field...
I updated my first answer with a response to this issue.

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

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