How to pass triggered alert timestamp to earliest and latest drilldown tokens?

[johnward4]

I'm trying to pass the timestamp from a field called alert_time in my main panel to the earliest and latest drilldown tokens for secondary panels in my dashboard. The primary panel uses a summary index for fired alerts and ini the secondary panels I'd like to pass the $click.value$ for the host and the alert_time - 1800 for $drilldown.earliest$ and alert_time + 1800 for $drilldown.latest$

Here is my current dashboard
Any suggestions are much appreciated to try to accomplish what I'm trying to show

<row>
    <panel>
      <table>
        <search>
          <query>index=alerts source="High CPU Usage > 95% - Infrastructure"
| eval alert_time_epoch = strptime('alert_time', "%m/%d/%Y %H:%M:%S") 
| rename orig_host as Host, avg_cpu_used_percent as "Avg % CPU Used" 
| table Host, "Avg % CPU Used", alert_time, alert_time_epoch
| sort - alert_time</query>
          <earliest>0</earliest>
          <latest></latest>
          <refresh>10m</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <drilldown>
          <set token="selectHost">$click.value$</set>
          <eval token="drilldown.earliest">($selectedalert_time$-1800)</eval>
          <eval token="drilldown.latest">$selectedalert_time$+1800</eval>
          <set token="showAlertDD">1</set>
        </drilldown>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table depends="$showAlertDD$">
        <title>Drilldown CPU - $selectHost$ ($drilldown.earliest$) ($drilldown.latest$)</title>
        <search>
          <query>| multisearch 
                    [search index=os host=$Host$ sourcetype=cpu cpu="all" |eval cpu_used_percent=100-PercentIdleTime]
                    [search index=perfmon host=$Host$ source="Perfmon:CPU" counter="% * Time" 
                    |eval user_cpu=if(counter=="% User Time",round(Value,2),""), proc_cpu=if(counter=="% Processor Time",round(Value),""), cpu_used_percent=user_cpu+proc_cpu
                    |eval earliest=$selected_time$-1800, latest=$selected_time$+1800]
                  |lookup infrastructure_servers host
                  |where state=1 
                  | search host!="irvetl01"
                  |stats avg(cpu_used_percent) as avg_cpu_used_percent by host
                  |eval avg_cpu_used_percent=round(avg_cpu_used_percent,2)
                  |where avg_cpu_used_percent>95</query>
          <earliest>$drilldown.earliest$</earliest>
          <latest>$drilldown.latest$</latest>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="rowNumbers">false</option>
        <option name="wrap">true</option>
        <drilldown>
          <unset token="showAlertDD"></unset>
        </drilldown>
      </table>
    </panel>

[renjith_nair · Accepted solution]

Hi @johnward4,

This is a run anywhere example . Lets know if we could be of further assistance ,

<dashboard>
  <label>Test1</label>
  <row>
    <panel>
      <title>Original : $original$ , Earliest = $drilldown_earliest$,Latest : $drilldown_latest$</title>
      <table>
        <search>
          <query>index=_* sourcetype="splunkd*"|table _time,host,source,sourcetype|head 10</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">cell</option>
        <drilldown>
          <set token="original">$click.value$</set>
          <eval token="drilldown_earliest">$click.value$-1800</eval>
          <eval token="drilldown_latest">$click.value$+1800</eval>
          <eval token="host">$row.host$</eval>
        </drilldown>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table>
        <title>Earliest :$drilldown_earliest$ , Latest :$drilldown_latest$</title>
        <search>
          <query>index=_internal host=$host$ earliest=$drilldown_earliest$ latest=$drilldown_latest$|table _time,host,source,sourcetype|eventstats earliest(_time) as Earliest,latest(_time) as Latest</query>
          <earliest>$drilldown_earliest$</earliest>
          <latest>$drilldown_latest$</latest>
        </search>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>
</dashboard>

[johnward4]

Exactly the example I was looking for, thank you!!

[johnward4]

@renjith.nair is there a way to convert the epoch $drilldown_earliest$ and $drilldown_latest$ to human readable format in the panel's title? Not pressing, just curious

[renjith_nair]

Hi @johnward4,
Yep, you can do it. Just replace the second row of above with this and adjust the time format according to your preference

           <row>
             <panel>
               <table>
                 <title>Earliest :$formatted_earliest$ , Latest :$formatted_latest$</title>
                 <search>
                   <query>index=_internal host=$host$ earliest=$drilldown_earliest$ latest=$drilldown_latest$|eval Earliest=strftime($drilldown_earliest$,"%d-%m-%Y %H:%M:%S")|eval Latest=strftime($drilldown_latest$,"%d-%m-%Y %H:%M:%S")</query>
                   <earliest>$drilldown_earliest$</earliest>
                   <latest>$drilldown_latest$</latest>
                   <done>
                     <set token="formatted_earliest">$result.Earliest$</set>
                     <set token="formatted_latest">$result.Latest$</set>
                   </done>
                 </search>
                 <option name="drilldown">none</option>
               </table>
             </panel>
           </row>

[johnward4]

@renjith.nair I can't get that format to work...

