Splunk Search

Take an average of values in a cell from every four rows

harshal_chakran
Builder

Hi,

I have a CSV file, which looks as follows:

ID time value parameter
1 0000-0015 12 param1
1 0015-0030 3 param1
1 0030-0045 3 param1
1 0045-0100 45 param1
1 0100-0115 4 param1
1 0115-0130 6 param1
1 0130-0145 65 param1
1 0145-0200 75 param1

I am trying to get the average of column "value" for every 4 rows, with time displayed as "0000-0100", to get the output as displayed below:-

ID time value parameter
1 0000-0100 15.74 param1
1 0100-0200 37.5 param1

I have tried many search commands but couldn't succeed.

Please Help...!!!

0 Karma
1 Solution

somesoni2
Revered Legend

Try this

source=yourcsvfile.csv | table ID, time, value parameter | streamstats count as sno | eval sno=ceil(sno/4) | rex field=time "(?<start>.*)-(?<end>.*)" | stats first(start) as start last(end) as end avg(value) as value by ID, parameter,sno | eval time=start."-".end | table ID, time, value, parameter

View solution in original post

somesoni2
Revered Legend

Try this

source=yourcsvfile.csv | table ID, time, value parameter | streamstats count as sno | eval sno=ceil(sno/4) | rex field=time "(?<start>.*)-(?<end>.*)" | stats first(start) as start last(end) as end avg(value) as value by ID, parameter,sno | eval time=start."-".end | table ID, time, value, parameter

harshal_chakran
Builder

Thanks a ton somesomi2...!!!

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