Splunk Search

How to expand table based on click to the column

krusovice
Path Finder

Hello there,

I've generated a table with data as below showing the % of data computed for various type of products.

date_hour     country     product_A %      product_B %      product_C %
  1              KR         25%               30%              45%
  2              KR         50%               20%              30%

My requirement now is when user click on column "product_x %", the table should expand with more data on product_x, example as below:

    date_hour     country    product_A %   product_A_volume   product_A_cust    product_B %      product_C %
      1              KR         25%           3300                XYZ                30%              45%
      2              KR         50%           3400                ABC                20%              30%

This is very much similiar to Excel grouping function whereby user can group/ungroup with what they would like to see.

How can I achieve this requirement?

Thanks.

Tags (1)
0 Karma
1 Solution

niketn
Legend

@krusovice, try the following run anywhere dashboard based on sample data. The % column that you click will add the volume and cust columns for that product.

<dashboard>
  <label>Add Detail Columns to Table</label>
  <init>
    <set token="tokDetailFields">fields - product_A_* product_B_* product_C_*</set>
    <set token="fieldNames">date_hour, country, "product_A %", "product_B %", "product_C %"</set>
  </init>
  <row>
    <panel>
      <table>
        <search>
          <query>| makeresults
|  fields - _time
| eval data="1              KR         25%           3300                XYZ                30%     3300                XYZ         45% 3300                XYZ;2              KR         50%           3400                ABC                20%  3300                XYZ            30% 3300                XYZ;" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=" " 
| eval date_hour=mvindex(data,0), country=mvindex(data,1), "product_A %"=mvindex(data,2), product_A_volume=mvindex(data,3), product_A_cust=mvindex(data,4), "product_B %"=mvindex(data,5), product_B_volume=mvindex(data,6), product_B_cust=mvindex(data,7), "product_C %"=mvindex(data,8),product_C_volume=mvindex(data,9), product_C_cust=mvindex(data,10)
| fields - data
| $tokDetailFields$
| table date_hour country "*"</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <drilldown>
          <condition field="product_A %">
            <set token="tokDetailFields">fields - product_B_*  product_C_*</set>
          </condition>
          <condition field="product_B %">
            <set token="tokDetailFields">fields - product_A_*  product_C_*</set>
          </condition>
          <condition field="product_C %">
            <set token="tokDetailFields">fields - product_A_*  product_B_*</set>
          </condition>
          <condition>
            <!-- DO not respond to click on other fields-->
          </condition>
        </drilldown>
      </table>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@krusovice, try the following run anywhere dashboard based on sample data. The % column that you click will add the volume and cust columns for that product.

<dashboard>
  <label>Add Detail Columns to Table</label>
  <init>
    <set token="tokDetailFields">fields - product_A_* product_B_* product_C_*</set>
    <set token="fieldNames">date_hour, country, "product_A %", "product_B %", "product_C %"</set>
  </init>
  <row>
    <panel>
      <table>
        <search>
          <query>| makeresults
|  fields - _time
| eval data="1              KR         25%           3300                XYZ                30%     3300                XYZ         45% 3300                XYZ;2              KR         50%           3400                ABC                20%  3300                XYZ            30% 3300                XYZ;" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=" " 
| eval date_hour=mvindex(data,0), country=mvindex(data,1), "product_A %"=mvindex(data,2), product_A_volume=mvindex(data,3), product_A_cust=mvindex(data,4), "product_B %"=mvindex(data,5), product_B_volume=mvindex(data,6), product_B_cust=mvindex(data,7), "product_C %"=mvindex(data,8),product_C_volume=mvindex(data,9), product_C_cust=mvindex(data,10)
| fields - data
| $tokDetailFields$
| table date_hour country "*"</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <drilldown>
          <condition field="product_A %">
            <set token="tokDetailFields">fields - product_B_*  product_C_*</set>
          </condition>
          <condition field="product_B %">
            <set token="tokDetailFields">fields - product_A_*  product_C_*</set>
          </condition>
          <condition field="product_C %">
            <set token="tokDetailFields">fields - product_A_*  product_B_*</set>
          </condition>
          <condition>
            <!-- DO not respond to click on other fields-->
          </condition>
        </drilldown>
      </table>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

krusovice
Path Finder

Hi @niketnilay,

Thanks for the great help, the drilldown works perfectly for me. However, by using "fields" (I even tried changing to "table"), the column seem to be sorted as alphabetical order. Is that a way to "un-sort" it?

Thanks.

0 Karma

niketn
Legend

@krusovice are the details for drilldown already available from your existing query/existing data or is a new lookup/join is required to pull it from somewhere else?

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

krusovice
Path Finder

hi @niketnilay,

It is already available from the existing query. At first I have shown everything in one table, but due to too many column, user would like to see whether can hide/unhide the column based on what they want to view.

Thanks.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...