How can I get the drilldown query working for the SUCCESS piece?

[dbcase]

Hi I have this initial query

      <title>Host = $relay_hostname$</title>
      <chart>
        <title>Successful/Unsuccessful Relay Sessions</title>
        <search>
          <query>index=relay_json host=$relay_hostname$ relayPairId!="null"  | transaction relayPairId startswith="extracted_eventType=NewRelayCreated*" endswith="extracted_eventType=RelayClosed" |eval decision=if(severity="ERROR", "ERROR","SUCCESS")
 | stats count  by decision</query>
          <earliest>$time_field.earliest$</earliest>
          <latest>$time_field.latest$</latest>
        </search>
        <option name="charting.chart">pie</option>
        <drilldown>
          <set token="showrelaysessions">$click.value$</set>
          <unset token="showlogins"></unset>
        </drilldown>
      </chart>
    </panel>

and then the subsequent drilldown

<row>
    <panel depends="$showrelaysessions$">
      <title>Relay sessions where severity=$showrelaysessions$</title>
      <event>
        <title>(Click any event time stamp to close)</title>
        <search>
          <query>index=relay_json host=$relay_hostname$ relayPairId!="null"  | transaction relayPairId startswith="extracted_eventType=NewRelayCreated*" endswith="extracted_eventType=RelayClosed"|where severity="$showrelaysessions$"</query>
          <earliest>$time_field.earliest$</earliest>
          <latest>$time_field.latest$</latest>
        </search>
        <drilldown>
          <unset token="showrelaysessions"></unset>
        </drilldown>
      </event>
    </panel>
  </row>

It works perfectly when the user selects the ERROR condition because the severity field in the events actually has ERROR as the value. If the user selects SUCCESS the drilldown fails because the severity field contains either DEBUG or WARNING. How can I get the drilldown query working for the SUCCESS piece? Essentially severity!=ERROR.

[niketn · Accepted solution]

@dbcase to answer your question, I would use <eval> to set the token for chart <drilldown>

<drilldown>
   <eval token="showrelaysessions">if($click.value$="ERROR","severity=ERROR","severity=DEBUG OR severity=WARN")</eval>
   ...
</drilldown>

Then in the second Panel Search I would use the following <query>

 <query>index=relay_json host=$relay_hostname$ relayPairId!="null"  
| transaction relayPairId startswith="extracted_eventType=NewRelayCreated*" endswith="extracted_eventType=RelayClosed"
| search $showrelaysessions$</query>

However besides above changes that you have asked I would also consider only the fields required from NewRelayCreated* and RelayClosed and instead of transaction would try to use stats.

Further, I would have tried to incorporate post-processing from Panel 1 to Panel 2. Essentially we are just applying a filter to the base search. Running transaction itself is expensive (and may silently truncate results over a longer span), you are running the same transaction twice in the same dashboard.

Following is a run anywhere dashboard based on Splunk's _internal index. It used post-processing where the base search baseTransactionSearch uses stats to correlate the data and pass on the required fields to be displayed as detail from Pie Chart drilldown. The component field has been used instead of relayPairId as an example. You would also need to move extracted_eventType="NewRelayCreated*" and extracted_eventType="RelayClosed" as your main query filter in the base search. In this case for simplicity I have used values(_raw) as correlated_data in the base search, but you should evaluate the required field and populate the same use appropriate statistical function like last(), min(), latest() etc.

<form>
  <label>Token Drilldown run anywhere example</label>
  <search id="baseTransactionSearch">
    <query>index=_internal sourcetype=splunkd host=* component=*
| stats last(log_level) as severity values(_raw) as correlated_data by component
    </query>
    <earliest>$time_field.earliest$</earliest>
    <latest>$time_field.latest$</latest>
  </search>
  <fieldset submitButton="false">
    <input type="time" token="time_field" searchWhenChanged="true">
      <label></label>
      <default>
        <earliest>-4h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title></title>
      <chart>
        <title>Successful/Unsuccessful Relay Sessions</title>
        <search base="baseTransactionSearch">
          <query>| stats count by severity 
| eval severity=replace(replace(severity,"INFO","SUCCESS"),"WARN","SUCCESS")
| stats sum(count) as count by severity</query>
        </search>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.0001</option>
        <option name="charting.legend.placement">right</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.splitBy">_aggregation</option>
        <drilldown>
          <eval token="showrelaysessions">case($row.severity$="ERROR","severity=ERROR",true(),"severity=DEBUG OR severity=WARN")</eval>
          <unset token="showlogins"></unset>
        </drilldown>
      </chart>
    </panel>
  </row>
  <row>
    <panel depends="$showrelaysessions$">
      <title>Relay sessions where $showrelaysessions$</title>
      <table>
        <title>(Click any event time stamp to close)</title>
        <search base="baseTransactionSearch">
          <query>| search $showrelaysessions$</query>
        </search>
        <option name="drilldown">cell</option>
        <drilldown>
          <unset token="showrelaysessions"></unset>
        </drilldown>
      </table>
    </panel>
  </row>
</form>

Please try out and confirm!

[dbcase]

Interesting, how would I go about using stats?

[niketn]

For that could you tell me the fields you would be interested in and also the sample data for NewRelatCreated* and RelayClosed that you are trying to correlate?

I have updated the answer with a run anywhere dashboard based on Splunk's _internal index for you to modify the same as per your needs. BTW, did the above drilldown work? Since that is the main issue we intend to resolve 🙂

[dbcase]

it worked perfectly! Many thanks!!!

[niketn]

I am glad you found your answer 🙂

[gcusello]

Hi dbcase,
try something like this in the secondary search

index=relay_json host=$relay_hostname$ relayPairId!="null"  
| transaction relayPairId startswith="extracted_eventType=NewRelayCreated*" endswith="extracted_eventType=RelayClosed"
| eval 
     token_severity1=if("$showrelaysessions$"="ERROR","ERROR","DEBUG"), 
     token_severity2=if("$showrelaysessions$"="ERROR","ERROR","WARNING")
| search severity=token_severity1 OR severity=token_severity2

Bye.
Giuseppe