topic Re: Drilldown using chart clicks but also need all values in Splunk Search

Drilldown using chart clicks but also need all values

mbasharat — Wed, 31 Jul 2019 14:57:43 GMT

Hi,

Something eiher I forgot or not getting right. I have a chart. See attached. When I click on the EVENTYPE value in chart, my additional stats table picks up search based off of the value clicked in chart and it is working fine.

Issue is:

I need default results to be ALL values.
When user clicks on EVENTTYPE in chart, user should get the selected results based off of value clicked in chart. I do not want to use drop down etc. it needs to be chart clicks.
Also need to know how to switch back to all after above selection?

My simple selection search is:

| search USERID="$userid$" AND EVENTTYPE="$eventtype$"

Above SPL keeps saying "search waiting for input" and like I said, need default to be all, when clicked, show selective results and then need to switch back to all again.

Thanks in- advance!!!!

Re: Drilldown using chart clicks but also need all values

gcusello — Wed, 31 Jul 2019 15:18:47 GMT

Hi mbasharat,
let me understand:
in your dashboard do you have one or two panels?
if one panel, you have a drilldown clicking on the eventtype (user or assessment)
if two panels, you have one panel with users and one with eventtypes and you want to drilldown from both the panels in one dashboard but you have two parameters.

If you have one panel I don't understand your question, if you have two panels, you have to put in the drilldown command also the other parameter=*, in other words:
in the eventtype panel

<drilldown>
     <link>secondary_dashboard?eventtype=$click.name2$&amp;user=*</link>
</drilldown>

in the user panel

<drilldown>
     <link>secondary_dashboard?eventtype=*&amp;user=$click.name2$</link>
</drilldown>

Bye.
Giuseppe

Re: Drilldown using chart clicks but also need all values

mbasharat — Wed, 31 Jul 2019 15:32:14 GMT

Hi @ gcusello,

See sample dashboard code below:

<form>
  <label>Test Dashboard</label>
  <search id="zipcodematch" ref="Zipcode Match Report"></search>
  <fieldset autoRun="false" submitButton="true">
    <input type="dropdown" searchWhenChanged="true" token="userid">
      <label>User ID</label>
      <choice value="*">All</choice>
      <default>*</default>
      <initialValue>*</initialValue>
      <search base="zipcodematch">
        <query>| stats dc(USERID) AS  count BY USERID
| fields USERID
| sort USERID Asc</query>
      </search>
      <fieldForLabel>USERID</fieldForLabel>
      <fieldForValue>USERID</fieldForValue>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Count by EVENTTYPE</title>
      <chart>
        <search base="zipcodematch">
          <query>| search USERID="$userid$"
| stats count by EVENTTYPE
| sort count Desc</query>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
        <option name="charting.axisTitleX.visibility">collapsed</option>
        <option name="charting.axisTitleY.visibility">collapsed</option>
        <option name="charting.axisY.scale">log</option>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.chart.sliceCollapsingThreshold">0</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.legend.placement">none</option>
        <option name="height">225</option>
        <option name="link.exportResults.visible">0</option>
        <option name="link.inspectSearch.visible">0</option>
        <option name="link.openPivot.visible">0</option>
        <option name="link.openSearch.visible">0</option>
        <option name="refresh.display">progressbar</option>
        <option name="refresh.link.visible">0</option>
        <drilldown>
          <set token="eventtype">$row.EVENTTYPE$</set>
        </drilldown>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Report Data</title>
      <table>
        <search base="zipcodematch">
          <query>| search USERID="$userid$" AND EVENTTYPE="$eventtype$" 
