topic Stats Count not returning expected Results - Difference in count over single date and span covering same date in Splunk Search

Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Thu, 28 Jul 2016 09:34:29 GMT

HI Guys,

Just noticed something a little strange,
I am running a query to cont the number of a certain transaction.
When I run this query for a set date say the 23rd of June, it returns about 5.5k results.
Yet if I run the query with a time chart over the span of 1 month, It returns double the results in the count.
When I click into the events from this search it shows the original number as returned by the search of just that day.

Would someone be able to answer why this occurs?
Search query for the single day is:

host=myComputer-* index=index "KeyPhrase" success=True NOT (Content=Test1 OR Content=Test2) Crm=myRequest|stats count

and for the monthly one I run:

host=myComputer-* index=index "KeyPhrase" success=True NOT (Content=Test1 OR Content=Test2) Crm=myRequest|timechart count

any help appreciated.
Thanks
Steve

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

woodcock — Thu, 28 Jul 2016 13:54:58 GMT

By default, the timechart command will create "empty events" for timeslots with no data so that the spacing on the visualization will be even on the X-Axis. I assume this is what you are noticing.

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Thu, 28 Jul 2016 14:47:55 GMT

This would result in a double the number of events counted?
Like in my issue when I count the single day events it shows roughly 5.5k.
Yet when I run the monthly time chart search, it counts 11k roughly. as that days number of events.
I click into show events that returns the 11k and it resolves to 5.5k results on the day.

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

woodcock — Thu, 28 Jul 2016 15:33:25 GMT

Are you saying that the field count is doubled? That makes no sense to me.

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Thu, 28 Jul 2016 15:40:14 GMT

yeah exactly, so my daily search returns a number or 5326 for the 23rd of June.
yet my search for the entire month of June, on the 23rd shows 10652.
I have this same issue for the following day as well. Yet all the rest of the days of the month or displayed as they should be - counts from timechart search match count from daily count.

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

somesoni2 — Thu, 28 Jul 2016 20:17:16 GMT

Can you try this to see if that makes any difference

host=myComputer- index=index "KeyPhrase" success=True NOT (Content=Test1 OR Content=Test2) Crm=myRequest | bucket span=1d _time | stats count by _time

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Fri, 29 Jul 2016 08:20:26 GMT

Nope still the same.
Returns the double value in the statistics tab, for count as 10652, and if I click into the day in questions events, 23rd of June, that returns the correct count of 5326.

Thanks for the input. It is a puzzling issue.

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Fri, 29 Jul 2016 08:49:20 GMT

If I change the monthly timechart search to a | stats count by date
it still returns the same wrong result for the dates in question.

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

woodcock — Sat, 30 Jul 2016 15:03:41 GMT

The issue is surely that in chart case you are using date but in the timechart case you are using _time. If the date field is a multi-valued field with 2 values, then this will cause doubling. Is this what is happening?

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Mon, 01 Aug 2016 08:15:12 GMT

my understanding was the timechart was charting over a period of time, in this case a month, and creating buckets of 1 day for each of the 30.*.
if I do a stats count on that given date 23rd of June, it returns one result.
If i do a stats count by date (buckets of 1 day) over the month, i get the same doubled results.
Even if it was counting double when I click into show events surely then it would have doubled events ? rather than just the half the count value?

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

somesoni2 — Mon, 01 Aug 2016 16:19:23 GMT

Could you try reducing the timerange of the time chart to 3 days or 7 days (including 23 Jun) and see if the behavior changes?

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Wed, 03 Aug 2016 11:53:44 GMT

The behaviour did indeed change, if I ran the time chart search over seven days the results where what the count for just the 23rd showed.
Any ideas why?

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Tue, 16 Aug 2016 12:10:40 GMT

Thought I had responded to this already, sorry for the delay.
Yeah it does change it seems to be anything over 14days and it doubles some results.
Any idea why this is happening?

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

echalex — Wed, 17 Aug 2016 07:45:01 GMT

I wonder if you should specify the span too. I don't know how the default span is chosen, but for a month, it is normally 1 day in my experience, but perhaps you want to try and set it? If the span chosen by default is longer than 1 day, the timechart normally visualizes this with the first date and/or time.

 host=myComputer-* index=index "KeyPhrase" success=True NOT (Content=Test1 OR Content=Test2) Crm=myRequest|timechart span=1d count

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

insaneteddie — Wed, 17 Aug 2016 11:46:30 GMT

I tried specifying the span already and it made no difference. The results for that date and the following day were doubled, I have tried re centering my search too, as in to move the 23rd to earlier in the time frame and it still appears to be doubled until you click into the events.

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

ejenson_splunk — Wed, 19 Jul 2017 16:24:44 GMT

If you are using a index cluster ensure that your search head is configured to talk to the cluster master and using mode=searchhead in server.conf (please see docs for all details). Do not add the indexers in the index cluster as search peers.

Re: Stats Count not returning expected Results - Difference in count over single date and span covering same date

DalJeanis — Wed, 19 Jul 2017 22:33:47 GMT

This is almost always the result of either a multivalue field or (this other odd situation I ran into with event logging...)

Compare these...

host=myComputer-* index=index "KeyPhrase" success=True NOT (Content=Test1 OR Content=Test2) Crm=myRequest | table _time |stats count

 host=myComputer-* index=index "KeyPhrase" success=True NOT (Content=Test1 OR Content=Test2) Crm=myRequest| table _time | timechart count

If either of those is still doubled, compare | table _raw instead, and/or try this format.

host=myComputer-* index=index "KeyPhrase" success=True NOT (Content=Test1 OR Content=Test2) Crm=myRequest | stats count(_raw)

 host=myComputer-* index=index "KeyPhrase" success=True NOT (Content=Test1 OR Content=Test2) Crm=myRequest | timechart count(_raw)