Splunk Search

How to expand table based on click to the column

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

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

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!