Splunk Search

How to sum multiple columns by multiple names?

tarantula
Engager

Hi, how do I sum multiple columns using multiple columns? For instance, my data looks like this:

alt text

How do I get two columns with just Name and Quantity that would combine the results in the table? Essentially:

  1. Name Quantity
  2. Car 3
  3. Plane 2

and etc.
Thank you!

Tags (2)
0 Karma
1 Solution

manjunathmeti
Champion

hi @tarantula,

Try this:

<your base search> | 
| strcat Name1 ":" Quantity1 "," Name2 ":" Quantity2 "," Name3 ":" Quantity3 zip 
| makemv delim="," zip 
| mvexpand zip 
| eval zip=split(zip, ":"), Name=mvindex(zip, 0), Quantity=mvindex(zip, 1) 
| where Name!="" 
| stats sum(Quantity) as Quantity by Name

Sample query:

| makeresults
| eval _raw=" Name1,Quantity1,Name2,Quantity2,Name3,Quantity3
Car,3,,,Bicycle,2
Plane,2,,,Car,1
Boat,2,Plane,1,,
Car,6,,,,,"
| multikv forceheader=1
| strcat Name1 ":" Quantity1 "," Name2 ":" Quantity2 "," Name3 ":" Quantity3 zip
| makemv delim="," zip
| mvexpand zip
| eval zip=split(zip, ":"), Name=mvindex(zip, 0), Quantity=mvindex(zip, 1)
| where Name!=""
| stats sum(Quantity) as Quantity by Name

View solution in original post

manjunathmeti
Champion

hi @tarantula,

Try this:

<your base search> | 
| strcat Name1 ":" Quantity1 "," Name2 ":" Quantity2 "," Name3 ":" Quantity3 zip 
| makemv delim="," zip 
| mvexpand zip 
| eval zip=split(zip, ":"), Name=mvindex(zip, 0), Quantity=mvindex(zip, 1) 
| where Name!="" 
| stats sum(Quantity) as Quantity by Name

Sample query:

| makeresults
| eval _raw=" Name1,Quantity1,Name2,Quantity2,Name3,Quantity3
Car,3,,,Bicycle,2
Plane,2,,,Car,1
Boat,2,Plane,1,,
Car,6,,,,,"
| multikv forceheader=1
| strcat Name1 ":" Quantity1 "," Name2 ":" Quantity2 "," Name3 ":" Quantity3 zip
| makemv delim="," zip
| mvexpand zip
| eval zip=split(zip, ":"), Name=mvindex(zip, 0), Quantity=mvindex(zip, 1)
| where Name!=""
| stats sum(Quantity) as Quantity by Name

Get Updates on the Splunk Community!

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...

Splunk Answers Content Calendar, June Edition II

Get ready to dive into Splunk Dashboard panels this week! We'll be tackling common questions around ...

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...