Splunk Search

Left Outer join

msrama5
Explorer

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 ?

Tags (2)
0 Karma

FrankVl
Ultra Champion

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"
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...