topic Re: How to append a row and a column of averages? in Splunk Search

How to append a row and a column of averages?

dzyfer — Wed, 31 Aug 2022 02:40:48 GMT

Hi, I have a search that uses the chart command to split by 2 fields, such that the results are shown below. The data is split by Name and Month.

I would like to add a row with the average of all Names for each month, and a column with the average of all Months for each Name.
I have tried using appendpipe and appendcols for each case, but couldn't quite figure out the syntax using a chart command.

PS. Each row is already an appended subsearch.

Re: How to append a row and a column of averages?

yuanliu — Wed, 31 Aug 2022 04:19:06 GMT

Are you thinking of addcoltotals, addtotals?

| addcoltotals | addtotals

Re: How to append a row and a column of averages?

dzyfer — Wed, 31 Aug 2022 05:05:07 GMT

Hi @yuanliu , thanks for replying. As I need the averages of the data, just using addtotals or addcoltotals doesn't seem to be sufficient, as the number of rows and columns are dynamic, hence I cannot simply add the totals then divide by a fixed number of rows or columns. Unless you have a way to dynamically count the number of rows and columns?

Re: How to append a row and a column of averages?

kamlesh_vaghela — Wed, 31 Aug 2022 05:33:21 GMT

@dzyfer

Can you please try this and check Avg_1 and AVG_2?

My Sample Search :

| makeresults | eval _raw="Name 03-22 04-22 05-22 06-22 07-22 08-22 A 11.11 11.11 11.11 11.11 11.11 11.11 B 22.22 22.22 22.22 22.22 22.22 22.22 C 33.33 33.33 33.33 33.33 33.33 33.33 D 44.44 44.44 44.44 44.44 44.44 44.44 E 55.55 55.55 55.55 55.55 55.55 55.55 " | multikv forceheader=1 | table Name 0* | addcoltotals | eventstats count as row_count | foreach * [ eval <<FIELD>>=if(isnull(Name),'<<FIELD>>'/(row_count-1),'<<FIELD>>')] | fields - row_count | addtotals | eval column_count=0 | foreach * [ eval column_count=column_count+1] | eval Avg_1 = Total/(column_count-3) | fields - column_count Total | eval Name=if(isnull(Name),"AVG_2",Name)

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: How to append a row and a column of averages?

dzyfer — Wed, 31 Aug 2022 06:20:49 GMT

Hi @kamlesh_vaghela, this works great! Thanks! A quick add-on question, is there a way for row_count and column_count to ignore null values?

Re: How to append a row and a column of averages?

yuanliu — Thu, 01 Sep 2022 04:09:05 GMT

In that case, use eventstats and foreach with addcoltotals.

| eventstats sum(*) as total_* | foreach * [ eval avg_<<FIELD>> = <<FIELD>> / total<<FIELD>> ] | fields - total_* | addcoltotals

Re: How to append a row and a column of averages?

kamlesh_vaghela — Thu, 01 Sep 2022 08:07:19 GMT

@dzyfer

Can you please try this?

YOUR_SEARCH | foreach * [ eval "<<FIELD>>_row_count" = 0 ] | foreach * [ eval "<<FIELD>>_row_count" = if(isnull('<<FIELD>>'),'<<FIELD>>_row_count' ,'<<FIELD>>_row_count'+1) ] | addcoltotals | foreach * [ eval <<FIELD>>=if(isnull(Name),'<<FIELD>>'/('<<FIELD>>_row_count'),'<<FIELD>>') ] | fields - *_row_count | addtotals | eval column_count=0 | foreach * [ eval column_count=if(isnull('<<FIELD>>'),column_count ,column_count + 1)] | eval column_count = (column_count-3) | eval Avg_1 = Total/column_count | fields - column_count Total | eval Name=if(isnull(Name),"AVG_2",Name)

My Sample Search :

| makeresults | eval _raw="Name 03-22 04-22 05-22 06-22 07-22 08-22 A 11.11 11.11 11.11 11.11 11.11 B 22.22 22.22 22.22 22.22 22.22 22.22 C 33.33 33.33 33.33 33.33 33.33 D 44.44 44.44 44.44 44.44 44.44 44.44 E 55.55 55.55 55.55 55.55 55.55 55.55 " | multikv forceheader=1 | table Name 0* | foreach * [ eval "<<FIELD>>_row_count" = 0 ] | foreach * [ eval "<<FIELD>>_row_count" = if(isnull('<<FIELD>>'),'<<FIELD>>_row_count' ,'<<FIELD>>_row_count'+1) ] | addcoltotals | foreach * [ eval <<FIELD>>=if(isnull(Name),'<<FIELD>>'/('<<FIELD>>_row_count'),'<<FIELD>>') ] | fields - *_row_count | addtotals | eval column_count=0 | foreach * [ eval column_count=if(isnull('<<FIELD>>'),column_count ,column_count + 1)] | eval column_count = (column_count-3) | eval Avg_1 = Total/column_count | fields - column_count Total | eval Name=if(isnull(Name),"AVG_2",Name)

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.