Splunk Search

How to edit my search to use appendpipe to add averages for a field to my table?

singhh4
Path Finder
index=servers
|stats count AS Total avg(porcTotal) as porcTotal_Average avg(porc30) as porc30_Average avg(porc90) as porc90_Average by Delivery, VDC
|appendpipe[stats sum(Total) as Total|eval LS_Delivery="All"|eval LS_VDC="All"| eval porcTotal_Average="###" | eval porc30_Average="###" | eval porc90_Average="###"]

With the search above, I get the averages of fields porcTotal, porc30, and porc90 by Delivery and VDC which contain percentages. Appendpipe, of course, adds an extra row with anything I want. I would like to replace the "###" with the averages of the entire field. I've tried appendpipe[stats sum(Total) avg(porcTotal_Average) as porcTotal_Average...] but it only averages the numbers that are populated by the table and doesn't give me the correct numbers. I created what I was looking for in a pivot, so I know what numbers I should get. I need this search and not the pivot for some token things I want to do that I can't with a pivot.

Can anyone help?
Please and thank you!

0 Karma
1 Solution

sundareshr
Legend

Try append

index=servers
 |stats count AS Total avg(porcTotal) as porcTotal_Average avg(porc30) as porc30_Average avg(porc90) as porc90_Average by Delivery, VDC 
| append [search index=servers | stats avg(porcTotal) as porcTotal_Average avg(porc30) as porc30_Average avg(porc90) as porc90_Average | eval Delivery="All" | eval VDC="All"]

View solution in original post

somesoni2
Revered Legend

WHen you say "it only averages the numbers that are populated by the table and doesn't give me the correct numbers", do you mean you want to do simple average of (total values/number of rows), instead of (total of available values/number of available rows)?

0 Karma

singhh4
Path Finder

Yes. that is it exactly. From what i am seeing, appendpipe uses only the available values. i need a way of using all values in a field.

0 Karma

sundareshr
Legend

Try append

index=servers
 |stats count AS Total avg(porcTotal) as porcTotal_Average avg(porc30) as porc30_Average avg(porc90) as porc90_Average by Delivery, VDC 
| append [search index=servers | stats avg(porcTotal) as porcTotal_Average avg(porc30) as porc30_Average avg(porc90) as porc90_Average | eval Delivery="All" | eval VDC="All"]

singhh4
Path Finder

The row never appears

0 Karma

sundareshr
Legend

You see any errors/messages in the Job Inspector?

0 Karma

singhh4
Path Finder

I got it to work. I was just missing a few filters and spelt a things differently but it works. Thank you!

0 Karma

sundareshr
Legend

Try this run anywhere sample

index=_internal bytes=* earliest=-15m | stats avg(bytes) as b by status | append [search index=_internal earliest=-15m | stats avg(bytes) as b | eval status="All"]

If this works, try reducing the timerange of your search to 15mins.

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...