Hi,
I have a timechart that shows the status of tickets per month.
index="_internal"
| where _time >= $timepicker_earliest$ AND _time <= $timepicker_latest$
| timechart span=mon count by status
| fields - Resolved Closed
| join type=left _time
[ search index="_internal"
| where restored_at >=$timepicker_earliest$ AND restored_at <= $timepicker_latest$
| search current_ticket_state="Restored"
| eval _time = restored_at
| timechart span=mon count by status]
My _time field holds the ticket's creation date.
For tickets restored, it is based on timestamp restored_at, not _time, which is why I had to use join.
The chart's time range is set to 'Global' because of this complication.
In my drilldown, I am using $earliest$ and $latest$ tokens to segment the results into the proper time spans.
However, if I click on the legend, I encounter an error, "Error in 'where' command: The expression is malformed. A comparison term is missing.".
This is the search for my drilldown.
index="_internal"
| where _time >= $earliest$ AND _time <= $latest$
| dedup ticket_number
| eval Date = strftime(_time, "%B %d, %Y %H:%M:%S")
| table Date, ticket_name
Apparently, Splunk is not able to get my earliest and latest tokens because the Time Range for the chart is not set to any timepickers.
Is there any workaround to this?
@dojiepreji,
Try setting the token on drilldown as below and use them in the drilldown search
<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>
Drilldown search
index="aiam_itsm_ticket_beamsuntory" earliest >=$drilldown_earliest$ latest <=$drilldown_latest$
| dedup ticket_number
| eval Date = strftime(_time, "%B %d, %Y %H:%M:%S")
| table Date, ticket_name
Here is a run anywhere example for reference
<dashboard>
<label>TimeChart DrillDown</label>
<row>
<panel>
<table>
<search>
<query>index=_internal earliest=-15m@m|timechart span=5m count by sourcetype</query>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</option>
<option name="percentagesRow">false</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
<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>
</table>
</panel>
</row>
<row>
<panel>
<table>
<search>
<query>index=_internal earliest>=$drilldown_earliest$ latest<=$drilldown_latest$
|stats min(_time) as earliest,max(_time) as latest by sourcetype
|convert ctime(earliest) as Earliest,ctime(latest) as Latest</query>
<earliest>-15m</earliest>
<latest>now</latest>
</search>
<option name="count">10</option>
<option name="drilldown">none</option>
<option name="refresh.display">progressbar</option>
</table>
</panel>
</row>
</dashboard>
@dojiepreji refer to the post where timechart drilldown default token
has been identified as "not working as expected" https://answers.splunk.com/answers/587132/drilldown-pass-the-earliest-and-latest-from-a-time.html
The workaround suggested by @renjith.nair should work until the default token $latest$
is resolved.
It seems fine when clicking on bars on a chart, but when clicking on a legend, this shows up in the url: 'earliest=%24drilldown_earliest%24&latest=null'.