How to create a dropdown search on columns of data which aren't indexed and a text box for keyword searches?

[sjanwity]

I have splunk's dbconnect app return me some columns, let's say they're they sample below:

 Table Customers-History:

 TIMESTAMP            | OPERATION | Customer ID | Customer Name | Customer Address
 1-Dec-2010 09:52:1232| INSERT     | 002        | Kyle A          | 10 Gammon Road
 2-Dec-2010 09:54:9500| DELETE     | 002         | Kyle A         | 10 Gammon Road
 2-Dec-2010 09:54:9900| INSERT     | 002         | Kyle A        | 16 Gammon Road
 2-Dec-2010 09:55:9921| DELETE     | 003         | Josh C        | 21 Drury Lane

I want to create a dropdown box where the user selects the column they want to search in (i.e. timestamp, operation, customer id, customer name, customer address) and then a text box where the user enters the keyword for the search

I've followed the splunk tutorial on this at http://docs.splunk.com/Documentation/Splunk/6.1.4/Viz/Buildandeditforms#Static_and_dynamic_inputs_to... but they assume that my columns are labelled as sourcetype when my columns are actually just columns returned from the dbconnect app. How can I work around this?

[martin_mueller]

So... you're running a dbquery whenever the user makes an input?

I see two options. Either modify the SQL being run like so:

| dbquery some_database "select * from foo where $column$ = '$value$'"

Or have Splunk filter the results like so:

| dbquery some_database "select * from foo" | search $column$ = "$value$"

In both cases $column$ is the token for the selected column and $value$ is the value entered into the text box.

[martin_mueller]

Combine Lisa's UI-minded answer with my search-minded answer.

[sjanwity]

not quite what I was looking for...I want a dropdown box for the user to select what column he wants to search on.

[lguinn2 · Accepted solution]

You probably don't want to set a dynamic input for this. I would do this in Simple XML

<fieldset>
     <input type="dropdown" token="searchColumn">
         <label>Select a column to search</label>
         <choice value="OPERATION">Operation</choice>
         <choice value="CustomerID">Customer ID</choice>
         <choice value="CustomerName">Customer Name</choice>
         <choice value="CustomerAddr">Customer Address</choice>
     </input>
  <input type="text" token="searchText">
    <label>Enter the value of the field</label>
  </input>
 </fieldset>
...
  <searchTemplate>
    yoursearchherewith $searchColumn$="$searchText$"
  </searchTemplate>

If you really want to dynamically generate the list of field names in the drop down, you can do this

     <input type="dropdown" token="searchColumn">
         <label>Select a column to search</label>
        <populatingSearch fieldForValue="fieldName" fieldForLabel="fieldName">
              <![CDATA[yourDBconnectsearchhere | fieldsummary maxvals=1 | rename field as fieldName | fields fieldName]>
       </populatingSearch>
     </input>

Everything else stays the same.

[lguinn2]

Yes, this will work with a time range picker. To explicitly add a timerange picker in Simple XML:

  <input type="time" token="time_tok" searchWhenChanged="true">
      <label>Select time range</label>
      <default>
        <earliestTime>-7d@h</earliestTime>
        <latestTime>now</latestTime>
      </default>
    </input>

The above input goes into your fieldset tag along with the other information. Note that this example sets the timerange picker to "Last 7 days" as a default, but you can change that, of course.

You can use the resulting time selection in both the populating search and the actual search that creates the report. For example:

<populatingSearch fieldForValue="fieldName" fieldForLabel="fieldName">
    <![CDATA[yourDBconnectsearchhere earliest=$time_tok.earliest$ latest=$time_tok.latest$
              | fieldsummary maxvals=1 | rename field as fieldName | fields fieldName]>
</populatingSearch>

And

  <chart>
        <title>Source type count for last 7 days</title>
        <searchString>
         youractualsearchhere
        </searchString>
        <earliestTime>$time_tok.earliest$</earliestTime>
        <latestTime>$time_tok.latest$</latestTime>
        ...
</chart>

[sjanwity]

By 'yourDBconnectsearchhere' you mean my query, right? Because the query is quite long and goes through quite a few splunk processes! Will this also work with a time range picker?