Splunk Search

How to sum a field and group by another - but remove first entry per group?

GK1982
New Member

I have a search that sorts events by a field (SYMBOL) . My issue is that I want to sum the duration between events by SYMBOL .

I have :

index=xxx sourcetype=yyy ..... | sort 0 SYMBOL, _time | streamstats range(_time) as duration window=2 | stats sum(duration) by SYMBOL

This is skewed by fact that when symbol changes (from A to B for eg), the time resets to start of my search period and this leads to a big time diff between last event of Symbol A and 1st event of Symbol B. I need to remove this diff or set to zero.

Any suggestions?

Tags (1)
0 Karma

sundareshr
Legend

Try this

index=xxx sourcetype=yyy ..... |  streamstats window=2 range(_time) as duration by SYMBOL | stats sum(duration) by SYMBOL

If you don't want a value for the first entry of the SYMBOL, add current=f to the streamstats command

0 Karma

Richfez
SplunkTrust
SplunkTrust

I think instead of streamstats you'll want either stats or transaction. Using the stats command could count items or list them, transaction bundles them together a little more tightly (and transaction gives you a duration without extra work).

I'll focus on transaction because, though it's a little more computationally expensive, I think it's a better fit. You may find someone else gives you a wonderful stats answer, too... :slightly_smiling_face:

index=xxx sourcetype=yyy ..... | transaction maxspan=1h SYMBOL

The maxspan=XX is optional, but should nearly always be included. Set it as long as your expected maximum timespan between the start and end of one set of SYMBOL events as required.

When you run that, "duration" will be set to the overall length of time of the transaction. You can read up on transaction in the link above if that's unclear.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...