| stats count BY USERID, EVENTTYPE
| fields - count</query>
        </search>
        <option name="count">10</option>
        <option name="drilldown">none</option>
        <option name="link.exportResults.visible">0</option>
        <option name="link.inspectSearch.visible">0</option>
        <option name="link.openPivot.visible">0</option>
        <option name="link.openSearch.visible">0</option>
        <option name="refresh.display">progressbar</option>
        <option name="refresh.link.visible">0</option>
        <option name="rowNumbers">true</option>
        <format type="color" field="RETURNCODE">
          <colorPalette type="map">{"SUCCESS":#53A051,"UNSUCCESSFUL":#BA2B04,"Unknown":#00DDFF}</colorPalette>
        </format>
      </table>
    </panel>
  </row>
</form>

Re: Drilldown using chart clicks but also need all values

gcusello — Wed, 31 Jul 2019 15:52:51 GMT

Hi mbasharat,
let me understand:

you have a drilldown for users that you use in only the first panel, not in the second one;
then clicking on the first panel, you choose an eventtype and you want to search with the clicked eventtype and all users, not the selected one,

is it correct?

if this is your request, in the second panel's search put

| search USERID="*" AND EVENTTYPE="$eventtype$"

Bye.
Giuseppe

Re: Drilldown using chart clicks but also need all values

mbasharat — Wed, 31 Jul 2019 16:05:24 GMT

The drilldown for User is used in both EVENTTYPE chart panel as well as Report Data panel.
I want the report data panel not to give me "search waiting for input" and waiting for me to click an EVENTTYPE from pie chart in first panel.
I want the Report Data panel to be showing results from all EVENTTYPE as default and then if I click on a specific EVENTTYPE in chart above, should display on that EVENTTYPE results.

Re: Drilldown using chart clicks but also need all values

gcusello — Wed, 31 Jul 2019 17:46:12 GMT

Hi mbasharat,
Ok, probably I understood,
you should modify the second panel search:

| eval my_param="all"
| eval my_param=if(my_param="all","*","$eventtype$")
| search USERID="$userid$" AND EVENTTYPE=my_param 
| stats count BY USERID, EVENTTYPE
| fields - count

I cannot test it, but it should run.

Bye.
Giuseppe

Re: Drilldown using chart clicks but also need all values

mbasharat — Thu, 01 Aug 2019 12:40:36 GMT

Hi @gcusello,

It did not work. Something is missing in eval.

Re: Drilldown using chart clicks but also need all values

gcusello — Thu, 01 Aug 2019 14:14:08 GMT

Hi mbasharat,
it's hard without testing, anyway try:

 | eval my_param="*"
 | eval my_param=coalesce("$eventtype$",my_param)
 | search USERID="$userid$" AND EVENTTYPE=my_param 
 | stats count BY USERID, EVENTTYPE
 | fields - count
 | eval my_param=if(my_param="all","*","$eventtype$")
 | search USERID="$userid$" AND EVENTTYPE=my_param 
 | stats count BY USERID, EVENTTYPE
 | fields - count

Bye.
Giuseppe

Re: Drilldown using chart clicks but also need all values

niketn — Thu, 01 Aug 2019 14:48:47 GMT

@mbasharat I think what you are looking for is to have drilldown token pre-populated with * to display all the values and on drilldown from chart you want to set the same to specific value.

You can use <init> section in Simple XML Dashboard (version 6.5 and above) for this. Also you would still need some way to reset the filter to * for All values as the same will not be available in your chart. Please refer below to a run anywhere example that does this using <init> section and Link List input.

The screenshot below has first snippet is where All is drilldown value by default or on reset and second snippet is specific drilldown from Pie Chart to Timechart below (by clicking on Specific Pie Slice, in this example INFO).

Following is the complete Simple XML code for above example:

<form>
  <label>Pie Chart Drilldown with Default Token</label>
  <init>
    <set token="component">*</set>
    <set token="componentLabel">All</set>
    <unset token="tokShowResetButton"></unset>
  </init>
  <fieldset submitButton="false"></fieldset>
  <row>
    <panel>
      <title>Click Pie Chart to Drilldown with specific log_level</title>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd
|  stats count by log_level</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.drilldown">all</option>
        <drilldown>
          <set token="component">$click.value$</set>
          <set token="componentLabel">$click.value$</set>
          <set token="tokShowResetButton"></set>
        </drilldown>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Show Component drilldown details for $componentLabel$ log_level</title>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level="$component$" 
| timechart count by component</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">all</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
  <row depends="$tokShowResetButton$">
    <panel>
      <input id="splk_chkbox_reset" type="link" token="tokReset" searchWhenChanged="true">
        <label></label>
        <choice value="reset">Reset to All</choice>
        <change>
          <condition value="reset">
            <set token="component">*</set>
            <set token="componentLabel">All</set>
            <unset token="form.tokReset"></unset>
            <unset token="tokShowResetButton"></unset>
          </condition>
        </change>
      </input>
      <html depends="$alwaysHideCSSPanel$">
        <style>
          #splk_chkbox_reset {
                float: right;
          }
        </style>
      </html>
    </panel>
  </row>
</form>

Re: Drilldown using chart clicks but also need all values

mbasharat — Thu, 01 Aug 2019 15:27:09 GMT

This worked!! TY @niketnilay

Re: Drilldown using chart clicks but also need all values

mbasharat — Thu, 01 Aug 2019 15:28:10 GMT

Hi gcusselo,

niketnilay's token suggestion worked. TY for your prompt responses which gave me an idea for another use case with slight mod. Will upvote this as well. !!!