topic Re: Drilldown - pass the earliest and latest from a timechart in Splunk Search

Drilldown - pass the earliest and latest from a timechart

netanelm7 — Tue, 29 Sep 2020 16:30:47 GMT

Hi everyone,

Im having a problem passing the earliest and latest from a timechart.
On the main graph, im showing a table with hourly interval which shows some counter for each column.
I wanted to pass the selected column and to show it on a different timechart with minutely interval.

the tokens I configured on the main graph are:
jnl_mb_counter = $click.name2$
jnl_mb_earliest = $earliest$
jnl_mb_latest = $latest$

The column is passing perfectly, but the time is always the entire time (if i have 3 hours - which are 3 rows in the main graph's table, no matter which hour i choose, i get the drilldown timechart with the entiretime)

The drilldown query is:
index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA earliest=$jnl_mb_earliest$ latest=$jnl_mb_latest$ | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | search IDs=$jnl_mb_counter$ | timechart span=1m avg(transfer_in_MB) as "$jnl_mb_counter$ Transfer"

Thank you very much!

Re: Drilldown - pass the earliest and latest from a timechart

kamlesh_vaghela — Mon, 30 Oct 2017 09:19:13 GMT

Can you please try below XML??

I have set earliest & latest in xml..

<dashboard>
  <label>CCC</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal | timechart span=1h count by sourcetype</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">all</option>
        <drilldown>
          <set token="name2">$click.name2$</set>
          <set token="clicked_e">$earliest$</set>
          <set token="clicked_l">$latest$</set>
        </drilldown>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <title>$name2$</title>
        <search>
         <query>index=_internal sourcetype=$name2$| timechart span=1m count</query>
          <earliest>$clicked_e$</earliest>
          <latest>$latest$</latest>
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">none</option>
      </chart>
    </panel>
  </row>

</dashboard>

Thanks

Re: Drilldown - pass the earliest and latest from a timechart

netanelm7 — Mon, 30 Oct 2017 09:34:55 GMT

Hi, Tried it, didn't work. still thank you 🙂

Re: Drilldown - pass the earliest and latest from a timechart

kamlesh_vaghela — Mon, 30 Oct 2017 09:38:27 GMT

Can you please share your sample xml??

Re: Drilldown - pass the earliest and latest from a timechart

netanelm7 — Mon, 30 Oct 2017 10:05:00 GMT

sure:

<panel>
  <table>
    <title>JNL Preformance Table (Shows the number of times a JNLs MB/s is greater then 450 MB)</title>
    <search>
      <query>index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB&gt;550 | timechart span=1h count(transfer_in_MB) by IDs</query>
      <earliest>$timeField1.earliest$</earliest>
      <latest>$timeField1.latest$</latest>
    </search>
    <option name="drilldown">cell</option>
    <drilldown>
      <set token="jnl_mb_counter">$click.name2$</set>
      <set token="jnl_mb_earliest">$earliest$</set>
      <set token="jnl_mb_latest">$latest$</set>
    </drilldown>
  </table>
</panel>
<panel depends="$jnl_mb_counter$">
  <chart>
    <title>Drilldown Selected JNL MB</title>
    <search>
      <query>index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA earliest=$jnl_mb_earliest$ latest=$jnl_mb_latest$ | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | search IDs=$jnl_mb_counter$ | timechart span=1m avg(transfer_in_MB) as "$jnl_mb_counter$ Transfer"</query>
      <earliest>$jnl_mb_earliest$</earliest>
      <latest>$jnl_mb_latest$</latest>
    </search>
    <option name="charting.chart">line</option>
    <option name="charting.chart.nullValueMode">connect</option>
    <option name="charting.drilldown">none</option>
  </chart>
</panel>

Re: Drilldown - pass the earliest and latest from a timechart

niketn — Mon, 30 Oct 2017 10:19:11 GMT

@netanelm7, your tokens seem fine. Can you share the query for the table from which you are doing the drilldown? Do you have _time field in the table from which you need to drilldown?

PS: Looking at your query, your drilldown search will work better if IDs=$jnl_mb_counter$ is added to the base search rather than a pipe later on with | search i.e.

index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA earliest=$jnl_mb_earliest$ latest=$jnl_mb_latest$ IDs=$jnl_mb_counter$

Re: Drilldown - pass the earliest and latest from a timechart

netanelm7 — Tue, 29 Sep 2020 16:30:53 GMT

Hi niketnilay,

I've posted my entire XML below, but sure, that's my main query:
index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB>550 | timechart span=1h count(transfer_in_MB) by IDs
I've used EVAL after the base search so i havent found anyway to include the IDs=$jnl_mb_counter$ for example (it doenst know what is IDs in the base search)..
Yes i have a _time field, im even outputing it in the query.

Thank you for your time!

Re: Drilldown - pass the earliest and latest from a timechart

kamlesh_vaghela — Tue, 29 Sep 2020 16:30:56 GMT

Hi
Can you please try below XML??

 <panel>
   <table>
     <title>JNL Preformance Table (Shows the number of times a JNLs MB/s is greater then 450 MB)</title>
     <search>
       <query>index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB&gt;550 | timechart span=1h count(transfer_in_MB) by IDs | eval start_time=_time , end_time=_time+_span</query>
       <earliest>$timeField1.earliest$</earliest>
       <latest>$timeField1.latest$</latest>
     </search>
     <option name="drilldown">cell</option>
     <drilldown>
       <set token="jnl_mb_counter">$click.name2$</set>
       <set token="jnl_mb_earliest">$row.start_time$</set>
       <set token="jnl_mb_latest">$row.end_time$</set>
     </drilldown>
   </table>
 </panel>
 <panel depends="$jnl_mb_counter$">
   <chart>
     <title>Drilldown Selected JNL MB</title>
     <search>
       <query>index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | search IDs=$jnl_mb_counter$ | timechart span=1m avg(transfer_in_MB) as "$jnl_mb_counter$ Transfer"</query>
       <earliest>$jnl_mb_earliest$</earliest>
       <latest>$jnl_mb_latest$</latest>
     </search>
     <option name="charting.chart">line</option>
     <option name="charting.chart.nullValueMode">connect</option>
     <option name="charting.drilldown">none</option>
   </chart>
 </panel>

Right now you will find 2 extra column in table. start_time and end_time.

Thanks

Re: Drilldown - pass the earliest and latest from a timechart

netanelm7 — Mon, 30 Oct 2017 11:45:20 GMT

Thank you!!! it's working, what is the _span variable?

Re: Drilldown - pass the earliest and latest from a timechart

rjthibod — Mon, 30 Oct 2017 11:51:22 GMT

The _span field indicates the bin or bucket size from your timechart command, in your case 1h or 3600 seconds. Anytime you use a SPL function that performs bin'ing, the hidden _span field is present. That field tells Splunk how to space out data on the x-axis when you chart timecharts.

Please "like" or "upvote" my comment or you can turn it into an answer and accept it. Either way.

Re: Drilldown - pass the earliest and latest from a timechart

netanelm7 — Mon, 30 Oct 2017 11:58:15 GMT

thank you!, i upvoted it, when can i click "accept" on it?

Re: Drilldown - pass the earliest and latest from a timechart

kamlesh_vaghela — Mon, 30 Oct 2017 12:04:12 GMT

Click on Accept link of Answer.
Scroll up to the first comment.

Re: Drilldown - pass the earliest and latest from a timechart

emeelan_splunk — Mon, 30 Oct 2017 21:33:02 GMT

Hi All,
Here's another workaround given to me by one of our intrepid engineers that might work better than the one already posted:

<drilldown>
          <eval token="drilldown.earliest">strptime($row._time$,"%Y-%m-%d %H:%M:%S")</eval>
          <eval token="drilldown.latest">strptime($row._time$,"%Y-%m-%d %H:%M:%S") + $row._span$</eval>
        </drilldown>

Re: Drilldown - pass the earliest and latest from a timechart

rjthibod — Tue, 31 Oct 2017 19:28:43 GMT

This is the correct answer. My previous (now deleted) answer was incorrect in that I thought row._time would return the epoch seconds and not the string version of time.

Re: Drilldown - pass the earliest and latest from a timechart

niketn — Tue, 31 Oct 2017 19:42:36 GMT

@emeelan [Splunk], thanks for the workaround. Do post an update here once the earliest and latest tokens are fixed for table drilldown event.

Re: Drilldown - pass the earliest and latest from a timechart

netanelm7 — Sun, 05 Nov 2017 08:59:43 GMT

I still have an issue.
The code is working but when the time is above 1h..
When I tell him to show me the last hour or less (30 minutes for example), it shows me the _time column empty.. and the drilldown doesnt work/
My guess is that it doesnt know the earliest or latest..

Thank you for your answer!

Re: Drilldown - pass the earliest and latest from a timechart

niketn — Mon, 06 Nov 2017 02:38:54 GMT

@netanelm7, can you give just the drilldown code that you have right now?

Re: Drilldown - pass the earliest and latest from a timechart

netanelm7 — Thu, 09 Nov 2017 12:57:36 GMT

Sure:

  <table>
    <title>JNL Preformance Table (Shows the number of times a JNLs MB/s is greater then 450 MB)</title>
    <search>
      <query>index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB&gt;450 | search IDs="JNL000" | dedup _time | timechart span=1h count(transfer_in_MB) by IDs | appendcols [search index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB&gt;450 | search IDs="JNL00A" | dedup _time | timechart span=1h count(transfer_in_MB) by IDs] | appendcols [search index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB&gt;450 | search IDs="JNL014" | dedup _time | timechart span=1h count(transfer_in_MB) by IDs] | appendcols [search index=storage_18037 sourcetype=csvRotemA_JNL_SUMMARY NOT DATETIME host=RotemA | eval transfer_in_MB=M_JNL_ASYNC_XFER_RATE/1024,IDs="JNL".JOURNAL_ID | where transfer_in_MB&gt;450 | search IDs="JNL01E" | dedup _time | timechart span=1h count(transfer_in_MB) by IDs] | fillnull value="0" JNL000 | fillnull value="0" JNL00A | fillnull value="0" JNL014 | fillnull value="0" JNL01E | eval start_time=_time, end_time=_time+_span</query>
      <earliest>$timeField1.earliest$</earliest>
      <latest>$timeField1.latest$</latest>
    </search>
    <option name="drilldown">cell</option>
    <drilldown>
      <set token="jnl_mb_counter">$click.name2$</set>
      <eval token="drilldown.earliest">strptime($row._time$,"%Y-%m-%d %H:%M:%S")</eval>
      <eval token="drilldown.latest">strptime($row._time$,"%Y-%m-%d %H:%M:%S") + $row._span$</eval>
    </drilldown>
  </table>
</panel>

Now it worked for some reason (i managed to see 1 line below 1h range... but sometimes it doesnt (and i see the _time column empty), weird

Re: Drilldown - pass the earliest and latest from a timechart

netanelm7 — Sun, 03 Dec 2017 12:49:42 GMT

Can someone help me please?

Re: Drilldown - pass the earliest and latest from a timechart

niketn — Sun, 03 Dec 2017 13:25:02 GMT

@netanelm7, this is because your timechart has static span defined for 1 hour

timechart span=1h

You can remove the span=1h from timechart command if you want the span to be less than an hour. Please try out and confirm.