How to join two indexes with a search

[JandrevdM]

Good day,

I want to join two indexes to show all the email addresses that the user have that signed in. 

This queries my mimecast signin logs 

index=db_mimecast splunkAccountCode=* mcType=auditLog | dedup user | table _time, user | sort _time desc

Lets say it returns a user@domain.com that singed in.

I want to then join this to show all the info from 

index=collect_identities sourcetype=ldap:query
| dedup email
| eval identity=replace(identity, "Adm0", "")
| eval identity=replace(identity, "Adm", "")
| eval identity=lower(identity)
| table email extensionAttribute10 extensionAttribute11 first last identity
| stats 
     values(email) AS email
     values(extensionAttribute10) AS extensionAttribute10
     values(extensionAttribute11) AS extensionAttribute11
     values(first) AS first
     values(last) AS last
     BY identity

I tried inner join but I do not have anything that match since my results come back as this for my second query

identity	email	extensionAttribute10	extensionAttribute11	first	last
USurname	user@domain.com userT1@domain.com	user@domain.com user@domain.com	user@another.com user@domain.com	user	Surname

[richgalloway · Accepted solution]

The join command is very inefficient and not always necessary.  Try this query using a subsearch.

index=collect_identities sourcetype=ldap:query [ search index=db_mimecast splunkAccountCode=* mcType=auditLog
  |fields user
  | dedup user
  | eval email=user, extensionAttribute10=user, extensionAttribute11=user
  | fields email extensionAttribute10 extensionAttribute11
  | format "(" "(" "OR" ")" "OR" ")" 
  ]
| dedup email
| eval identity=replace(identity, "Adm0", "")
| eval identity=replace(identity, "Adm", "")
| eval identity=lower(identity)
| table email extensionAttribute10 extensionAttribute11 first last identity
| stats 
     values(email) AS email
     values(extensionAttribute10) AS extensionAttribute10
     values(extensionAttribute11) AS extensionAttribute11
     values(first) AS first
     values(last) AS last
     BY identity