How To Exclude Results From One Search Where a Field Value Matches the Field Value of Another Search

[Traer001]

Hello!

I have two searches that return separate data but have a common field. I am trying to filter my first search by excluding results where the common field value matches the field value in my second search. However, my results are not being filtered and I am not sure where I am going wrong in this query. They are fairly lengthy queries, so a smaller example is below.

Query 1:

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE_ONE |

rex field=_raw "User:\s(?<user_id>\d+)\sLocation:\s(?<loc>\w+)\sLocation\sId:\s(?<loc_id>\d+)" |

where user_id < 5000 |

table user_id, loc, loc_id, _time

Query 2:

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE_TWO |

rex field=_raw "User:\s(?<error_user_id>\d+)\sError:\s(?<error_type>\w+)\sOccurred" |

rex field=_raw "User:\s(?<error_user_id>\d+)\sError\sId:\s(?<error_id>\d+)" |

where error_user_id < 5000 |

table error_user_id, error_type, error_id, _time

Attempt at Filtering:

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE_ONE |

rex field=_raw "User:\s(?<user_id>\d+)\sLocation:\s(?<loc>\w+)\sLocation\sId:\s(?<loc_id>\d+)" |

where user_id < 5000 |

table user_id, loc, loc_id, _time |

search [search

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE_TWO |

rex field=_raw "User:\s(?<error_user_id>\d+)\sError:\s(?<error_type>\w+)\sOccurred" |

rex field=_raw "User:\s(?<error_user_id>\d+)\sError\sId:\s(?<error_id>\d+)" |

where error_user_id < 5000 |

table user_id, error_type, error_id, _time

] |

where user_id != error_user_id |

table user_id, loc, loc_id, _time

In this attempt to filter, I am trying to filter the first search and exclude any instances of users where the user_id matches the error_user_id. It isn't working, though. Any ideas on how I can get this to work?

[ITWhisperer · Accepted solution]

Use outer join to count errors of either type by user_id and filter for when no errors have been found in the second search

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE_ONE |
rex field=_raw "User:\s(?<user_id>\d+)\sLocation:\s(?<loc>\w+)\sLocation\sId:\s(?<loc_id>\d+)" |
where user_id < 5000 |
table user_id, loc, loc_id, _time |
join type=outer user_id [search
index=INDEX host=HOSTNAME sourcetype=SOURCETYPE_TWO |
rex field=_raw "User:\s(?<user_id>\d+)\sError:\s(?<error_type>\w+)\sOccurred" |
rex field=_raw "User:\s(?<user_id>\d+)\sError\sId:\s(?<error_id>\d+)" |
where user_id < 5000 |
eval error=coalesce(error_id, error_type) |
stats count(eval(isnotnull(error))) by user_id |
where count > 0
] |

where isnull(count) |

table user_id, loc, loc_id, _time