Splunk Search
Highlighted

How to sum multiple columns by multiple names?

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
Highlighted

Re: How to sum multiple columns by multiple names?

Influencer

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

Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.