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
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?
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?
Thank you!!! Yes, it worked perfectly. I wanted to be able to sum for every possible userID. This made my week.
@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
Good to know, thanks!
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
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
.