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!
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>
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!
@netanelm7, can you give just the drilldown code that you have right now?
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>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>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>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>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
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?
Can someone help me please?
@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.
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?
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?
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
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>
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.
@emeelan [Splunk], thanks for the workaround. Do post an update here once the earliest and latest tokens are fixed for table drilldown event.
@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$
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!
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
Hi, Tried it, didn't work. still thank you 🙂
Can you please share your sample xml??
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>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>
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>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
Thank you!!! it's working, what is the _span variable?