how can we add a condition of selection in sql query using db_connect and field input

[otman01]

hi every one,
how can we add a condition of selection in sql query using db_connect .

what I want to do is to create a input field to give the user the opprtunity to get just data correspending to one automaker (the user will enter his choice in the text field) . below the data that I have in my splunk server

model=focus;year=2000;automaker=ford;country=usa
model=cmax;year=2008;automaker=citroen;country=usa
model=logan;year=2008;automaker=dacia;country=morroco
model=lodgy;year=2008;automaker=dacia;country=morroco
model=golf;year=2008;automaker=volswagen;country=Germany
model=polo;year=2008;automaker=volswagen;country=Germany
model=a4;year=2008;automaker=audi;country=Germany
model=a5;year=2008;automaker=audi;country=Germany
model=a3;year=2008;automaker=audi;country=Germany
model=a3;year=2000;automaker=audi;country=Germany

[otman01 · Accepted solution]

this is the right answer :
| dbquery "db_ventes" "SELECT model, year, automaker, pays FROM cars c, automaker a where c.automaker=a.automaker " | search automaker="$automaker$" | fields model, year, automaker, pays

[otman01]

i solved the issue by adding the search command instead of where command

[woodcock]

Read all about creating forms with user inputs (I would suggest dropdown for your case) here:

http://docs.splunk.com/Documentation/Splunk/latest/Viz/Buildandeditforms

[hogan24]

@woodcock's 2nd search below the text that reads "or even this" is by far the best/correct way to do it. I have searches running that way in my environment as well and that method does work.

I'm specifically referring to this part of passing the token into the query:
AND c.automaker=$automaker$"

[otman01]

It works if the data is stored in Splunk server. but if I try to extract data from MySQL database it doesn't work this is the command that I used :

| dbquery "db_ventes" "SELECT model, year, automaker, pays FROM cars c , automaker a where c.automaker=a.automaker " automaker=$automaker$ | fields model, year, automaker, country

         <label>sourcetype</label>
            <default>splunkd</default>
            <choice value="splunk_web_access">audi</choice>
            <choice value="splunkd_ui_access">dacia</choice>
         </input>
 </fieldset>

 <option name="showPager">true</option>
 <option name="count">20</option>

thank you

[woodcock]

Your command is slightly wrong; try this

| dbquery "db_ventes" "SELECT model, year, automaker, pays FROM cars c , automaker a where c.automaker=a.automaker " | search automaker="$automaker$" | fields model, year, automaker, country

Or even this:

| dbquery "db_ventes" "SELECT model, year, automaker, pays FROM cars c , automaker a where c.automaker=a.automaker AND c.automaker=$automaker$" | fields model, year, automaker, country

[otman01]

this is the hol command

|dbquery "db_ventes" "SELECT model, year, automaker, pays FROM cars c, automaker a where c.automaker=a.automaker " | search automaker="$automaker$" | fields model, year, automaker, pays

It works perfectley

[otman01]

hhhhhhhhh its the same sorry

[otman01]

it doesn't work