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>
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>
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>
Exactly the example I was looking for, thank you!!
@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
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>
@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 > 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>
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 > 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>
Thank you!!