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

[GK1982]

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?

[sundareshr]

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

[Richfez]

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... 🙂

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.