All Apps and Add-ons

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

nimeshkakadiya
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

ryanoconnor
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

ryanoconnor
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"

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

davebrooking
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

nimeshkakadiya
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...