I'm building a dashboard that shows a stacked column chart of different items sold in the last 6 months (using timechart
). For example, in Nov, there would be 2 Item A, 3 Item B, etc in the column for Nov. Then when I click on the block for Item A, I would get a table that shows the details of the 2 Item A in Nov only (using stats
).
Part of my simple XML is as follows.
<chart>
<search>
<query>... | timechart count by item span=1mon </query>
<earliest>-6mon@mon</earliest>
<latest>now</latest>
</search>
<drilldown>
<set token="trend_item_earliest">$earliest$</set>
<set token="trend_item_latest">$latest$</set>
</drilldown>
<chart>
...
<table>
<search>
<query>... | stats count by item </query>
<earliest>$trend_item_earliest$</earliest>
<latest>$trend_item_latest$</latest>
</search>
</table>
To troubleshoot this, I've reduced the queries to almost exactly the same for the 2 scenarios, except for the ...|timechart count by item
and ...|stats count by item
at the end. Running these 2 queries in separate searches (I simply clicked on the Magnifying glass icon on each panel to open the search separately), where the time range for timechart
query is "Last 6 months", and the time range for stats
query is "during Nov 2019", still gave me different results.
However, from the table I got from the timechart
query, if I click on one of the cells with discrepancy, e.g. the cell for Nov and Item A, and clicked "View Events", the corresponding search gave the correct number of events.
Why am I getting different results?
I did some more testing, and I noticed that the number of events returned differs when my time modifiers change. It doesn't seem to be a problem with using either timechart
or stats
.
For example, using the exact same query, but with the addition of earliest=-6mon@mon latest=X@mon
, only the results for the last month is accurate, while fewer results are returned for the preceding months.
The table below shows the different latest
modifier used and the corresponding results obtained.
latest= -2mon@mon -1mon@mon @mon Actual no. of events
(Jul-Oct) (Jul-Nov) (Jul-Dec)
Oct 37 14& 14& 37
Nov - 50 33^ 50
Dec - - 51 51
& Missing events occurred on 30 Oct (23 events)
^ Missing events occurred on 7 Nov (1), 12 Nov (1) and 17 Nov (15)
How is the time modifiers affecting my results, and how can I generate a timechart that is accuate?
timechart
has a limited display by default.
timechart limit=0 useother=f usenull=f count by item
please try this.
I'm still getting the same results with the new timechart
command. FYI, my problem wasn't with timechart
showing fewer number of fields, but the count
value was fewer than what I'm getting from stats count