TL;DR
@twhite - now that I've fully read your example use case, there is a better option. This is a job for a simple streamstats .
So, assuming that you want the username and email that are the most recent prior ones for any given purchase, we have this..
source="A" OR source="B"
| fields userid email username time action item
| sort 0 userid _time source
| rename COMMENT as "Roll the data from the user record to the purchase record"
| streamstats last(username) as username last(email) as email by userid
| rename COMMENT as "Now keep only the purchase records"
| where source="B"
That's it. Make sure the field names are spelled and capitalized correctly, and that the _time field exists. Also, make sure that if the times are simultaneous, that the purchase record will end up second, not first.
Okay, we believe that if you clarified your use case, that we'd be able to help you develop a much better way of getting what you want.
Here is our statement of what your code would do -
For each value of field_from_b in index=b , find all records in index=a with a matching common_id that have values of field_from_a that are greater than the field_from_b , and report those values.
The following code would achieve that with less RAM than the multiple-mvexpand implementation...
source="A" OR source="B"
| eval compare_value=case(index="A",field_from_a, index="B",field_from_b)
| sort 0 common_id - compare_value
| streamstats values(eval(case(index="A",field_from_a))) as greater_as by common_id
| where index="B" and isnotnull(greater_as)
| rename greater_as as field_from_a
| mvexpand field_from_a
| where field_from_a > field_from_b
Brief explanation:
Streamstats sees only those records that have gone before, so we sort in descending compare_value order. Records which have come before are greater than or equal to the current record.
We copy the values from index="a" that are greater (ie before) the current value from index="b" . (Incidentally, if it happens to be a record in index="a" , it gets all the prior values as well, but we are only keeping the index="b" record after the next test, and we are only keeping them if they had at least one index="a" record that was greater.
The final test is just in case a record has an equal value. Your use case was strictly greater, so we enforce that then.
updated sort to sort 0
... View more
