Left Outer join

[msrama5]

Hi, I am trying to do search based on field cardid between 2 queries and 2 different time durations, following query does not seem to work, what I need is cardid present only in first query results and not in second query results , tried NOT cause and left join and both are returning incorrect results-
Query 1 based on NOT clause -
earliest=-5m index=iis_openapi /internal/loyalty/v1/ cs_uri_stem="registrations" cs_method = "POST" cardid="" NOT [ search earliest=-5m index=log-cdx-test source=kubernetes sourcetype=_json "PROCESSOR_E2E_TRACKING" "cardRegistered" "cardId" cardNumber="" | rename cardNumber as cardid ] | table cardid

Query 2 based on Left join
index=iis_openapi /internal/loyalty/v1/ cs_uri_stem="registrations" cs_method = "POST" cardid="*" | table cardid | join type=left [ search index=log-cdx-test source=kubernetes sourcetype=_json "PROCESSOR_E2E_TRACKING" "cardRegistered" "cardId" | rename cardNumber as cardid | table cardid] | where id=cardid | dedup cardid | table cardid

Do you see any issue with this query ? can this query be simplified ?

[FrankVl]

Don't think the join approach will work like that, but I think your first approach should work with a few adjustments.

One thing you might want to do is is add the following in the subsearch: | fields cardid | format

earliest=-5m index=iis_openapi /internal/loyalty/v1/ cs_uri_stem="registrations" cs_method = "POST" cardid="*" NOT [ search earliest=-5m index=log-cdx-test source=kubernetes sourcetype=_json "PROCESSOR_E2E_TRACKING" "cardRegistered" "cardId" cardNumber="" | rename cardNumber as cardid  | fields cardid | format] | table cardid

Do note that there are limits to the number of results subsearches can handle. Not sure what number of cardids you would expect to be involved in this. If that is low (since you only look over past 5min) I think this approach should work.

An alternative approach (not using any subsearches and hence not restricted by subsearch restrictions) could be something along these lines:

(earliest=-5m index=iis_openapi /internal/loyalty/v1/ cs_uri_stem="registrations" cs_method = "POST" cardid="*")
OR
(earliest=-5m index=log-cdx-test source=kubernetes sourcetype=_json "PROCESSOR_E2E_TRACKING" "cardRegistered" "cardId" cardNumber="")
| rename cardNumber as cardid
| stats values(index) by cardid
| where index!="log-cdx-test"