Splunk Search

How to estimate an empty value in a search

celianouguier
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

stephenoleary
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

celianouguier
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

celianouguier
Explorer

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

0 Karma

stephenoleary
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
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...