Dashboards & Visualizations

Drilldown with Lookup table data

MikeB
Loves-to-Learn

Hello! So I have a panel in my dashboard that pulls in data from a lookup table to display the contents of three separate products I have in a line chart. This line chart just counts and sums up the totals for each product for each day. I want to be able to click on any of the three products for any given day and have that populate the individual event records of those contents for that product on that day. I've been trying to work with the documentation and the example dashboard on Splunk but can't seem to get it to work. I've included an example table from my lookup below.

Any feedback would be great. Thanks!

datecountusd_totalproduct_name
161218080024240product_1
161226720018360product_2
161235360030900product_3
161244000011110product_1
161252640011220product_2
1612612800371110product_3
161269920043430product_1
161278560027540product_2
161287200015450product_3
161295840047470product_1
161304480012240product_2
1613131200461380product_3
Labels (2)
0 Karma

to4kawa
SplunkTrust
SplunkTrust
| makeresults
| eval _raw="date	count	usd_total	product_name
1612180800	24	240	product_1
1612267200	18	360	product_2
1612353600	30	900	product_3
1612440000	11	110	product_1
1612526400	11	220	product_2
1612612800	37	1110	product_3
1612699200	43	430	product_1
1612785600	27	540	product_2
1612872000	15	450	product_3
1612958400	47	470	product_1
1613044800	12	240	product_2
1613131200	46	1380	product_3"
| multikv forceheader=1
| rename date as _time
| fields - _raw linecount
| stats sum(count) as count by  _time	usd_total	product_name
0 Karma

MikeB
Loves-to-Learn

Thank you for such a quick reply @to4kawa ! I have a couple questions if you don't mind. If my lookup table didn't just have static data like I showed before but also would have data constantly adding to it how would that eval be written? Also would I write this query into a new panel to have the drilldown results show up there or would I append this under my existing query in the original panel? 

Thanks!

0 Karma

to4kawa
SplunkTrust
SplunkTrust

eval ? you don't need it.

sample:

<dashboard>
  <label>lookup_drilldown</label>
  <init>
    <unset token="viz"></unset>
  </init>
  <search id="lookup">
    <query>| inputlookup geo_attr_countries.csv</query>
    <earliest>0</earliest>
  </search>
  <row>
    <panel>
      <table>
        <search base="lookup">
          <query>
| eval first_letter=substr(iso2,1,1)
| stats count values(iso2) as iso2 by first_letter delim=","
| nomv iso2</query>
        </search>
        <option name="count">50</option>
        <option name="drilldown">cell</option>
        <drilldown>
          <set token="first_letter">$click.value$</set>
          <set token="viz">1</set>
        </drilldown>
      </table>
    </panel>
  </row>
  <row>
    <panel depends="$viz$">
      <table>
        <search base="lookup">
          <query>search iso2="$first_letter$*"
| table iso2 country</query>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</dashboard>

You can use drilldown if you like.

0 Karma

MikeB
Loves-to-Learn

@to4kawa So I guess I need to wrap my head around the idea of these drilldowns better. If I wanted to have the user able to click on a given datapoint on a certain day on a line chart to drilldown and display the event detail data it contains I should first set a token for the token to click based on the category of the datapoint itself? Then the drilldown query will run a separate query to search the lookup table and then use the search command with the category I want it to drilldown on with the newly created token? 

Thanks again for your help!

0 Karma