Splunk Search

How to append a row and a column of averages?

dzyfer
Path Finder

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.

dzyfer_0-1661913302944.png

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.

Labels (5)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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)

Screenshot 2022-09-01 at 1.36.59 PM.png

 

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.

 

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@dzyfer 

Can you please try this and check Avg_1 and AVG_2?

YOUR_SEARCH_TILL_CHART
| 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)

 

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)

 

Screenshot 2022-08-31 at 11.02.31 AM.png

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.

dzyfer
Path Finder

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?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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)

Screenshot 2022-09-01 at 1.36.59 PM.png

 

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.

 

yuanliu
SplunkTrust
SplunkTrust

Are you thinking of addcoltotalsaddtotals?

| addcoltotals
| addtotals

 

Tags (1)

dzyfer
Path Finder

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?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

In that case, use eventstats and foreach with addcoltotals.

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

 

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...