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!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...