How do I compare the average of the first 10 results to the average of the last 10 results and apply a calculation?

[hyperscaleau]

I need to return the average of the earliest 10 results (OG) in an index and the average of the latest 10 results (FG) in the same index. I then need to apply a calculation to get the result (ABV) -ie:

ABV=[average of earliest 10 results] minus [average of the latest 10 results] multiplied by 131.25

I can calculate OG by using this search:

| streamstats window=10 earliest(SG) as SGStart | stats avg(SGStart) as OG

And I can calculate FG by using this search:

| streamstats window=10 latest(SG) as SGEnd | stats avg(SGEnd) as FG

And I can also calculate ABV by appending:

| eval stepG = 'OG'-'SG' | eval ABV=stepG*131.25 | table ABV

But obviously some events are lost in the pipeline due to filtering and I can't figure out how to put it all together.

Any help would be greatly appreciated!

[harishalipaka · Accepted solution]

Hi @hyperscaleau

try like this

index=.................| streamstats window=10 earliest(SG) as SGStart | stats avg(SGStart) as OG |appendcols [search index=........| streamstats window=10 latest(SG) as SGEnd | stats avg(SGEnd) as FG] | eval stepG = 'OG'-'SG' | eval ABV=stepG*131.25 | table ABV

[hyperscaleau]

Thanks @harishalipaka You're a star! Slight correction to due some typos (from my original post) but it works perfectly.

index=xyz| streamstats window=10 earliest(SG) as SGStart | stats avg(SGStart) as OG |appendcols [search index=xyz | streamstats window=10 latest(SG) as SGEnd | stats avg(SGEnd) as FG] | eval stepG = 'OG'-'FG'  | eval ABV = 'stepG'*131.25 | table OG,FG,ABV