Splunk Search

Drilldown - pass the earliest and latest from a timechart

netanelm7
Path Finder

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!

0 Karma
1 Solution

emeelan_splunk
Splunk Employee
Splunk Employee

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>

View solution in original post

netanelm7
Path Finder

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!

0 Karma

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

netanelm7
Path Finder

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

0 Karma

netanelm7
Path Finder

I figured out why the problem happends.
It can happen if its only 1 hour or less and even if it's the last 10 hours .
Because i saw that every "JNL" is sampled a few times in each minute (and it's value stays the same), I cannot use count in a timechart, it will count duplicate values and will show a false result, so I decided to use "dedup _time", but i can only do it if I search for only 1 JNL, because if im searching for more then 1 and do "dedup _time", it will delete the other JNLs rows.
So i use appendcols and dedup every JNL by it's own.
And then i saw the if the first search before the first "appencols" didnt return any return (which mean it didnt find any result), the _time will go blank for the rest of the search.
Do you guys have any suggestion how to deal with that problem?

0 Karma

netanelm7
Path Finder

Can someone help me please?

0 Karma

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

netanelm7
Path Finder

I figured out why the problem happends.
It can happen if its only 1 hour or less and even if it's the last 10 hours .
Because i saw that every "JNL" is sampled a few times in each minute (and it's value stays the same), I cannot use count in a timechart, it will count duplicate values and will show a false result, so I decided to use "dedup _time", but i can only do it if I search for only 1 JNL, because if im searching for more then 1 and do "dedup _time", it will delete the other JNLs rows.
So i use appendcols and dedup every JNL by it's own.
And then i saw the if the first search before the first "appencols" didnt return any return (which mean it didnt find any result), the _time will go blank for the rest of the search.
Do you guys have any suggestion how to deal with that problem?

0 Karma

netanelm7
Path Finder

I want the rows of the timechart to be hours (row1=2017-12-13 08:00, row2=2017-12-13 09:00 for example), i need the "span=1h"..
Even if the span is static at 1h, if i want to display 2 hours, why would i see blank values in the _time column?

0 Karma

I thought your issue arises only when time selected is less than and hour. If you remove span from timechart, span will be adjusted by default on its own. Alternatively you can add a dropdown with span of your choice to be overridden for 60 min or less.

When I tell him to show me the last hour or less (30 minutes for example), it shows me the _time column empty

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

emeelan_splunk
Splunk Employee
Splunk Employee

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>

rjthibod
Champion

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.

0 Karma

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

@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$
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

netanelm7
Path Finder

Hi niketnilay,

  1. 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

  2. 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)..

  3. Yes i have a _time field, im even outputing it in the query.

Thank you for your time!

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

HI

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

0 Karma

netanelm7
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Can you please share your sample xml??

0 Karma

netanelm7
Path Finder

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

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

netanelm7
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...