All Apps and Add-ons

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

Explorer

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 *
0 Karma
1 Solution

Builder

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"

View solution in original post

Builder

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"

View solution in original post

Explorer

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 - _*"

Contributor

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

0 Karma

Explorer

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!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!