Splunk Search

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

Communicator

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

0 Karma
1 Solution

Communicator

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

View solution in original post

0 Karma

Communicator

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

View solution in original post

0 Karma

Communicator

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

0 Karma

Esteemed Legend

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

0 Karma

Path Finder

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

0 Karma

Communicator

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

0 Karma

Esteemed Legend

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
0 Karma

Communicator

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

0 Karma

Communicator

hhhhhhhhh its the same sorry

0 Karma

Communicator

it doesn't work

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!