topic Re: Calculate average from two fields in Splunk Search

Calculate average from two fields

BornConfused — Mon, 12 Oct 2020 14:56:10 GMT

My initial log looks something like:

The quick brown fox jumps over the lazy dog, and it jumped in 23092 seconds.

Trying to extract the number value and get an average. I have a query which extracts the 14th value, essentially a time field. This query works, but I am trying to get an average of the times per host.

| rex field=_raw "(\S+\s+){13}(?<processTime>\S+)\s"
| stats count by processTime, host

processTime host
23092 host123
45098 host088
98987 host238
23092 host123
23092 host123
98656 host088
54545 host238

I need an average for host123, host088, host238

The above query is also grouping the same times and displaying the counts, which is not preferred.

Re: Calculate average from two fields

inventsekar — Mon, 12 Oct 2020 05:17:38 GMT

Please check this:

base search query| stats count(processTime) by host | stats avg(count) as AvgProcessTime by host

Re: Calculate average from two fields

BornConfused — Mon, 12 Oct 2020 14:59:56 GMT

Thank you, for your answer. But, this would return the number of times the base search was found by host:

| stats count(processTime) by host

but thats not what is expected. I have updated the question to reflect the initial log.

Re: Calculate average from two fields

rnowitzki — Mon, 12 Oct 2020 15:14:43 GMT

Hi @BornConfused ,

If I understood your requirement correct, it should be as simple as:

| stats avg(processTime) by host

When I add the sample table you provided as input, the given command puts out the average processing time per host:

host avg(processTime) host088 71877 host123 23092 host238 76766

Hope it helps.
BR
Ralph

Re: Calculate average from two fields

BornConfused — Mon, 12 Oct 2020 15:21:23 GMT

Thank you ! This worked like a charm, I didn't think it was this easy.