Dashboards & Visualizations

Drilldown with Lookup table data

MikeB
Path Finder

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
1 Solution

to4kawa
Ultra Champion
| 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

View solution in original post

to4kawa
Ultra Champion
| 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

MikeB
Path Finder

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
Ultra Champion

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
Path Finder

@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
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...