topic Re: dbquery and variables in All Apps and Add-ons

dbquery and variables

jgauthier — Wed, 08 Jan 2014 21:26:37 GMT

Can I pass any kind of variable to dbquery through splunk? It demands being the first search command: "Error in 'dbquery' command: This command must be the first command of a search."

I would like to accomplish a search in splunk that then passed data to dbquery so I can use it in the select statement. I know this can be done in a form, but I want to do it in a query that is generated a the dashboard.

Re: dbquery and variables

aelliott — Wed, 08 Jan 2014 22:46:07 GMT

Looks like you can create your variables using eval and use them within the query with dollar signs surrounding.. similar to this query

index=_internal | stats dc(source) dc(sourcetype) | rename dc(source) AS dcs dc(sourcetype) AS dct | dboutput database=mysql type=sql "INSERT INTO t1 (a, b) VALUES ($dct$, $dcs$)"

within the documentation
http://docs.splunk.com/Documentation/DBX/1.1.1/DeployDBX/Commands

Re: dbquery and variables

jgauthier — Mon, 28 Sep 2020 15:36:57 GMT

I see the article, but the examples given do not work for me. As I mentioned, I get an error if dbquery is not the first search paramter:
"Error in 'dbquery' command: This command must be the first command of a search."
So, the example in the documentation:
index=_internal | dbquery ASSET_DB "SELECT id,name, ip_address,owner,last_update FROM hosts WHERE active = 1" limit=25
Just plainly does not work.

Re: dbquery and variables

aelliott — Thu, 09 Jan 2014 14:26:10 GMT

what if you leave off the index=_internal ?

Re: dbquery and variables

jgauthier — Thu, 09 Jan 2014 17:40:51 GMT

If I removed that my queries work. (using my own database of course, not ASSET_DB).

Re: dbquery and variables

aelliott — Thu, 09 Jan 2014 17:43:36 GMT

If you were to use a dbquery command in conjunction with another search, it would be like running the dbquery command many times for each result in the search. Is this what functionality you want?

Re: dbquery and variables

linu1988 — Thu, 09 Jan 2014 18:19:01 GMT

|dbquery should be the first command, there shouldn't be any index=_internal or any other statement at all

Re: dbquery and variables

jgauthier — Thu, 09 Jan 2014 21:58:43 GMT

aelliot> No, actually, i would like to pass data to a single dbquery. In this case the command would return one value for one query.
linu1988> Are you implying their documentation is wrong in the example provided?

Re: dbquery and variables

linu1988 — Thu, 09 Jan 2014 22:04:17 GMT

Yep, pretty sure. Even if they make it right, index=_internal makes no sense at all.

Re: dbquery and variables

aelliott — Thu, 09 Jan 2014 22:32:33 GMT

The only way I can think of to use variables in a query is to use tokens within the query. What kind of dashboard are you using this with? (simplexml advancedxml, javascript, django) etc.

Re: dbquery and variables

linu1988 — Thu, 09 Jan 2014 22:41:12 GMT

if you want it to be a dashboard variable why do you want it in the search? you can have some pulldown modules or textbox then with submit button form your query to run. i dont find any difficulty in this.

|dbquery database=xxx "select $var1,$var2 from table"

$var1,$var2 can be selected from pulldown or textbox..

Re: dbquery and variables

jgauthier — Fri, 17 Jan 2014 16:26:04 GMT

I am using advanced XML, and I don't want them to select the data. That is because the data is their username. I can build customized dashboards, using custom queries from a database that uses their username (we have common usernames in apps). So a person can go to "My dashboard" and it will populate with queries that are relevant to their username, which is gathered from a splunk command.

Re: dbquery and variables

linu1988 — Mon, 28 Sep 2020 15:40:47 GMT

yes it is possible. I have done a similar thing where it will find the logged in user and log their activity. You can maintain a lookup or pass them directly to the query by finding the logged in user. Apply the intention to pass it to dbquery. This is my assumtion

this will give you the logged user.

Re: dbquery and variables

jgauthier — Fri, 17 Jan 2014 18:31:25 GMT

I can get the username. Just not sure I understand this part: Apply the intention to pass it to dbquery. That is my struggle inside a search command. (or perhaps it has to be a dashboard?)

Re: dbquery and variables

linu1988 — Fri, 17 Jan 2014 18:36:00 GMT

yes as per your previous post you mentioned you are advanced xml i.e. on dashboard right!?

Follow some examples in sideview utils/splunk UI examples how to pass the value from parent to child module. You can get the user name then pass it as a variable to your |dbquery...

Re: dbquery and variables

linu1988 — Fri, 17 Jan 2014 18:37:27 GMT

in dbx app you also have the same implementation where you choose the database and then write the query. it is kinda same what you are looking for..

Re: dbquery and variables

jgauthier — Fri, 17 Jan 2014 19:06:25 GMT

I will check out passing the values in the dashboard. thanks for the tip!

Re: dbquery and variables

arlakathena — Mon, 22 Apr 2019 15:22:21 GMT

I downvoted this post because that doesn't work 😞

Re: dbquery and variables

niketn — Mon, 22 Apr 2019 17:01:15 GMT

@arlakathena rather than down-voting a 4 year old post, I would request you to post a new question with the issue you are facing, along with other required details, so that Community members can assist you better.

As you might be aware dbquery is no longer used in db connect. With the latest version you should be using dbxquery instead.