Splunk Search
Highlighted

How to estimate an empty value in a search

Explorer

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 !

0 Karma
Highlighted

Re: How to estimate an empty value in a search

Explorer

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*
0 Karma
Highlighted

Re: How to estimate an empty value in a search

Explorer

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?

0 Karma
Highlighted

Re: How to estimate an empty value in a search

Explorer

And I want the average with a two hour gap if two consecutives values are missing.

0 Karma
Highlighted

Re: How to estimate an empty value in a search

Explorer

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

0 Karma