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: [email protected]
session_id: 123 request: 1 email: [email protected]
session_id: 321 request: 1 email: [email protected]

Result)
email request successful_request
[email protected] 2 1
[email protected] 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