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
Hi, if you concern is performance issue. You may wish to consider in optimizing your searches. Here are some of the pointers.
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.
@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
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>
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.
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.
@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?
@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)
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