Splunk Subsearch, Using value from field in primary search used to conduct a secondary search

Question

I need to pull information from two indices, I need to use a username from a search in my first index to then search in a second index to pull that user's corresponding email address from a different field. I have been trying different way with "search" and then with "join" but whatever im doing just isnt working.

index=dlp sourcetype="security:ta" signature="Administrative account was enabled" | rename file_name as username | join type=left username [search index=aad_enterprise userDisplayName=username | fields userPrincipalName | format | table userPrincipalName ] | table _time user userPrincipalName

The above is using the value of "username" from my first search and being used to match the "userDisplayName" field in the second search being done in the "aad_enterprise" index. I want to be able to use the value from the "userPrincipalName" field from the second search in my main one, because this is the user's email and I need it to be tabled out with more information being pulled out in my first search.

Is what im trying to do possible? Thank you in advance

Here are some queries I have tried with no success:

index=dlp sourcetype="security:ta" signature="Administrative account was enabled" | rename file_name as username | join type=left username [search index=aad_enterprise userDisplayName=username | fields userPrincipalName | format | table userPrincipalName ] | table _time user userPrincipalName

and

index=dlp sourcetype="security:ta" signature="Administrative account was enabled" [search index=aadenterprise userDisplayName=username| fields userPrincipalName | rename userPrincipalName as userEmail] | table _time username userPrincipalName

Answer 1

The first query should work with a little modification. Don't bother trying to match userDisplayName to username - join will do that.

index=dlp sourcetype="security:ta" signature="Administrative account was enabled" 
| rename file_name as username 
| join type=left username [search index=aad_enterprise 
  | fields userPrincipalName 
  | rename userPrincipalName as username
  | format ] 
| table _time username

Be advised the join command is not very performant.