All Apps and Add-ons

dbquery and variables

jgauthier
Contributor

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.

Tags (1)
0 Karma

aelliott
Motivator

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

0 Karma

jgauthier
Contributor

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

0 Karma

linu1988
Champion

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

0 Karma

linu1988
Champion

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

0 Karma

jgauthier
Contributor

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?)

0 Karma

linu1988
Champion

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

|rest /services/authentication/users splunk_server=local | search [| rest /services/authentication/current-context splunk_server=local | rename username as title | fields title]

this will give you the logged user.

0 Karma

jgauthier
Contributor

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.

0 Karma

linu1988
Champion

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

0 Karma

arlakathena
Explorer

I downvoted this post because that doesn't work 😞

0 Karma

niketnilay
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

aelliott
Motivator

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.

0 Karma

linu1988
Champion

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

0 Karma

jgauthier
Contributor

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?

0 Karma

linu1988
Champion

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

0 Karma

aelliott
Motivator

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?

jgauthier
Contributor

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

0 Karma

aelliott
Motivator

what if you leave off the index=_internal ?

0 Karma

jgauthier
Contributor

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.

0 Karma