How to join events on an id and subtract values from same named field

[econstantin]

I've got two different events that have identical data points, including an id. I'd like to join the events on an id and then find the delta between a field that's found in each one.

For example

Event1: { type='eventA', id: '123', duration: 500}
Event2: { type='eventB' ,id: '123', duration: 550}
Event3: { type='eventB' ,id: '456', duration: 200}  // this one should be ignored since it doesn't have an id that found in both eventA and eventB

I'd like to get some output like this:

p95(eventA_duration)  p95(eventB_duration)  p95(delta_duration)

(i.e. I think there'd be something in there like: | eval delta_duration = eventB_duration - eventA_duration )

Many thanks!

[woodcock]

Like this:

index="YouShouldAlwaysSpecifyAnIndex" AND sourcetype="AndSourcetypeToo"
| eventstats count(eval(type="eventA")) AS eventAcount BY id
| where eventAcount > 0
| eval {type}_duration = duration
| eventstats range(duration) AS delta_duration BY ID
| stats perc95(eventA_duration) perc95(eventB_duration) perc95(delta_duration)

[aberkow · Accepted solution]

So eval will only work if you have both fields in the same row, which won't work until you've aggregated your two rows into one. That being said, you have to rename the fields so as not to lose them when you convert them (or you can make it a multivalue field and deal with some splitting and other stuff). I'd suggest something like this:

baseSearch
| eval durationEventA = if(type="eventA", duration, null())
| eval durationEventB = if(type="eventB", duration, null())
| stats values(durationEventA) as durationEventA, values(durationEventB) as durationEventB by id
| where isnotnull(durationEventA) isnotnull(durationEventB)
| eval delta_duration= durationEventB - durationEventA

What I'm doing here: creating two fields on each log that will either be null or have a duration, then aggregating them by id, filtering out those that don't have both, and taking the delta.

Hope this helps!

[econstantin]

thanks aberkov, that did the trick. I just needed to add an 'AND' between the conditions on line 5, and add the 'chart p95(durationA) p95(durationB) p95(delta_duration)' at the end