How to return value list from subsearch and use it in main search?

[C_Sparn]

Hello,
I'm trying to return a list of values from a subsearch to compare that list to other field values in main search. It should look like this:

sourcetype=any OR sourcetype=other
|eval test =[search sourcetype=any OR sourcetype=other
|streamstats count by field1, field2
|stats values(field1) AS f1 values(field1) AS f2
|mvexpand f1
|eval status =if(match(f2,f1),"True","False")
|where status ="False"
|return f1]
|stats values(field3) as f3
|where field4 = test
|table f3

Hello,sry that my question above is a bit complicated to understand.What I want to do is this:
I'm indexing data from two different sourcetypes that have some similar data in different fields. Now I want to compare the values of two fields (field1 and field2) and check if there are some equal values and get a list of that equal values (lets call it "VALUE_LIST"). Then i want to compare other field values (from field3 and field4) of events that have one of the values from VALUE_LIST in their field1 or field2. At the end i need a table with values from VALUE_LIST, field3 and field4 where field3 and field4 are not eqaul. Thanks for the help!

[C_Sparn · Accepted solution]

Hello,
the solution for that problem was using inner join and a where clause for field 3 and 4:

sourcetype=any | eval Tickets=TicketNumber |join type=inner Tickets [search sourcetype=other | eval Tickets=TicketNumberOther] |where UserId != UserIdOther |stats values(UserId) values(UserIdOther) by Tickets

Greetings
C_Sparn

[martin_mueller]

Your stats/streamstats use is a mess, so it's futile to try to gather your requirements from the search. Do explain your use case in natural language instead.