How to add drilldown to connect bar chart with events table?

[POR160893]

Hi,

In Splunk, I have a dashboard with 2 separate searches. I need to connect both these searches such that the first search has a drilldown that, on click by the user, it runs the 2nd search. The first search finds the number of "dv_parent" events for last quarter and it outputs a bar chart of the number of "dv_parent" events per quarter. The 2nd search shows the individual events per "dv_parent" events for last quarter. Currently, both searches are not connected and I need to connect both with the drilldown. Here is the XML for the dashbord:

<form>
<label>FCR Peer Review Dashboard</label>
<fieldset submitButton="false">
<input type="time" token="timeframe">
<label></label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>
<input type="text" token="assign_tok">
<label>Name Assigned to Ticket</label>
<default>*</default>
<initialValue>*</initialValue>
<prefix>businessemail ="</prefix>
<suffix>"</suffix>
</input>
</fieldset>
<row>
<panel>
<title>Number of FCR changes per quarter</title>
<chart>
<search>
<query>index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes." earliest=-3mon@mon latest=@mon
| stats latest(*) as * by dv_parent
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+12)/4))
| stats count by Quarter</query>
<earliest>-3m@y</earliest>
<latest>now</latest>
</search>
<option name="charting.chart">column</option>
<option name="charting.drilldown">none</option>
<option name="refresh.display">progressbar</option>
</chart>
</panel>
</row>
<row>
<panel>
<table>
<title>FCR Peer Review</title>
<search>
<query>index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" dv_state="Closed Complete" description="Request for Dell firewall changes."
| table _time, description, dv_parent, dv_state, dv_assigned_to
| dedup dv_parent
| eval assigned_user=round(random() % 74, 0)+1
| lookup id_lookup.csv businessemail as businessemail
| lookup temp_id.csv dv_parent OUTPUT dv_assigned_to as already_assigned
| eval assigned_user=coalesce(already_assigned, user)</query>
<earliest>-1y@y</earliest>
<latest>now</latest>
</search>
<option name="drilldown">none</option>
<option name="refresh.display">progressbar</option>
</table>
</panel>
</row>
</form>

Can you please help by altering this dashboard with the necessary drilldown with the "dv_parent" fields as token for the drilldown????


Many thanks,

[woodcock]

It cannot be done because you do not have "dv_parent" as a field in the first search because of the 2nd stats.

Be aware that because you are redefining "time" that you should be using a MUCH wider timepicker range that you desired goal (assuming that _time != dv_sys_updated_on), otherwise, some values of dv_sys_updated_on will fall outside of your timepicker and the results will be inaccurate.

Also change this:
| stats latest(*) as * by dv_parent
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+12)/4))
| stats count by Quarter

To this:
| stats first(*) AS * by dv_parent
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| bin _time span=1q
| stats count by _time

Make SURE that you remove "| stats latest(*) as * by dv_parent" because this is slowing your search WAY down!