How to remove fields from a search that will be used in dashboard drill down panel in dashboard.

[GersonGarcia]

All,
I am running this search to build a drilldown panel in a dashboard:

index=os "invoked oom-killer:" 
| eval stime=_time 
| eval tearliest=relative_time(stime,"-1") | eval tlatest=relative_time(stime,"+1") 
| stats by _time stime tearliest tlatest
| fields _time stime tearliest tlatest

The new "evaled" fields stime tearliest tlatest will be used when we click in the line in the result panel. This search will generate the following result:

     _time                   stime          tearliest             tlatest
1   2017-07-06 09:50:42     1499359842  1499359841.000000   1499359843.000000
2   2017-07-09 21:16:32     1499660192  1499660191.000000   1499660193.000000
3   2017-07-09 21:16:35     1499660195  1499660194.000000   1499660196.000000
4   2017-07-09 21:16:36     1499660196  1499660195.000000   1499660197.000000
5   2017-07-10 15:40:50     1499726450  1499726449.000000   1499726451.000000 

When I click on one of the lines, drilldown will build another panel that contains all events between tearliest and tlatest:

index=os earliest=$tearliest$ latest=$tlatest$

This is working very well, but the result of the first search is ugly and it is causing confusion with my users.

How can I remove stime tearliest tlatest from the search result, without interfere in the drill down? The simple XML of my dashboard is:

    <panel>
      <title>oom-killer invoked</title>
      <table>
        <title>Between $timer.earliest$ and $timer.latest$</title>
        <search>
          <query>index=os "invoked oom-killer:" 
| eval stime=_time 
| eval tearliest=relative_time(stime,"-1") | eval tlatest=relative_time(stime,"+1") 
| stats by _time stime tearliest tlatest
| fields _time stime tearliest tlatest
          <earliest>$timer.earliest$</earliest>
          <latest>$timer.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">50</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">true</option>
        <option name="totalsRow">false</option>
        <option name="wrap">false</option>
        **<drilldown>
          <eval token="tearliest">'row.tearliest'</eval>
          <eval token="tlatest">'row.tlatest'</eval>
        </drilldown>**
      </table>
    </panel>



    <panel depends="$tearliest$,$tlatest$">
      <title>All events in timerange</title>
      <event>
        <search>
          <query>index=os earliest=$tearliest$ latest=$tlatest$</query>
          <earliest></earliest>
          <latest></latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="list.drilldown">full</option>
        <option name="list.wrap">1</option>
        <option name="maxLines">5</option>
        <option name="raw.drilldown">full</option>
        <option name="rowNumbers">0</option>
        <option name="table.drilldown">all</option>
        <option name="table.sortDirection">asc</option>
        <option name="table.wrap">1</option>
        <option name="type">list</option>
      </event>
    </panel>

Thank you,

Gerson

[niketn · Accepted solution]

@GersonGarcia, I think stime field in your query is just duplicate data. You should perform evals after transforming query aggregates results to smaller buckets. You have also not mentioned what exact stats you need to aggregate. Since your fields command in the final pipe just retains time related field it seems confusing.
In your example relative_time is missing time unit which implies -1 will reduce time by 1 second and +1 will increase by 1 second. Do you really want this? It is always better to define the unit of time as well. like 1h implies 1 hour etc.
Do you want to show a table only with _time?
Do you need stats? If not you should use just one like count. Just typing stats will calculate all stats and then you seem to drop all of them.

You have two ways to do this. If you just want to show _time field in the table, <fields>_time</fields> Simple XML code is required in both examples.

Option 1: Use <fields> Simple XML option to show only required fields in the table. Other fields are hidden but still available for drilldown. Following example retains just _time field for display since I am not sure whether you need fields from stats to be displayed.

 index=os "invoked oom-killer:" 
 | stats count by _time
 | eval tearliest=relative_time(stime,"-1h") 
 | eval tlatest=relative_time(stime,"+1h") 

Your drilldown code remains the same:

     <fields>_time</fields>
     <drilldown>
       <eval token="tearliest">$row.tearliest$</eval>
       <eval token="tlatest">$row.tlatest$</eval>
     </drilldown>**

Option 2: Just have the _time field passed to drilldown and use <eval> to calculate "-1h" and "+1h" relative_time.

 index=os "invoked oom-killer:" 
 | stats count by _time

Drilldown code changes to use eval to compute tearliest and tlatest through relative_time() function (fields is required if you just want to show _time field):

    <fields>_time</fields>
    <drilldown>
      <eval token="tearliest">relative_time($click.value$,"-1h")</eval>
      <eval token="tlatest">relative_time($click.value$,"+1h")</eval>
    </drilldown>

PS: Ideally since your table is depicting time series data, you should consider timechart command with span=1h or similar as per your need instead of stat. Even for stats you can define span (or buckets of time for aggregation of results). For 1 hour you can add the following before stats command

| bin _time span=1h
| stats count by _time

[DalJeanis]

@GersonGarcia - @niketn's advice is spot-on. The only thing you need to display is the exact _time, the rest can be hidden, and there's no reason at all to copy _time to stime, since the formatted one meets the need.

I would add that the title for _time should be something like "TIme of Oom-killer Invocation" , or the title of the panel might be "Select Desired TIme of Oom-Killer Invocation"

Also, since _time is the only thing you are keeping from the records, you can do a table and dedup commands right at the beginning to eliminate all the other dead weight fields and events, which means you don't need stats at all. And, since we're just adding/subtracting exactly 1 second in epoch time, we can just use straight math rather than relative_time. It only saves a couple of machine instructions, but it's the principle of the thing.

Here's your revised query...

<query>index=os "invoked oom-killer:" 
| dedup _time 
| table _time 
| eval tearliest=_time-1 
| eval tlatest=_time+1 

[GersonGarcia]

@DalJeanis and @niketnilay, thank you very much. The tag in Simple XML was the trick. I also replace the stat command by table (duhhh). But I don't want dedup the output since each line is one process analyzed by oom-killer and it may be for different hosts.
Another point is I am adding and subtracting 1s of the 2nd search because earliest and latest with the same value returns no data:

index=os earliest=1499726450 latest=1499726450

No results found. 

[niketn]

@GersonGarcia, has the solution worked for you. Please accept the answer if it has. If not please let us know what is still not working.