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!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

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

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...