Splunk Search

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

goji
Explorer

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,

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

somesoni2
Revered Legend

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
0 Karma

goji
Explorer

Thank you somesoni2! This is perfect answer!!

0 Karma

niketn
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

syazaki_splunk
Splunk Employee
Splunk Employee

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 
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...