Use one field to find matching string values in a second field with diffetent times

[matt_squaretrad]

Hello,

I'm trying to find matching strings between two different fields. The complication I'm having seems to be the fact that the entries are not time sync'd due to how the data is generated.

Here's my current search:

index=sfdc source="sfdc_object://object1_Salesforce"
| stats count by Id | appendcols [ search index=sfdc source="sfdc_object://object2_salesforce"| stats count by Case ]
| eval correlate = if (Case=Id, "true", "false") | stats count by correlate Id Case

This is failing to find matches because the events don't line up properly to be compared. For example, here's a short snippet from returned results:

false xxxxxxAY xxxxxxAG 1
false xxxxxxAW xxxxxxAE 1
false xxxxxxAO xxxxxxAI 1
false xxxxxxAQ xxxxxxAW 1

In this case the xxxxxxAW should be matching and show as True. Normally to find this direct from salesforce I'd use a query like:

select name, createddate from case where createddate=today and id not in (select Case from object2)

My goal here is to essential take a specific field from object1, then with those values, go through all of object 2 and try to find a matching value. Then I would exclude all the matches and just return a count of non-matching entries.

[whrg]

Hi! Use a subsearch instead of appendcols. Using a subsearch, you can search for or exclude specific events based on one or more common fields.

If you want to have a list of all events in object1 which are not part of object2, try this:

index=sfdc source="sfdc_object://object1_Salesforce" NOT [search index=sfdc source="sfdc_object://object2_salesforce" | rename Case AS Id | table Id]

Looking at your current search I suppose you need to rename Case to Id in the subsearch so that the fields are the same.