topic Re: How to join events on an id and subtract values from same named field in Splunk Search

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

econstantin — Wed, 18 Dec 2019 02:17:35 GMT

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!

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

aberkow — Wed, 18 Dec 2019 16:53:26 GMT

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!

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

woodcock — Wed, 18 Dec 2019 19:57:55 GMT

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)

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

econstantin — Wed, 18 Dec 2019 21:55:18 GMT

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