How to write lookup file for splunk query

[asplunk789]

I have a requirement to use lookups instead of queries in Splunk Dashboards.

How can I get them and how to convert them to lookups using queries.

For example::

Query :

index="gcp_prod_ecomm_webstoreui" "[ACCESS]" ("/catalog/*.jsp" OR "/product/" OR "/search.jsp*" OR "Cavisson") NOT("alive") NOT “dlr=true”
| rex field=MESSAGE "\d{2}:\d{2}:\d{2}\s(?<page_response_time>[0-9.]*)\s(?<method>.[A-Z]+)\s(?<Request>.[a-zA-Z0-9-:/^%?+&()\"=+_.-]*)\s(?<Request1>.[a-zA-Z0-9-:/^%?+&()\"=+_.-]*)\s(?<StatusCode>[0-9]+)" 
| eval RequestFormat=case(Request like "%catalog%", "Catalog Page", Request like "%product%", "Product Page", Request like "%search%", "Search Page") 
| timechart span=1m avg(page_response_time) by RequestFormat

[Kendrick821]

Hi, if you concern is performance issue. You may wish to consider in optimizing your searches. Here are some of the pointers.

1. Avoid extracting fields in search (rex). Try to extract the field first using props and transforms.
2. Avoid using keyword searches like "[ACCESS]". Use key-value pair instead if the fields are extracted.
3. Specify the sourcetype in your search. The index may contains multiple sourcetypes depending on your setup.
4. Use automatic lookup for your "Request" field to lookup the type of request. Prerequisite that the request field must be present during searching.

You may also like to watch this past .conf video for some recommendation in optimizing your searches.
https://conf.splunk.com/files/2016/recordings/search-optimization.mp4

For your case, it is not recommended to write the result into lookup and the present it again on dashboard from lookup.

[niketn]

@asplunk789, following is a search based on lookup file search_queries.csv with two columns Name of the search and Search with the Splunk Search Query. The dropdown token $tokSearchQuery$ has been passed directly to the <search><query>.

PS: For better management of Search Queries insert, update, delete you should explore KV Store Implementation in Splunk instead of lookups.

<panel>
  <title>Search Based on Lookup</title>
  <input type="dropdown" token="tokSearchQuery" searchWhenChanged="true">
    <label>Select Search Query (from lookup)</label>
    <fieldForLabel>Name</fieldForLabel>
    <fieldForValue>Search</fieldForValue>
    <search>
      <query>| inputlookup search_queries.csv | table Name Search</query>
    </search>
  </input>
  <chart>
    <search>
      <query>$tokSearchQuery$</query>
      <earliest>$tokTime.earliest$</earliest>
      <latest>$tokTime.latest$</latest>
    </search>
    <option name="charting.chart.showDataLabels">minmax</option>
    <option name="charting.chart.stackMode">stacked</option>
    <option name="refresh.display">progressbar</option>
  </chart>
</panel>

Following is a run anywhere example with makeresults instead of inputlookup. The same result from makeresults has been pushed to lookup file through the outputlookup command.

<panel>
  <title>Search Based on Dummy Query to Simulate Fetching from Lookup</title>
  <input type="dropdown" token="tokSearchQueryDummyTesting" searchWhenChanged="true">
    <label>Select Search Query (dummy through makeresults)</label>
    <fieldForLabel>Name</fieldForLabel>
    <fieldForValue>Search</fieldForValue>
    <search>
      <query>| makeresults

| eval Search="index=_internal sourcetype=splunkd component=\"ExecProcessor\" log_level!=\"INFO\" | timechart sum(date_second) as response_time by log_level",Name="Execprocessor Errors"
| append [| makeresults
| eval Search="index=_internal sourcetype=splunkd component=\"Search*\" log_level!=\"INFO\" | timechart sum(date_second) as response_time by log_level",Name="Search Components Errors"]
| append [| makeresults
| eval Search="index=_internal sourcetype=splunkd component=\"*\" log_level!=\"INFO\" | timechart sum(date_second) as response_time by log_level",Name="All Errors"]
| table Name Search
-24h@h
now

$tokSearchQueryDummyTesting$
$tokTime.earliest$
$tokTime.latest$

