topic Re: Drilldown with Lookup table data in Dashboards & Visualizations

Drilldown with Lookup table data

MikeB — Fri, 19 Feb 2021 18:02:54 GMT

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!

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

Re: Drilldown with Lookup table data

to4kawa — Sat, 20 Feb 2021 05:35:10 GMT

| 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

Re: Drilldown with Lookup table data

MikeB — Mon, 22 Feb 2021 21:24:41 GMT

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!

Re: Drilldown with Lookup table data

to4kawa — Mon, 22 Feb 2021 23:41:40 GMT

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.

Re: Drilldown with Lookup table data

MikeB — Wed, 24 Feb 2021 17:21:28 GMT

@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!