Correlate most recent event ‘a’ with event ‘b’

[larryp]

I have a need to pull a field from the most recent event type ‘a’ and add it to event type ‘b’ for those records with the same value for another field (account, for example).

E.g., simplified,

t₁ type=a account-a field=value¹
t₂ type=a account-a field=value²
t₃ type=b account-a
t₄ type=b account-a
t₅ type=a account-a field=value³
t₆ type=b account-a
t₇ type=b account-a

at t₃ and t₄ type=b should have value² added, and at t₆ and t₇ it should have value³ added.

I've tried with eventstats but run into a limitation due to the number of accounts. Transaction doesn't appear to have a way of ending a transaction on an event prior to a value change. We're considering putting the most recent unique account/field combination into a data base and doing a DB lookup in Splunk, but want to see if there's a simpler way to do it.

Thanks.

[DalJeanis · Accepted solution]

Yep, it's actually trivial.

your search that pulls the info giving _time, type, account, yourfield when present 
| sort 0 _time
| streamstats global=t last(yourfield) as lastyourfield by account

When I initially wrote that, I had an extra step in there as below, but since whenever yourfield exists, you want it, myfield is 100% the same as yourfield, so it is unneeded.

your search that pulls the info giving _time, type, account, yourfield when present 
| sort 0 _time
| eval myfield=if(type=a,yourfield,null())
| streamstats current=f global=t last(myfield) as lastyourfield by account

[larryp]

Thank you!
I actually tried streamstats (forgot to mention that) but had 2 problems that you may have fixed - I was getting 10,000 results and assuming it was due to a memory limitation but “sort 0…” takes care of that, and hadn't tried “global=t” as I thought that was the default.
I'm trying with a larger data set.

[DalJeanis]

I believe you are correct about the default setting for global=t, but I change that option often enough that I like to specify it, just in case.

Yes, when I discovered the need for 0 in | sort 0, I had to go back and correct a couple of dozen answers of mine so that they would work if folks used them for large datasets.

What was the second problem you had with your earlier attempt?

[larryp]

The “global=t” issue, but turns out to be a non-issue as you pointed out.
Thanks for the answer! So simple in retrospect.
Not important, but in your unneeded solution (#2), ‘myfield’ and ‘yourfield’ equate to the field in question in type a and type b events? If so, there is no ‘yourfield’ in my example; however, I could see where, with ‘current=f’ and values for field in both type a and type b events, one may want to keep the most recent value prior to the current event's field value.

[DalJeanis]

Yes, that was my initial thinking, but then I deleted/simplified it as per the specifics of the question.

[aaraneta_splunk]

@larryp - Did the answer provided by DalJeanis help provide a working solution to your question? If yes, please don't forget to resolve this post by clicking "Accept". If no, please leave a comment with more feedback. Thanks!

[larryp]

Yes, I've accepted the answer after running on a larger data set (18.5m results).