topic Re: Using Lookup Table in Splunk Search

Using Lookup Table

alan20854 — Thu, 23 Jun 2016 13:53:09 GMT

Hi,

I am using a lookup table to populate 3 dropdown menus: Source, Service, and Method, where each selection of the previous dropdown creates the set of options for the next dropdown.

However, in the dropdown options for Service and Method, I am using a word description of the service/method code, e.g. Vendor Web Service in lieu of VNDR_WS. In my .csv file, the first three columns are source, service, and then method, and then I have two more columns with the corresponding serviceCode and methodCode.

How do I incorporate the last two columns into my search query? I would just like to search for something like serviceCode = $serviceCode$ methodCode=$methodCode$

Re: Using Lookup Table

alan20854 — Thu, 23 Jun 2016 13:53:47 GMT

Here is my XML:

<input type="dropdown" token="source1" searchWhenChanged="true">
  <label>Source</label>
  <search>
    <query>| inputlookup Lookup.csv | stats count by source | table source</query>
  </search>
  <fieldForLabel>source</fieldForLabel>
  <fieldForValue>source</fieldForValue>
</input>
<input type="dropdown" token="service1" searchWhenChanged="true">
  <label>Service</label>
  <search>
    <query>| inputlookup Lookup.csv | search source="$source1$" | stats count by service | table service</query>
  </search>
  <fieldForLabel>service</fieldForLabel>
  <fieldForValue>service</fieldForValue>
</input>
<input type="dropdown" token="method1" searchWhenChanged="true">
  <label>Method/Operation</label>
  <search>
    <query>| inputlookup Lookup.csv | search (source = "$source1$" AND service = "$service1$")| stats count by method | table method</query>
    <earliest>0</earliest>
  </search>
  <fieldForLabel>method</fieldForLabel>
  <fieldForValue>method</fieldForValue>
</input>
<input type="time" token="time" searchWhenChanged="true">
  <label>Last Transaction Time</label>
  <default>
    <earliest>@d</earliest>
    <latest>now</latest>
  </default>
</input>


<panel>
  <event>
    <title>Most Recent Event</title>
    <search>
      <query>| inputlookup Lookup.csv | $serviceCode$ = search (source = "$source1$" AND service = "$service1$" AND method = "$method1$") |sort -_time | head 1
      <earliest>$time.earliest$</earliest>
      <latest>$time.latest$</latest>
    </search>
    <fields>["host","source","sourcetype"]</fields>
  </event>
</panel>

Re: Using Lookup Table

somesoni2 — Thu, 23 Jun 2016 14:16:56 GMT

Use like this

dropdown service

<search>
     <query>| inputlookup Lookup.csv | search source="$source1$" | stats count by service serviceCode | table service serviceCode</query>
   </search>
   <fieldForLabel>service</fieldForLabel>
   <fieldForValue>serviceCode</fieldForValue>

dropdown method

 <search>
     <query>| inputlookup Lookup.csv | search (source = "$source1$" AND serviceCode = "$service1$")| stats count by method methodCode| table method methodCode</query>
     <earliest>0</earliest>
   </search>
   <fieldForLabel>method</fieldForLabel>
   <fieldForValue>methodCode</fieldForValue>

Panel Search

| inputlookup Lookup.csv | $serviceCode$ = search (source = "$source1$" AND serviceCode = "$service1$" AND methodCode = "$method1$") |sort -_time | head 1

Re: Using Lookup Table

snoobzilla — Thu, 23 Jun 2016 14:23:08 GMT

Perfect used case for label/value functionality... I partially changed below. Basically you need to carry changes into your search.

 <input type="dropdown" token="source1" searchWhenChanged="true">
   <label>Source</label>
   <search>
     <query>| inputlookup Lookup.csv | stats count by source | table source</query>
   </search>
   <fieldForLabel>source</fieldForLabel>
   <fieldForValue>source</fieldForValue>
 </input>
 <input type="dropdown" token="service1" searchWhenChanged="true">
   <label>Service</label>
   <search>
     <query>| inputlookup Lookup.csv | search source="$source1$" | stats count by service | table service ServiceCode</query>
   </search>
   <fieldForLabel>service</fieldForLabel>
   <fieldForValue>ServiceCode</fieldForValue>
 </input>
 <input type="dropdown" token="method1" searchWhenChanged="true">
   <label>Method/Operation</label>
   <search>
     <query>| inputlookup Lookup.csv | search (source = "$source1$" AND service = "$service1$")| stats count by method | table method MethodCode</query>
     <earliest>0</earliest>
   </search>
   <fieldForLabel>method</fieldForLabel>
   <fieldForValue>MethodCode</fieldForValue>
 </input>
 <input type="time" token="time" searchWhenChanged="true">
   <label>Last Transaction Time</label>
   <default>
     <earliest>@d</earliest>
     <latest>now</latest>
   </default>
 </input>


 <panel>
   <event>
     <title>Most Recent Event</title>
     <search>
       <query>| inputlookup Lookup.csv | $serviceCode$ = search (source = "$source1$" AND service = "$service1$" AND method = "$method1$") |sort -_time | head 1
       <earliest>$time.earliest$</earliest>
       <latest>$time.latest$</latest>
     </search>
     <fields>["host","source","sourcetype"]</fields>
   </event>
 </panel>

Re: Using Lookup Table

snoobzilla — Thu, 23 Jun 2016 14:25:30 GMT

Yeah, what he said.

Re: Using Lookup Table

alan20854 — Thu, 23 Jun 2016 14:49:29 GMT

Thanks for the help!