Splunk Search

Combine 3 Fields with Same ID

Hppjet
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)
0 Karma
1 Solution

elliotproebstel
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:

your current search
| 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?

View solution in original post

elliotproebstel
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:

your current search
| 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?

Hppjet
Path Finder

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

0 Karma

niketn
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!!!"
0 Karma

elliotproebstel
Champion

Good to know, thanks!

0 Karma

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

0 Karma

elliotproebstel
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 ...