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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...