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

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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...