Dashboards & Visualizations

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

dbcase
Motivator

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.

0 Karma
1 Solution

niketn
Legend

@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!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@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!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

dbcase
Motivator

Interesting, how would I go about using stats?

0 Karma

niketn
Legend

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 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

dbcase
Motivator

it worked perfectly! Many thanks!!!

0 Karma

niketn
Legend

I am glad you found your answer 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...