Splunk Search

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

kimsej
Explorer

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

Screen Shot 2022-09-16 at 1.25.29 PM.png

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:

Screen Shot 2022-09-16 at 1.24.46 PM.png

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

Labels (1)
Tags (2)
0 Karma
1 Solution

JacekF
Path Finder

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

 

View solution in original post

JacekF
Path Finder

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:

diffreq_idsum(diff)
1a1
1a2
1a3
2b2
1a4
2b4
1a5
2b6

 

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

0 Karma

kimsej
Explorer

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 
 
0 Karma

JacekF
Path Finder

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
Explorer

Worked like a charm thank you!

Tags (1)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...