topic How to calculate min/max/avg/stdev by each line in Splunk Search

How to calculate min/max/avg/stdev by each line

goji — Tue, 29 Sep 2020 13:25:20 GMT

The date are all number field, such as
cluster, field_1, field_2, field_3, field_4, field_5
1 3 56 6 767 8
1 56 6 5432 5 7
2 6 65 987 356 6767
2 65 56 4321 4 56
3 3 5656 65 56456 56

I'd like to calculate min/max/avg/stdev of each line.
I understand that can stats min() max() avg() stdev() by xxxx,
but stats returned like min(field_1) max(field_2) avg(field_3) ..... as new column.

But, I want display min/max/avg/stdev on each line just like tableau or excel pivot table function.
For example,
new_field, field_1, field_2, field_3, field_4, field_5,
min 3 6 6 5 7
max 30 31 2,719 386 8
avg 30 31 2,719 386 8
stdev 37.47 35.35 3836.76 538.81 0.707

I mean, stats can calculate in parallel but I want to calculate it vertically.
Anyone have any idea? If I could, I'd like to group by cluster number with min/max/avg.
Thanks,

Re: How to calculate min/max/avg/stdev by each line

syazaki_splunk — Wed, 29 Mar 2017 13:57:58 GMT

fieldsummary command include in min max stdev in each fields. Then you can stats,transpose,replace and rename, I was able to outputted you want it. But fieldsummary command does not have avg field.

I tested using _internal and numerous date fields. How about these search?

index=_internal |table date* 
| fields - date_wday date_zone date_month 
| fieldsummary 
| stats list(*) by field 
| fields field *max* *min* *stdev*  
| transpose 
| replace list(*) with * in column 
| replace field with "field_name" in column 
| search column=*max* OR column=*min* OR column=*stdev* 
| rename column as field_name "row 1" as date_hour "row 2" as date_mday "row 3" as date_minute "row 4" as date_second "row 5" as date_year

Re: How to calculate min/max/avg/stdev by each line

niketn — Tue, 29 Sep 2020 13:29:04 GMT

field_1 field_2 etc are field names or field values? If you have already got a Vertical table using stats then you can flip the same by using transpose on the same

| transpose header_field="<YourByFieldInStats>"

If you can share your search that would be helpful to assist.

Re: How to calculate min/max/avg/stdev by each line

somesoni2 — Wed, 29 Mar 2017 14:59:44 GMT

Give this a try

your current search giving fields cluster, field_1, field_2, field_3, field_4, field_5
| untable clusuter FieldName value
| stats min(Value) as min max(Value) as max avg(Value) as avg stdev(Value) as stdev by FieldName
|untable FieldName Metrics Value
| xyseries Metrics FieldName Value

Re: How to calculate min/max/avg/stdev by each line

goji — Tue, 04 Apr 2017 11:36:55 GMT

Thank you somesoni2! This is perfect answer!!