Splunk Search

How do I get the avg for each column in a Totals row at the bottom?

tkwaller_2
Communicator

Hello

My base search uses CSV data and is very basic, simple field renames

index=fp_dev_csv sourcetype=fp:dev:csv
| dedup Section
| rename "12_31_2018" as "12/31/2018", "1_2_2018" as "1/2/2018", "3_31_2018" as "3/31/2018", "6_30_2018" as "6/30/2018", "9_30_2018" as "9/30/2018"
| table Section, "1/2/2018", "3/31/2018", "6/30/2018", "9/30/2018","12/31/2018"

It gives you a table like

Section 1/2/2018    3/31/2018   6/30/2018   9/30/2018   12/31/2018
Monitoring and Review   3   3.5 3.5 4   4.5
Tools, Measurement and Analysis 1   4   4   4   4.5
Communication and Information Sharing   1   2   3   3.5 4
Skills and Expertise    1   4   4   4   4.5
Vendor Risk Identification and Analysis 2   2.5 3.5 4   4.5
Contracts   2   2.5 3   3.5 4
Policies, Standards, Procedures 1   2   2.5 3   3.5
Program Governance  3   3.5 4   4.5 5

What I would like to do is add a Totals row that has the avg of each column at the bottom
Would be something like

Total    1.75    3.00    3.44    3.81    4.31 

Thanks!

0 Karma
1 Solution

493669
Super Champion

Try to add this :

|appendpipe[stats sum(*) as * |eval Section="Total"]

Your query will be:

index=fp_dev_csv sourcetype=fp:dev:csv
 | dedup Section
 | rename "12_31_2018" as "12/31/2018", "1_2_2018" as "1/2/2018", "3_31_2018" as "3/31/2018", "6_30_2018" as "6/30/2018", "9_30_2018" as "9/30/2018"
 | table Section, "1/2/2018", "3/31/2018", "6/30/2018", "9/30/2018","12/31/2018"|appendpipe[stats sum(*) as * |eval Section="Total"]

View solution in original post

493669
Super Champion

Try to add this :

|appendpipe[stats sum(*) as * |eval Section="Total"]

Your query will be:

index=fp_dev_csv sourcetype=fp:dev:csv
 | dedup Section
 | rename "12_31_2018" as "12/31/2018", "1_2_2018" as "1/2/2018", "3_31_2018" as "3/31/2018", "6_30_2018" as "6/30/2018", "9_30_2018" as "9/30/2018"
 | table Section, "1/2/2018", "3/31/2018", "6/30/2018", "9/30/2018","12/31/2018"|appendpipe[stats sum(*) as * |eval Section="Total"]

View solution in original post

tkwaller_2
Communicator

I knew it was something easy. nice work! thanks a bunch!

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!