topic How to Round in a stats command? in Splunk Search

How to Round in a stats command?

ebs — Fri, 08 Apr 2022 14:27:32 GMT

Hi,

I'm trying to round the average of my response_time but still getting undesirable results (all the decimal places). Can someone advise what the correct format is?

stats avg(eval(round((response_time),2))) as avg_response_time

Re: Round in a stats command

kamlesh_vaghela — Tue, 11 May 2021 04:58:14 GMT

@ebs

Try this.

| stats avg(eval(round(response_time,2))) as avg_response_time

Re: Round in a stats command

ebs — Tue, 11 May 2021 05:36:20 GMT

I get this error:

Error in 'eval' command: The expression is malformed.

Re: Round in a stats command

bowesmana — Tue, 11 May 2021 05:37:16 GMT

Do your round after the stats

| stats avg(response_time) as avg_response_time | eval avg_response_time=round(avg_response_time,2)

Re: Round in a stats command

kamlesh_vaghela — Tue, 11 May 2021 05:38:58 GMT

@ebs

I tried this.

index=_internal | eval response_time=date_hour + 0.30303 | stats avg(eval(round(response_time,2))) as avg_response_time

Can you please share your search ?

Re: Round in a stats command

mal11 — Fri, 08 Apr 2022 12:38:21 GMT

The issue with doing it this way is you're rounding before you average it. This works if you already have values that need to be rounded and then you average that. I used values(eval(round to round and aggregate into mv for unique values and it works well. I have not been able to find a way with stat, operation and round because it seems like eval needs to go after stats operation which defeats the purpose of a the average.

I could be missing something so if anyone found a way please post as that would save me an extra line almost every time i write a dashboard.

Re: Round in a stats command

mal11 — Fri, 08 Apr 2022 12:45:01 GMT

Here is an example of what i mean and reason i decided to revisit this topic

| stats avg(eval(round(TX,1))) as avg1 avg(TX) as avg2

output

Re: Round in a stats command

bowesmana — Mon, 11 Apr 2022 01:08:43 GMT

Not sure what the issue is here.

stats avg will compute the average of the values found in each event and give you an unrounded result.

stats avg(eval(round(val, 0))) will round the value before giving it to the avg() aggregation. so if you have three events with values 3.3, 3.4 and 4.4, then it will take the average of 3+3+4 (10), which will give you 3.33333333 - again, an unrounded result.

If you want to get a rounded value from your average, the round after the stats, as in the accepted solution, i.e.

Can you clarify what you mean by defeating the purpose

@mal11 wrote:
eval needs to go after stats operation which defeats the purpose of a the average.