minmax
stacked
progressbar

Following is the complete dashboard code for screenshot above:

<form>
  <label>Run Search from Lookup</label>
  <fieldset submitButton="false">
    <input type="time" token="tokTime" searchWhenChanged="true">
      <label>Select Time</label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Search Based on Dummy Query to Simulate Fetching from Lookup</title>
      <input type="dropdown" token="tokSearchQueryDummyTesting" searchWhenChanged="true">
        <label>Select Search Query (dummy through makeresults)</label>
        <fieldForLabel>Name</fieldForLabel>
        <fieldForValue>Search</fieldForValue>
        <search>
          <query>| makeresults
| eval Search="index=_internal sourcetype=splunkd component=\"ExecProcessor\" log_level!=\"INFO\" | timechart sum(date_second) as response_time by log_level",Name="Execprocessor Errors"
| append [| makeresults
| eval Search="index=_internal sourcetype=splunkd component=\"Search*\" log_level!=\"INFO\" | timechart sum(date_second) as response_time by log_level",Name="Search Components Errors"]
| append [| makeresults
| eval Search="index=_internal sourcetype=splunkd component=\"*\" log_level!=\"INFO\" | timechart sum(date_second) as response_time by log_level",Name="All Errors"]
| table Name Search</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
      </input>
      <chart>
        <search>
          <query>$tokSearchQueryDummyTesting$</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
        </search>
        <option name="charting.chart.showDataLabels">minmax</option>
        <option name="charting.chart.stackMode">stacked</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
    <panel>
      <title>Search Based on Lookup</title>
      <input type="dropdown" token="tokSearchQuery" searchWhenChanged="true">
        <label>Select Search Query (from lookup)</label>
        <fieldForLabel>Name</fieldForLabel>
        <fieldForValue>Search</fieldForValue>
        <search>
          <query>| inputlookup search_queries.csv | table Name Search</query>
        </search>
      </input>
      <chart>
        <search>
          <query>$tokSearchQuery$</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
        </search>
        <option name="charting.chart.showDataLabels">minmax</option>
        <option name="charting.chart.stackMode">stacked</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
</form>

[deepikasounda]

Hi Nike,

The above solution works good.
But I have will have to have two drop downs inside the panel "Search Based on Lookup".
as Name and Subname and the corresponding query has to be executed.
This is because the Name has many subnames under it.
Could you kindly help me with it.

[Deepz2612]

Hi Nike,

The above solution works good.
But I have will have to have two drop downs inside the panel "Search Based on Lookup".
as Name and Subname and the corresponding query has to be executed.
This is because the Name has many subnames under it.
Could you kindly help me with it.

[niketn]

@splunk789 do you need to create lookup for Request values like Catalog Page, Product Page and Search Page etc or for entire query?

You can save entire query as a Macro Knowledge Object in Splunk, if you need to matain/re-use queries.

If you really need to run the Splunk Query from Lookup file could you tell what is the use case?

[asplunk789]

@niketnilay, Thanks for the reply.

My requirement is to optimize the dashboard which is having queries. Need to use lookups and optimize them for a better performance of Splunk search. So, how can I use the lookup in (XML Source ) the place of noraml search query (inputlookup snbuireesponsetime.csv)

[asplunk789]

      index="gcp_prod_ecomm_webstoreui"

"[ACCESS]" ("/catalog/.jsp" OR
"/product/" OR "/search.jsp" OR
"Cavisson") NOT("alive") NOT
“dlr=true” | rex field=MESSAGE
"\d{2}:\d{2}:\d{2}\s(?<page_response_time>[0-9.])\s(?<method>.[A-Z]+)\s(?<Request>.[a-zA-Z0-9-:/^%?+&()\"=+_.-])\s(?<Request1>.[a-zA-Z0-9-:/^%?+&()\"=+_.-]*)\s(?<StatusCode>[0-9]+)" | eval RequestFormat=case(Request like
"%catalog%", "Catalog Page", Request
like "%product%", "Product Page",
Request like "%search%", "Search
Page") | timechart span=1m
avg(page_response_time) by
RequestFormat usenull=f
useother=f
-15m
now