How to search for events based on certain field and its exclusive values between two searches?

[FallMonkey]

Say I have two searches on data sets which contain four fields [field1, field2, field3, field4], e.g.

[1,20,am,a]
[1,20,am,b]
[1,20,pm,b]
[1,20,pm,c]

Search 1: field1 = 1, field2 = 20, field3 = am will return [1,20,am,a] and [1,20,am,b]
Search 2: field1 = 1, field2 = 20, field3 = pm will return [1,20,pm, b] and [1,20,pm,c]

Yet I'm interested in field4 and those events with values of field4 exclusively in my first search, i.e. [1,2,am,a] in this case since field4=b is also presented in second search.

What would be an efficient way to do so? Thanks a lot!

[woodcock · Accepted solution]

Like this:

index=YourIndexHere sourcetype=YourSourcetypeHere field1 = "1" field2 = "20" (field3 = "am" OR field3 = "pm")
| stats dc(field3) AS numSources values(*) AS * BY field4

You now have a fully joined set:
For left Join, add this:

 | search field3="am" AND numSources>1

For right join, add this:

 | search field3="pm" AND numSources>1

For inner join, add this:

| search numSources>1

For outer join, add this:

| search numSources=1

[diogofgm]

Try this

"Your search1" | join type=outer field4 [search "your search2"]

[FallMonkey]

Sorry for not being clear. I've edited my question with more concrete samples for your information.

[woodcock]

You have made a minor adjustment to YOUR plan (not working) and ignored MY plan (which would have gotten you an answer instead of snark). Let me take a stab and you tell me if I am guessing anything remotely close (I am a nerd, not a mind reader):

I am interested in taking running a search where I specify values for 3 fields and extracting from that search the values of a 4th field. I would then like to use those values to drive another search.

In the case of the example data above, the first search is 1: field1 = 1, field2 = 20, field3 = am and will return [1,20,am,a] and [1,20,am,b]
These events with values a and b for field field4. Now I would like to use those values to drive another search like this: field1 = 1, field2 = 20, field3 = pm (field4=b OR field4=c) which would return [1,20,pm, b].

How can I do this all in a single search?

See how I gave specific final desired output? I know that my guess is probably wrong, but why are you making us guess?

[FallMonkey]

Thanks a lot for the comment. Again sorry for the confusion caused here.

I wish to run two similar searches first, as shown above. Then in the results there will be some events with same value of field4 between two search results.

From there I wish to run another search/filtering on complete dataset, to get rid of those events with values of field4 that show up in my 2nd search. Therefore [1,20,am,b] and [1,20,pm,b] are taken out because field4=b is in my 2nd search, as well as [1,20,pm,c]. Clearly I need first two searches to identify how values of field4 are distributed between two searches, so that I could start filter.

Then my question is how I can do all of this in one line. Please lemme know if something is still unclear.

[ryanoconnor]

Can you try to clarify a little bit more of what you're looking for here? I don't see field4 mentioned in either of your searches

[FallMonkey]

Thanks for the reply!

Yes field4 is not listed as my search keyword but it's inside the event/data sets. One event actually contains much more fields but the ones I listed are most interesting for me. Please lemme know if you need more information.

[woodcock]

You have not been clear at all. Please start over, show us COMPLETE sample events and then desired final output.