How can I convert the data in a chart to a more readable format and than perform a subsequent drilldown?

[dbcase]

Hi,

I have this query that works just fine for its initial purpose.

index=wholesale_app CustomAnalytic Properties.index=17 OR Properties.index=19 OR Properties.index=21 OR Properties.index=23 OR (Properties.index=>25 AND Properties.index<=32) buildTarget=* product=* |rename Properties.index as pindex|eval pindextype=case(pindex == "17", "CVR Event Selected", pindex == "19", "CVR Fetch Events",pindex == "21", "CVR Event View Ended", pindex == "23", "CVR Play Pressed",pindex == "25", "CVR Pause Pressed", pindex == "26", "CVR Landscape Orientation",pindex == "27", "CVR Portrait Orientation", pindex == "28", "CVR Jump Forward", pindex == "29", "CVR Jump Back", pindex == "30", "CVR Video Session Started", pindex == "31", "CVR Video Error", pindex == "32", "CVR Range Set")|stats count by pindextype

I'm changing the Properties.index (aka pindex) field from a number to a more human readable text for the chart which looks like this

All is well and good until I get asked to make the pie chart drilldown-able. I know how to make that happen, the challenge I'm running into is when the user clicks a slice of the pie chart, the $click.value$ value is the human-readable text, not the numeric actual data in the event so the search returns nothing. After thinking about this for a while I did come up with a "solution" but its ugly. My "solution" is: When the user clicks on a slice take the resulting $click.value$ value and eval it back to the original numeric data, then do the search, then eval the numeric data back into human-friendly form. While this will work it seems ugly. Is there a more elegant way to do this?

[FrankVl]

Wouldn't this be a good case to use fieldformat instead of eval? That changes the appearance of the field, but not the underlying value. Don't have a splunk instance at hand to confirm that also works for charting and drilldowns, but should be easy enough to try.

[dbcase]

Hi FrankVI,

Interesting, never have used fieldformat before. From what I'm reading it looks like it would work. I'm trying it in the query this way but does not seem to work, the query gives no results but if I change fieldformat back to eval it works. Any thoughts?

earliest=-24h index=wholesale_app buildTarget=* product=* CustomAnalytic Properties.index=17 OR Properties.index=19 OR Properties.index=21 OR Properties.index=23 OR (Properties.index=>25 AND Properties.index<=32) buildTarget=* product=* |rename Properties.index as pindex|fieldformat pindextype=case(pindex == "17", "CVR Event Selected", pindex == "19", "CVR Fetch Events",pindex == "21", "CVR Event View Ended", pindex == "23", "CVR Play Pressed",pindex == "25", "CVR Pause Pressed", pindex == "26", "CVR Landscape Orientation",pindex == "27", "CVR Portrait Orientation", pindex == "28", "CVR Jump Forward", pindex == "29", "CVR Jump Back", pindex == "30", "CVR Video Session Started", pindex == "31", "CVR Video Error", pindex == "32", "CVR Range Set")|stats count by pindextype

[FrankVl]

You need to apply fieldformat to the pindex field itself, rather than a fresh, not existing field.

[niketn · Accepted solution]

[Updated Answer]
Added two more approaches to extract only pindex from the label field which contains both pindex and description. For example 17.CVR Event Selected

---

@dbcase, an easier option would be to have pindex - pdesciption in your pie slice (for example - 17.CVR Event Selected) so that you can use <eval> to extract only pindex during drilldown. Following is a approach with eval where pindex is first two characters of the Pie Label containing both pindex and description

| eval pindex=pindex.".".Description

Following should be the <eval> section for drilldown which extracts the first two characters as pindex:

    <drilldown>
      <eval token="tokPIndex">substr($click.value$,0,2)</eval>
    </drilldown>

Another option on same approach would be to use a dummy search to extract and assign pindex from the clicked pie slice which contains both index and description. Following example uses the replace() function to apply reg ex to pull only first extraction

  <search>
    <query>| makeresults
 | eval clickedValue="$tokPIndexDesc$"
 | eval pindex=replace(pindex,"([^\.]+)(\..*)","\1")
    </query>
    <done>
      <set token="tokPIndex">$result.pindex$</set>
    </done>
  </search>

However, if you do not want to display pindex in Pie Chart and still do the drilldown, you may use the lookup table approach as suggested. Following is a run anywhere dashboard example based on lookup file pindex_description.csv and lookup definition pindex_description. It uses a dummy search to perform a reverse lookup of pindex from the clicked description in the pie chart.

Following is the Dashboard code

<dashboard>
  <label>Pie chart Drilldown with lookup</label>
  <search>
    <query>| inputlookup pindex_description.csv where Description="$tokPIndexDesc$"
| table pindex
    </query>
    <done>
      <set token="tokPIndex">$result.pindex$</set>
    </done>
  </search>
  <row>
    <panel>
      <title>$tokPIndexDesc$ - $tokPIndex$</title>
      <chart>
        <search>
          <query>| makeresults
| eval pindex=17,count=20
| append [| makeresults
| eval pindex=26,count=15]
| append [| makeresults
| eval pindex=31,count=35]
| fields - _time
| lookup pindex_description pindex output Description
| table Description count</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisEnd</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <drilldown>
          <eval token="tokPIndexDesc">$click.value$</eval>
        </drilldown>
      </chart>
    </panel>
  </row>
</dashboard>

Lookup file ( pindex_description.csv ) for reference:

pindex  Description
17  CVR Event Selected
19  CVR Fetch Events
21  CVR Event View Ended
23  CVR Play Pressed
25  CVR Pause Pressed
26  CVR Landscape Orientation
27  CVR Portrait Orientation
28  CVR Jump Forward
29  CVR Jump Back
30  CVR Video Session Started
31  CVR Video Error
32  CVR Range Set

Please test and confirm.

[dbcase]

Hi Niketnilay,

Wow, I really do appreciate all your effort on this one. You went above and beyond. I went with the eval token solution and it works great! thanks again!!!

[niketn]

Anytime!!! Happy Weekend 🙂

[dbcase]

Hi Niketnilay,

I don't mind having the number in the pie slice description and then doing an eval. How would I go about that?

[niketn]

@dbcase, I have updated my answer with two more approaches without the use of lookup, in case your can prefix the pindex to the Description. Also if the pindex will always be two digits you can use first approach with <eval>, otherwise you would need to use second approach with dummy search. Similar to the dummy search in the answer approach with the lookup file.

[niketn]

@dbcase create a lookup for pindex numeric and human readable description.

Is the drilldown within dashboard or external?

[dbcase]

Hi Niketnilay,

I thought about the lookup table but I figured I'd end up in the same place as the problem above. The drilldown is within the dashboard.