My main panel shows fired alerts off a alert that I pipe in a | collect index=alerts so I'm able to save the details of my alerts. The alert timestamp isn't extracted automatically so I'm using a regex to extract to the field named "alert_time" with the format of

07/13/2018 06:19:30

I'm also using the following eval to convert the alert_time to epoch

| eval alert_time_epoch = strptime('alert_time', "%m/%d/%Y %H:%M:%S")

Here's my test XML trying to use the formatted method you suggested

  <label>Fired Alerts Drilldown</label>
  <row>
    <panel>
      <table>
        <title>Drilldown - $host$ Earliest :$formatted_earliest$ , Latest :$formatted_latest$</title>
        <search>
          <query>index=alerts source="High CPU Usage &gt; 95% - Infrastructure"
| eval alert_time_epoch = strptime('alert_time', "%m/%d/%Y %H:%M:%S") 
| rename orig_host as host, avg_cpu_used_percent as "Avg % CPU Used" 
| table alert_time_epoch, alert_time, host, "Avg % CPU Used"
| sort - alert_time</query>
          <earliest>0</earliest>
          <latest></latest>
          <refresh>10m</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="count">15</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <drilldown>
          <set token="original">$click.value$</set>
          <eval token="drilldown_earliest">$click.value$-7200</eval>
          <eval token="drilldown_latest">$click.value$+300</eval>
          <eval token="host">$row.host$</eval>
          <set token="showAlertDD">1</set>
        </drilldown>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table depends="$showAlertDD$">
        <title>CPU</title>
        <search>
          <query>|multisearch
   [search index=os host=$host$ sourcetype=cpu cpu="all" earliest=$drilldown_earliest$ latest=$drilldown_latest$
   |eval cpu_used_percent=100-PercentIdleTime]
   [search index=perfmon host=$host$ source="Perfmon:CPU" counter="% User Time"  OR counter="% Processor Time" earliest=$drilldown_earliest$ latest=$drilldown_latest$ 
    |eval cpu_user_percent=if(counter=="% User Time",round(Value,2),""), cpu_load_percent=if(counter=="% Processor Time",round(Value),""), cpu_used_percent=user_cpu+proc_cpu]
    |eval Earliest=strftime($drilldown_earliest$,"%m/%d/%Y %H:%M:%S")|eval Latest=strftime($drilldown_latest$,"%m/%d/%Y %H:%M:%S")
|eval lhost=upper(host)
|bin _time span=10m
|stats avg(cpu_user_percent) as user_cpu, avg(cpu_load_percent) as proc_cpu by host, _time
|stats avg(user_cpu) as user_cpu, avg(proc_cpu) as proc_cpu, sparkline(avg(user_cpu)) as "% User CPU Load", sparkline(avg(proc_cpu)) as "% Processor CPU Load" by host
|eval  user_cpu=round(user_cpu,2), proc_cpu=round(proc_cpu,2)
|rename user_cpu as "% Average User CPU Load", proc_cpu as "% Average Processor Load"
| sort - "% Average User CPU Load"</query>
          <earliest>$drilldown_earliest$</earliest>
                    <latest>$drilldown_latest$</latest>
                    <done>
                      <set token="formatted_earliest">$result.Earliest$</set>
                      <set token="formatted_latest">$result.Latest$</set>
                    </done>
                  </search>
                  <option name="drilldown">none</option>
                </table>
              </panel>

[renjith_nair]

Hi @johnward4,

Only one possibility I could think of is your stats commands where Earliest and Latest are not part of stats and as a result of it, the fields are not part of the final result set and hence in the token. Either you can add these fields to the stats or we create a dummy panel in between just to convert the date. Replace your first section with this and let me know if it works.

  <row>
    <panel>
      <table>
        <title>Drilldown - $host$ Earliest :$formatted_earliest$ , Latest :$formatted_latest$</title>
        <search>
          <query>index=alerts source="High CPU Usage &gt; 95% - Infrastructure"
 | eval alert_time_epoch = strptime('alert_time', "%m/%d/%Y %H:%M:%S") 
 | rename orig_host as host, avg_cpu_used_percent as "Avg % CPU Used" 
 | table alert_time_epoch, alert_time, host, "Avg % CPU Used"
 | sort - alert_time</query>
          <earliest>-5m</earliest>
          <latest>now</latest>
          <refresh>10m</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="count">15</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="refresh.display">progressbar</option>
        <drilldown>
          <set token="original">$click.value$</set>
          <eval token="drilldown_earliest">$click.value$-7200</eval>
          <eval token="drilldown_latest">$click.value$+300</eval>
          <set token="host">$row.host$</set>
          <set token="showAlertDD">1</set>
        </drilldown>
      </table>
    </panel>
    <panel depends="$I_am_always_a_hidden_panel_do_not_set_my_token_and_I_am_ok_with_it$">
      <search>
        <query>
          |stats count|eval Earliest=strftime($drilldown_earliest$,"%d-%m-%Y %H:%M:%S")|eval Latest=strftime($drilldown_latest$,"%d-%m-%Y %H:%M:%S")
        </query>
          <done>
            <set token="formatted_earliest">$result.Earliest$</set>
            <set token="formatted_latest">$result.Latest$</set>
          </done>        
      </search>
    </panel>
  </row>

[johnward4]

Thank you!!