Splunk Search

How to add sub totals to a table

vshakur
Path Finder

Suppose I have the following table:

comonent | count |

--------------|---------|
a1 | 3 |
a2 | 5 |
a3 | 6 |
b1 | 2 |

b2 | 5 |

I would like to add two rows to the table that would represent the sum of all the a's and the b's. In other words, I'd like to create new components called a-total and b-total that would contain the sum of all the a components and the b components respectfully, and then add those components to the component field.

0 Karma
1 Solution

somesoni2
Revered Legend

If you need to sort as well (giving all a's and corresponding a-total, then all b's and corresponding b-total), try like this

your current search giving fields component and count
| eval keep=0
| appendpipe [| where match(component,"^a") | addcoltotals labelfield=component label="a-total" 
 eval keep=1]
| appendpipe [| where match(component,"^b") | addcoltotals labelfield=component label="b-total" 
 eval keep=1]
| where keep=1

View solution in original post

somesoni2
Revered Legend

If you need to sort as well (giving all a's and corresponding a-total, then all b's and corresponding b-total), try like this

your current search giving fields component and count
| eval keep=0
| appendpipe [| where match(component,"^a") | addcoltotals labelfield=component label="a-total" 
 eval keep=1]
| appendpipe [| where match(component,"^b") | addcoltotals labelfield=component label="b-total" 
 eval keep=1]
| where keep=1

elliotproebstel
Champion

Here's one way to do it:

your base search 
| appendpipe 
  [ | where match(component, "^a") 
    | stats sum(count) AS count 
    | eval component="a-total" ] 
| appendpipe 
  [ |where match(component, "^b") 
    | stats sum(count) AS count 
    | eval component="b-total" ]

The appendpipe command allows you to add some more calculations while preserving the original query results.
http://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Appendpipe

Get Updates on the Splunk Community!

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...

Updated Team Landing Page in Splunk Observability

We’re making some changes to the team landing page in Splunk Observability, based on your feedback. The ...