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

[kimsej]

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? 

[JacekF]

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

| makeresults
| eval data = "a,1;a,1;a,1;b,2;a,1;b,2;a,1;b,2"
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval req_id = mvindex(data,0)
| eval diff = mvindex(data,1)
| streamstats sum(diff) by req_id
| fields - _time data

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

[kimsej]

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 

[JacekF · Accepted solution]

My bad, sorry, below is the correct version

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

[kimsej]

Worked like a charm thank you!