topic Re: Avg disappears if I add min and max in Splunk Search

Avg disappears if I add min and max, so how do I combine avg, min, and max together to have all the stats I need?

pszabo75 — Wed, 02 Sep 2020 22:33:16 GMT

I have events with response_time fields coming from an access log file. I have to display the average, min, and max response times sorted by another field called repository. My search looks like this:

<base_search> | stats min(response_time) as min max(response_time) as max avg(response_time) as avg by repository

The average column is empty while I have min and max calculated correctly.

Now, if I remove min and max from my stats command average is calculated as expected.
The response_time field is numeric.

How do I combine avg, min, and max together to have all the stats that I need?

Re: Avg disappears if I add min and max

ITWhisperer — Wed, 02 Sep 2020 16:59:25 GMT

Does putting commas between or using different names help?

<base_search> | stats min(response_time) as minimum, max(response_time) as maximum, avg(response_time) as average by repository

Re: Avg disappears if I add min and max

pszabo75 — Wed, 02 Sep 2020 18:23:12 GMT

No, that doesn't help. In fact, that was my initial version and I removed the commas because I saw examples without them here in the forum.

Re: Avg disappears if I add min and max

thambisetty — Wed, 02 Sep 2020 19:04:46 GMT

Can you change renamed values, try to use minimum instead of min, maximum instead of max, average instead of avg.

min, max and avg are function keywords may be that could be reason.

Re: Avg disappears if I add min and max

pszabo75 — Wed, 02 Sep 2020 20:11:59 GMT

My original stats command looked like this and it didn't work:

| stats min(response_time) as min_response_time, max(response_time) as max_response_time, avg(response_time) as avg_response_time by repository

However I tested it once again, and confirmed that it doesn't help.

Re: Avg disappears if I add min and max

isoutamo — Wed, 02 Sep 2020 20:41:53 GMT

if you get min and max, but avg is empty it usually means that your field is not numeric. Can you check it and convert to numeric if needed?

r. Ismo

Re: Avg disappears if I add min and max

pszabo75 — Wed, 02 Sep 2020 21:15:25 GMT

Did you mean something like this?

| eval resp_time=tonumber(response_time) | stats min(resp_time) as minimum, max(resp_time) as maximum, avg(resp_time) as average by repository

With this search I don't have any results, not even the min and max values.
And don't forget that avg is only empty if I add min and max to the search. Without min and max I have average. I also examined the events and I can see this if I select the response_time field:

I think response_time is definitely number.

Re: Avg disappears if I add min and max

ITWhisperer — Wed, 02 Sep 2020 22:26:59 GMT

What happens if you just try min and avg or max and avg (i.e. not all 3)?

Re: Avg disappears if I add min and max

isoutamo — Thu, 03 Sep 2020 05:50:54 GMT

FYI: Interesting Fields 1st character
a -> String
# -> Number
That is the easiest way to check the type of field.

Re: Avg disappears if I add min and max

isoutamo — Thu, 03 Sep 2020 06:02:31 GMT

Maybe it's time for support case to splunk?

btw. which version you are running and which OS?

r. Ismo

Re: Avg disappears if I add min and max

isoutamo — Thu, 03 Sep 2020 06:20:09 GMT

Actually you could use commas or not, both are working. Many times commas make it more readable, but those are not mandatory. Same is also valid quite many other places (table, foreach ...) too.

Re: Avg disappears if I add min and max

pszabo75 — Thu, 03 Sep 2020 15:12:14 GMT

I should have mentioned that I already tried it too.

Re: Avg disappears if I add min and max

pszabo75 — Thu, 03 Sep 2020 15:15:44 GMT

It's Splunk 8.0.1.
I don't know the OS but most likely our firm's RHEL 7 based internal Linux build

Re: Avg disappears if I add min and max

isoutamo — Fri, 04 Sep 2020 17:50:42 GMT

Can you post the whole query as the issue is probably somewhere else than this stats?

And is the avg only function which is not working or is the same also e.g. for median, p90 etc.?

And no mater if you change the order of those?

Anything on job inspector?

r. Ismo

Re: Avg disappears if I add min and max

pszabo75 — Tue, 08 Sep 2020 16:02:27 GMT

This is the whole query:

index="log-39337-prod-c" laas_appId="gitscm.stash*-bitbucket-access" status=200 username!=" - " labels!=" - " | fields labels, response_time, action, laas_appId | rex field=laas_appId "gitscm.(?<instance>(.*))-bitbucket-access" | rex field=labels ".+(?<operation>refs|fetch|push|clone|archive)" | search operation=clone | rex field=action ".+\/scm\/(?<repository>.+)\.git.+" | stats min(response_time) as minimum, max(response_time) as maximum, avg(response_time) as average by repository

Re: Avg disappears if I add min and max

isoutamo — Wed, 09 Sep 2020 16:49:50 GMT

Really weird, at least I couldn’t notice anything which breaks that query.
Probably next step is to create ticket to splunk support.

Re: Avg disappears if I add min and max

pszabo75 — Wed, 09 Sep 2020 18:20:01 GMT

Yeah, everyone seems to run out of ideas so I guess I don't have any other option.

Thank you for the help.