Archive

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

Explorer

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,

t1 type=a account-a field=value1
t2 type=a account-a field=value2
t3 type=b account-a
t4 type=b account-a
t5 type=a account-a field=value3
t6 type=b account-a
t7 type=b account-a

at t3 and t4 type=b should have value2 added, and at t6 and t7 it should have value3 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.

Tags (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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

View solution in original post

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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?

0 Karma

Explorer

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.

SplunkTrust
SplunkTrust

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

0 Karma

Splunk Employee
Splunk Employee

@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!

0 Karma

Explorer

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

0 Karma