topic How to change two parts of a search query based on input selection? in Splunk Search

How to change two parts of a search query based on input selection?

josephinemho — Tue, 29 Sep 2020 21:11:41 GMT

I have a column chart that needs to update based on the input selection (Hour/Weekday/Month - aka $field4$). I've managed to get it to update one part of the search query, but I need it to update two parts (not just one).

For example, this is my query:

index=os sourcetype=cpu cpu=all 
**| eval date_wday=strftime(_time,$field4$)**
| stats avg(pctIdle) by date_wday 
| rename avg(pctIdle) AS "Avg CPU" 
**| eval sort_field = case(date_wday=="Monday",1, date_wday=="Tuesday",2, date_wday=="Wednesday",3, date_wday=="Thursday",4, date_wday=="Friday",5, date_wday=="Saturday",6, date_wday=="Sunday",7)**
| sort sort_field
| fields - sort_field

I can't seem to figure out how to also update the second part in bold (eval sort_field = case(date_wday...) when a selection for $field4$ is made and I need it to change so that if "Month" is selected, the second part of the query would update to:

| eval sort_field = case(date_month=="January",1, date_month=="February",2, date_month=="March",3, date_month=="April",4, date_month=="May",5, date_month=="June",6, date_month=="July",7, date_month=="August",8, date_month=="September",9, date_month=="October",10, date_month=="November",11, date_month=="December",12)

Re: How to change two parts of a search query based on input selection?

DalJeanis — Thu, 06 Sep 2018 03:15:06 GMT

While we could tell you how to do exactly what you ask, I believe you would be better served with this advice:

On your dash, have two different panels with different searches, and hide the search you don't need with depends or rejects. That is conceptually simpler than swapping out language based on the selection, and it gives you more granular control over the appearance of the two different charts you want to present based on the selection.

Re: How to change two parts of a search query based on input selection?

niketn — Thu, 06 Sep 2018 04:58:22 GMT

@josephinemho, seems like while you need to display the Date on x-axis as Week Day or Month, you need to sort them on the actual order. However, even with your search SPL your logic might not work as expected in case there are more than one of same week day or month in the result. For example Monday Monday Tuesday ..., in case the selected time spans two week. So I would recommend using String Date also to be fetched with strftime() in YYYY-MM-DD or YYYY-MM format (depending on whether Week Or Month is chosen) followed by either WeekDay or Month. This will sort the results automatically using String Time. Then in your results you can use regular expression replace() or rex with sed depending on your preference for removing prefixed date.

Please try out the following run anywhere example. Notice Thursday appears twice as First and Last values in the series. (PS: I have not included hour for simplicity but your can use the same logic for Weekday for hour as well)

<form>
  <label>Set tokens through change handler</label>
  <fieldset submitButton="false">
    <input type="time" token="tokTime" searchWhenChanged="true">
      <label></label>
      <default>
        <earliest>-7d@h</earliest>
        <latest>now</latest>
      </default>
    </input>
    <input type="radio" token="tokSpan" searchWhenChanged="true">
      <label>Select span</label>
      <choice value="%Y/%m/%d - %A">Weekday</choice>
      <choice value="%Y/%m - %B">Month</choice>
      <default>%Y/%m/%d - %A</default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Table</title>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level=* 
| eval date_wday=strftime(_time,"$tokSpan$")
| stats avg(cpu_seconds) as "Avg CPU" by date_wday
| eval date_wday=replace(date_wday,"^([^\s]+)","")</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
</form>

OPTION 2: If you need to stick to your original search and want to set more than one token you should use input <change> event handler where you can set multiple tokens as per your need.

<change>
     <condition value="%A">
           <set token="tokSortEval">case(date_wday=="Monday",1, date_wday=="Tuesday",2, date_wday=="Wednesday",3, date_wday=="Thursday",4, date_wday=="Friday",5, date_wday=="Saturday",6, date_wday=="Sunday",7)</set>
     </condition>
     <condition value="%B">
           <set token="tokSortEval">case(date_month=="January",1, date_month=="February",2, date_month=="March",3, date_month=="April",4, date_month=="May",5, date_month=="June",6, date_month=="July",7, date_month=="August",8, date_month=="September",9, date_month=="October",10, date_month=="November",11, date_month=="December",12)</set>
     </condition>
</change>

Then use $tokSortEval$ in your second part of search i.e.

| eval sort_field = $tokSortEval$

Re: How to change two parts of a search query based on input selection?

josephinemho — Thu, 06 Sep 2018 22:18:46 GMT

Thank you so much @niketnilay ^_^ I went with your second suggestion and the dashboard working perfectly now!!

Here's the first section of XML (for token setting/switching text in search query):

      <input type="radio" token="field4" searchWhenChanged="true">
        <label>By Hour/Weekday/Month</label>
        <choice value="&quot;%H&quot;">Hour</choice>
        <choice value="&quot;%A&quot;">Weekday</choice>
        <choice value="&quot;%B&quot;">Month</choice>
        <change>
          <condition value="&quot;%H&quot;">
            <set token="date_label">Hour</set>
            <set token="sort_query"></set>
          </condition>
          <condition value="&quot;%A&quot;">
            <set token="date_label">Weekday</set>
            <set token="sort_query">| eval sort_field = case(date=="Monday",1, date=="Tuesday",2, date=="Wednesday",3, date=="Thursday",4, date=="Friday",5, date=="Saturday",6, date=="Sunday",7)</set>
          </condition>
          <condition value="&quot;%B&quot;">
            <set token="date_label">Month</set>
            <set token="sort_query">| eval sort_field = case(date=="January",1, date=="February",2, date=="March",3, date=="April",4, date=="May",5, date=="June",6, date=="July",7, date=="August",8, date=="September",9, date=="October",10, date=="November",11, date=="December",12)</set>
          </condition>
        </change>
        <default>"%A"</default>
        <initialValue>"%A"</initialValue>
      </input>

Second section of XML (for graphs):

  <row>
    <panel>
      <chart>
        <title>By $date_label$</title>
        <search>
          <query>index=os (sourcetype=cpu cpu=all) OR (sourcetype=vmstat) 
| lookup sa_managed_servers_new.csv host 
| search server_group=SA machine_type=$field2$ sadb_service=$field3$
| eval Percent_CPU_Load = 100 - pctIdle 
| eval date=strftime(_time,$field4$) 
| stats avg(Percent_CPU_Load) avg(memUsedPct) avg(swapUsedPct) by date 
| rename avg(Percent_CPU_Load) AS "Avg CPU" avg(memUsedPct) as "Avg Memory" avg(swapUsedPct) AS "Avg Swap Memory"
$sort_query$
| sort sort_field
| fields - sort_field</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="charting.axisTitleX.visibility">collapsed</option>
        <option name="charting.axisTitleY.text">%</option>
        <option name="charting.axisY.maximumNumber">100</option>
        <option name="charting.axisY.minimumNumber">0</option>
        <option name="charting.chart">line</option>
        <option name="charting.chart.showDataLabels">minmax</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.layout.splitSeries">0</option>
      </chart>
    </panel>