Splunk DB Connect 2: How to pass a Splunk username in my dbxquery?

[nimeshkakadiya]

I am trying to run a query with variables using the dbxquery command. I want to pass Splunk username in my query. I have tried following, but was not successful:

|rest /services/authentication/current-context | search username!="splunk-system-user" | table username 
| eval user=username 
| eval searchquery="\"SELECT * FROM PERSONS WHERE USERNAME='".user."'\"" 
|search [|dbxquery connection=dev_all_privs query=searchquery shortnames=true|  fields - _*| return ID, FIRST_NAME]| fields *

[ryanoconnor · Accepted solution]

Have you tried using the map command instead?
http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Map

You might have better luck with something like:

|rest /services/authentication/current-context | search username!="splunk-system-user" | table username 
 | eval user=username 
 | eval searchquery="\"SELECT * FROM PERSONS WHERE USERNAME='".user."'\"" 
 |map search="|dbxquery connection=dev_all_privs query=$searchquery$ shortnames=true|  fields - _*| return ID, FIRST_NAME"

[nimeshkakadiya]

Thank you Ryan!

Just want to make a note here that following query worked for me.

|rest /services/authentication/current-context  | search username!="splunk-system-user"
| eval user=username
| eval searchquery="SELECT * FROM PERSONS WHERE USERNAME='".user."'"
| map search="|dbxquery connection=dev_all_privs query=$searchquery$ shortnames=true| fields - _*"

[davebrooking]

What version of DBConnect 2 are you using? Take a look at this Answer where spaces in the SQL caused a problem, could that be the cause?

Dave

[nimeshkakadiya]

Dave,

I am using Version 2.2.0.

Related to the link you mentioned, I don't think that encoded version is required with this version because my following query works just fine. I am not sure if there are other syntax errors with my previous query.

|dbxquery connection=dev_all_privs query="SELECT * FROM PERSONS WHERE USERNAME='JOSEPH'" shortnames=true | fields - _*

Thank you!