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!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...