I created a search that shows the count of outages by certain Apps over spans of last 90 days, broken up by last 30, 31-60, and 61-90. I want to configure the drilldown so I can click on the value for an app in one of these timespans and show a table with the App name and Alert message. The search operates how I want, just not sure about the drilldown capabilities.
Search:
index=... sourcetype=... App=* Status="Down" earliest=-90d latest=now()
| fields Alert App Status
| dedup Alert
| stats count(Alert) as "C" by App
| join type=left
[search index=... sourcetype=... App=* Status="Down" earliest=-60d latest=now()
| fields Alert App Status
| dedup Alert
| stats count(Alert) as "B" by App]
| join type=left
[search index=... sourcetype=... App=* Status="Down" earliest=-30d latest=now()
| fields Alert App Status
| dedup Alert
| stats count(Alert) as "A" by App]
| fillnull value=0 A B C
| table App A B C
| eval B=B - A
| eval AB=A + B
| eval CC=C - AB
| eval Sum=A + B + CC
| rename A as "30 Days", B as "31-60 Days", CC as "61-90 Days"
| fields App "30 Days" "31-60 Days" "61-90 Days"
Results Example:
App 30 Days 31-60 Days 61-90 Days Sum
App1 5 7 0 12
App2 2 4 10 16
My drilldown search will be something like:
index=... sourcetype=... App=* Status="Down"
| dedup Alert
| search App="$click.value2$"
| table App Alert
Is there a way to set a token to select the time range based on where I click? Any recommendations to get just the values for example the 7 events for "App1" "31-60 Days"?
Thank you. This was very close and helped me find the solution.
<set token="selected_app">$click.value$</set>
<eval token="$drilldown.earliest$>"case($click.name2$=="30 Days", "-30d", $click.name2$=="31-60 Days", "-60d", $click.name2$=="61-90 Days", "-90d", $click.name2$=="Sum", "-90d")</eval>
<eval token="$drilldown.latest$>"case($click.name2$=="30 Days", "now", $click.name2$=="31-60 Days", "-31d", $click.name2$=="61-90 Days", "-61d", $click.name2$=="Sum", "now")</eval>
I used case instead because I wasn't sure how to add the Sum column value with the if statement
Something like this:
<dashboard version="1.1">
<label>https://community.splunk.com/t5/Dashboards-Visualizations/How-to-configure-table-drilldown-with-time-elements/m-p/639686#M52303</label>
<row>
<panel>
<table>
<search>
<query>| makeresults
| eval _raw="
App 30 Days 31-60 Days 61-90 Days Sum
App1 5 7 0 12
App2 2 4 10 16"
| multikv forceheader=1
| fields - _* linecount
| table App * Sum
| rename *_* AS "*-* Days" 30 AS "30 Days"</query>
<earliest>-7d@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="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
<drilldown>
<eval token="drilldown.earliest">if($click.name2$=="30 Days", "-30d", "-" . replace(replace($click.name2$, "^.*-", ""), " Days", "") . "d")</eval>
<eval token="drilldown.latest">if($click.name2$=="30 Days", "now", "-" . tostring((tonumber(replace($click.name2$, "-.*", "")) - 1)) . "d")</eval>
<link target="_blank">search?q=index%3D%22YourIndexHere%22%20AND%20sourcetype%3D%22YourSourcetypeHere%22%20earliest%3D$drilldown.earliest$%20latest%3D$drilldown.latest$&earliest=-7d@h&latest=now</link>
</drilldown>
</table>
</panel>
</row>
</dashboard>
Thank you. This was very close and helped me find the solution.
<set token="selected_app">$click.value$</set>
<eval token="$drilldown.earliest$>"case($click.name2$=="30 Days", "-30d", $click.name2$=="31-60 Days", "-60d", $click.name2$=="61-90 Days", "-90d", $click.name2$=="Sum", "-90d")</eval>
<eval token="$drilldown.latest$>"case($click.name2$=="30 Days", "now", $click.name2$=="31-60 Days", "-31d", $click.name2$=="61-90 Days", "-61d", $click.name2$=="Sum", "now")</eval>
I used case instead because I wasn't sure how to add the Sum column value with the if statement
$click.name2$ will give you the name of the column clicked