topic Re: Different results using stats vs timechart in Splunk Search

Different results using stats vs timechart

wu_weidong — Mon, 06 Jan 2020 10:38:20 GMT

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?

Re: Different results using stats vs timechart

to4kawa — Mon, 06 Jan 2020 12:44:29 GMT

timechart has a limited display by default.

timechart limit=0 useother=f usenull=f count by item
please try this.

Re: Different results using stats vs timechart

wu_weidong — Tue, 07 Jan 2020 02:13:20 GMT

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

Re: Different results using stats vs timechart

wu_weidong — Tue, 07 Jan 2020 03:26:36 GMT

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?