topic Why is timechart with where and streamstat not retrieving same results as where and stats? in Splunk Search

Why is timechart with where and streamstat not retrieving same results as where and stats?

kimsej — Fri, 16 Sep 2022 17:50:32 GMT

I am running a query where the following fetches the latency above 1000 milliseconds:

As you can see the query uses stats and a where clause to yield approximately 60 results

When I try to timechart this data-replacing stats with streamstats:

I am now getting 26K+ events. Why is my timechart not reflecting the 60 results I was fetching when using the stats command?

Re: Timechart with where and streamstat not retrieving same results as where and stats

JacekF — Fri, 16 Sep 2022 14:26:38 GMT

The reason for that is in how streamstats works. Consider this example:

This produces the following table:

diff	req_id	sum(diff)
1	a	1
1	a	2
1	a	3
2	b	2
1	a	4
2	b	4
1	a	5
2	b	6

If now the condition

where sum(diff) > 3

is applied, multiple rows for each req_id will match.

If you want to do a timechart from your initial SPL query, you can try the following (replacing the last line of your query)

| bin _time span=5m
| stats sum(diff) as FinalDiff by X_Request_ID, _time
| eval seriesName="Baxter<->Saturn"
| timechart count by seriesName

Re: Timechart with where and streamstat not retrieving same results as where and stats

kimsej — Fri, 16 Sep 2022 14:20:51 GMT

So using your example I get:

index=cards_prod component="card-notification-service" eventCategory=transactions eventType=auth AND ("is going to process" OR ("to POST https://apay.com" AND status=204))
| eval diff=if(searchmatch("is going to process") and isnull(diff), _time*-1, diff)
| eval diff=if(searchmatch("is going to process") and diff > 0, _time*-1 + diff, diff)
| eval diff=if(searchmatch("to POST https://apay.com") and isnull(diff), _time, diff)
| eval diff=if(searchmatch("to POST https://apay.com") and diff < 0 , diff+_time, diff)
| bin span=5m
| stats sum(diff) as FinalDiff by X_Request_ID, _time
| eval seriesName="Baxter<->Saturn"
| timechart count by seriesName

This gives me the error:

Error in 'bin' command: You must specify a field to discretize.

Furthermore is there no way to include the comparison operator?-where sum(diff) > 3

Re: Timechart with where and streamstat not retrieving same results as where and stats

JacekF — Fri, 16 Sep 2022 14:25:59 GMT

My bad, sorry, below is the correct version

Re: Timechart with where and streamstat not retrieving same results as where and stats

kimsej — Fri, 16 Sep 2022 16:20:09 GMT

Worked like a charm thank you!