Dashboards & Visualizations

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

Communicator

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>
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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>

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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>

View solution in original post

0 Karma

Communicator

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

0 Karma

Communicator

@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

0 Karma

SplunkTrust
SplunkTrust

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>
0 Karma

Communicator

@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>
0 Karma

SplunkTrust
SplunkTrust

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>

Communicator

Thank you!!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!