Time difference between events and NULL when second event does not appear in logs

[bburns2122]

I'm trying to figure out how to get the time difference between two events that use the same UUID. However, the second event doesn't always happen. How can I add to my query or alter it to show the time difference between event1 and event2 AND just make event2 NULL if it does not exist with the UUID. Here is what I have started that shows the UUID, time_start of event1, time_finish of event2 and difference.

I think I might need to use the transaction command instead of stats but would like to avoid anything too resource intensive.

index=* sourcetype=* eventCode="event1" serviceCallUUID=*
| stats latest(_time) as time_start by serviceCallUUID
| join serviceCallUUID
[ search index=* sourcetype=* eventCode="event2" serviceCallUUID=*
| stats latest(_time) as time_finish by serviceCallUUID ]
| eval difference=time_finish-time_start
| eval difference=strftime(difference,"%M:%S")
| eval time_finish=strftime(time_finish,"%m-%d-%Y %H:%M:%S.%f")
| eval time_start=strftime(time_start,"%m-%d-%Y %H:%M:%S.%f")

Thanks!

[PickleRick]

I'm not sure if you're not overcomplicating a bit.

Firstly - do you really have many event1's for start so you need to look for latest()? (same goes for event2). Secondly, join doesn't seem to be the proper tool here (in splunk it rarely is). Maybe you just need to use "| transaction". Then you have transaction duration calculated automatically.

Oh, and it's usually better to fieldformat times, not eval them. The difference is that if you do

 | fieldformat whatever=strftime(whatever,"yourformat")

instead of eval-ing it, the field itself stays as a unix timestamp (a number) and you can easily compare it to other timestsmps, recalculate, add offsets and so on whereas if you cast it permanently to a string (by eval) you need to re-parse it again if you want to do any of those things.

And for duration you should rather use tostring(duration,"duration") instead of fooling around with strftime.