searching 3 index to get field not found in other index

[kb_vells]

I have three indexes. I want to create single table in dashboard panel for the following requirement.

index=indexProvision with field "UserName"
index=indexApp with field "officer"
index=indexTeam with fields "officer" and "team"

e.g
indexProvision has 500 users and indexApp has 150 Users and indexTeam is a master index which has 1000 users.

Question
I want to compare IndexProvision to find Users who are not found in indexApp. The result I am expecting is 350users. Using 350 users (from indexProvision), I want to compare against indexTeam so that the end result is officer and team details for 350 users.

I want to add this query to my panel, so that I can get table showing UserName and Team

[somesoni2 · Accepted solution]

Try this

index=indexProvision NOT [search index=indexApp | rename officer as UserName | table UserName] 
| table UserName | rename UserName as officer | join officer [search index=indexTeam | table officer team]

Alternative1 (may be faster)

 index=indexProvision OR index=indexApp OR index=indexTeam | eval officer=coalesce(officer,UserName) | stats values(index) as indexes values(team) as team by officer | where mvcount(indexes)=2 | fields - indexes

Alternative2 (may be faster)

|multisearch [search  index=indexProvision NOT [search index=indexApp | rename officer as UserName | table UserName]| table UserName | rename UserName as officer | eval source="1" ] [search index=indexTeam | table officer, team | eval source="2"] | stats values(source) as source values(team) as team by officer | where mvcount(source)=2 | fields - source

[kb_vells]

Hi Somesh
I am not getting any response from both alternative1 and for alternative 2, I am getting the following error. I am find now as the first command is doing well and getting response quickly.

Error in 'multisearch' command: Multisearch subsearches may only contain purely streaming operations (subsearch 1 contains a non-streaming command.)
The search job has failed due to an error. You may be able view the job in the Job Inspector.

[somesoni2]

Could you tell what is the issue you see with 2nd query?

Also,iIn 2nd query, before the where clause, is it returning all 1000 rows and you're getting a multivalued field called indexes?

[kb_vells]

Brilliant Somesoni2. The first one is working perfectly The second query is not working.
If you think the second one is faster can you please validate the second query