Match IDs in 2 search and ensure a non-match is from the right source

[tkwaller1]

Hello

I have a search like

index=index1 
| rename Number__c as EventId
| append [search index=index2 sourcetype="api" ]
| stats count by EventId
| search count < 2

What it does is search 2 indexes for ids and counts them, expecting 2(1 from each index). What I would like to ensure is that when the count is less than the expected 2 that its only source is from the first search. Meaning that if there is only 1 record it is from the first portion of the search and not found in the second. In the table however I only want to show the EventId.

Thanks for the assistance!

[yuanliu]

As @PickleRick says, do not use append unless absolutely necessary.  I'd suggest a direct expression to do what you want:

index=index1 OR (index=index2 sourcetype="api")
| eval EventId = coalesce(EventId, Number__c)
| stats count values(index) as indices by EventId
| search count < 2 indices = index1

[PickleRick]

1. Be careful with the append command. It spawns a subsearch and therefore is limited by subsearch constraints (and can get finalized silently without producing full results). In your case you could either use multisearch since you have only streaming comands or a single search with conditional assignment or evaluation to get EventId properly assigned.

index=index1 OR (index=index2 sourcetype=something)
| eval EventId=coalesce(EventId,Number__c)

(That's assuming that when you have Number__c in your event, you don't have EventId; if it's not the case, you have to use if() or case() with your eval).

2. To not only find if there are two matching events but which of them is missing if there is only one, you have to do it slightly differently.

Firstly classify your events

| eval classifier=if(index=index1,1,2)

Now you can do

| stats sum(classifier) by EventId

This way you'll get a value of 3 when there are both events, 1 if there is only an event from index1 or 2 if there is only an event from index2.

[livehybrid]

Hi @tkwaller1 ,

You should be able to your search as follows:

(index=index1) OR (index=index2 sourcetype="api")
| eval EventId=COALESCE(Number__c, EventId)
| stats dc(index) as indexCount by EventId
| where indexCount>1

Please let me know how you get on and consider upvoting/karma this answer if it has helped.
Regards

Will