How to configure table drilldown with time elements?

[michaeler]

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"?

[woodcock]

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$&amp;earliest=-7d@h&amp;latest=now</link>
        </drilldown>
      </table>
    </panel>
  </row>
</dashboard>

[michaeler · Accepted solution]

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

[ITWhisperer]

$click.name2$ will give you the name of the column clicked