I have some events like :
_time CITY %CPU %Disk Read Time %Disk Write Time 2018-10-11 11:00 PARIS 56 75 10 2018-10-11 10:00 PARIS 12 12 45 2018-10-11 09:00 PARIS 2018-10-11 08:00 PARIS 45 47 7
And I want to complete the 3rd line with the average. So I will obtain :
Edit : not the global average, but the average between the previous and the next hour. If two hour have no value, then the average will take a two hour gap.
_time CITY %CPU %Disk Read Time %Disk Write Time 2018-10-11 09:00 PARIS 25.5 29.5 26
And I know how to do it.
Thanks in advance for you help !
You could use the eventstats command to calculate the averages for each of the values that you are interested in, this adds the value as a new field to every event. Then you could use the coalesce function to fill in the empty values with the average values calculated using eventstats, like this:
| eventstats avg(cpu_time) AS avg_cpu avg(disk_read_time) AS avg_read avg(disk_write_time) AS avg_write | eval cpu_time=coalesce(cpu_time,avg_cpu) | eval disk_read_time=coalesce(disk_read_time,avg_read) | eval disk_write_time=coalesce(disk_write_time,avg_write) | fields- avg*
I made a mistake. It is not the overall average that I want to replace. But by the average between the previous hour and the next hour. An average on only these two values. Am I being a little clearer?
Yes, i get you, I guess then you'd have to use streamstats instead then with a defined window size. I'm not sure how you'd go about accounting for more than one missing value though...