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!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...