Splunk Search

## Combine 3 Fields with Same ID

Path Finder

I have 3 fields that will contain the same user IDs and I would like to merge them into 1. They each have a sum value they represent but the 3 fields are just a different role.

If the events look like
Day 1 Pounds=20,field1=A,field2=B,field3=C
Day 2 Pounds=50,field1=C,field2=A,field3=B
Day 3 Pounds =60,field1=B,field2=R,field3=A

Is it possible to combine the 3 so if field1 = A field2 = A field3 = A then the sum(Pounds)=130

Tags (1)
1 Solution
Champion

Are you looking to do this for only a single value at once? I'm trying to imagine taking the above events and producing a table that makes sense. For a single userID (such as "A"), I can envision a search that looks for all events where field1=A OR field2=A OR field3=A and then summing the pounds. No problem.

If you want the sum for every possible userID, I'd expect the solution to involve something like this:

| eval id=mvappend (field1, field2, field3)
| mvexpand id
| stats sum(pounds) AS total BY id

Given the data in you post, this would result in a table like this:

id  | total
A   | 130
B   | 130
C   | 70
R   | 60

Does that fit your specs?

Champion

Are you looking to do this for only a single value at once? I'm trying to imagine taking the above events and producing a table that makes sense. For a single userID (such as "A"), I can envision a search that looks for all events where field1=A OR field2=A OR field3=A and then summing the pounds. No problem.

If you want the sum for every possible userID, I'd expect the solution to involve something like this:

| eval id=mvappend (field1, field2, field3)
| mvexpand id
| stats sum(pounds) AS total BY id

Given the data in you post, this would result in a table like this:

id  | total
A   | 130
B   | 130
C   | 70
R   | 60

Does that fit your specs?

Path Finder

Thank you!!! Yes, it worked perfectly. I wanted to be able to sum for every possible userID. This made my week.

Legend

@elliotproebstel, mvexpand id is not required.

Following is the run anywhere search based on sample data

| makeresults
| eval data="Day 1 Pounds=20,field1=A,field2=B,field3=C;Day 2 Pounds=50,field1=C,field2=A,field3=B;Day 3 Pounds =60,field1=B,field2=R,field3=A"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval id=mvappend (field1, field2, field3)
| stats sum(Pounds) as Pounds by id
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Champion

Good to know, thanks!

SplunkTrust

You're looking for the function coalesce. It will work like this

| eval new_field=coalesce(fieldA,fieldB,fieldC)

This will take fieldA, fieldB ,fieldC and combine their values into new_field

Champion

No, coalesce will assign to new_field the first non-null value it encounters in the list of fields supplied as arguments. So in the sample code, new_field would always contain the value of fieldA.

Get Updates on the Splunk Community!

#### Observability Highlights | November 2022 Newsletter

November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

#### Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...

#### Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...