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]

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?

[ITWhisperer]

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

[pszabo75]

I should have mentioned that I already tried it too.

[thambisetty]

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.

[pszabo75]

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.

[isoutamo]

Hi

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

[pszabo75]

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.

[isoutamo]

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

[isoutamo]

Maybe it's time for support case to splunk?

btw. which version you are running and which OS?

r. Ismo

[pszabo75]

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

[isoutamo]

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

[pszabo75]

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

[isoutamo]

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

[pszabo75]

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

Thank you for the help.

[ITWhisperer]

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

[pszabo75]

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.

[isoutamo]

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.