Splunk Search

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

otman01
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

otman01
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

otman01
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

0 Karma

otman01
Communicator

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

0 Karma

woodcock
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

hogan24
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

otman01
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

woodcock
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

otman01
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

otman01
Communicator

hhhhhhhhh its the same sorry

0 Karma

otman01
Communicator

it doesn't work

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...