How do I subtract values from different events and fields based on a common field?

[thomaszheng]

Please help, I'm stuck on this problem for a while. Basically, lets say I have different events with fields like this. Basically I need a way to subtract a count from two different fields from two different events. Those two events only have 1 common field to somehow tie them together.

Event1)
session_id: 123 error: 1

Event2)
session_id: 123 request: 1 email: user1@gmail.com
session_id: 123 request: 1 email: user1@gmail.com
session_id: 321 request: 1 email: user2@gmail.com

Result)
email request successful_request
user1@gmail.com 2 1
user2@gmail.com 1 1

[arjunpkishore5]

Try this. Please mark as answer if this works for you.

|union 
   [search 1]
   [search 2]
| stats values(email) as email, sum(request) as request, sum(error) as error by session_id
| eval successful_request=request - error
| fields - error, session_id