topic Re: how can we add a condition of selection in sql query using db_connect and field input in Splunk Search

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

otman01 — Thu, 09 Jul 2015 14:18:05 GMT

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

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

woodcock — Thu, 09 Jul 2015 14:46:50 GMT

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

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

otman01 — Fri, 10 Jul 2015 08:10:51 GMT

it doesn't work

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

otman01 — Fri, 10 Jul 2015 08:23:57 GMT

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

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

otman01 — Fri, 10 Jul 2015 13:13:13 GMT

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

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

woodcock — Fri, 10 Jul 2015 13:18:37 GMT

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

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

otman01 — Fri, 10 Jul 2015 13:28:47 GMT

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

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

otman01 — Fri, 10 Jul 2015 13:29:44 GMT

hhhhhhhhh its the same sorry

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

otman01 — Fri, 10 Jul 2015 13:32:00 GMT

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

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

hogan24 — Thu, 23 Jul 2015 20:08:37 GMT

@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$"