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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...