lookup the fields in a different index

[goringop]

I have a splunk query in paloalto data (index=idx_paloalto) something like this:

index=idx_paloalto sourcetype=pan:traffic app:subcategory=encrypted-tunnel OR app:subcategory=gaming OR app:subcategory=proxy OR app:subcategory=remote-access NOT(application=ssl OR app:subcategory=storage-backup OR app:subcategory=email)
| search action=allowed bytes>=10000000
| eval user=mvindex(split(user,"\\"),-1)
| table app:subcategory generated_time user src_ip application src_zone dest_zone action bytes_in bytes_out bytes
| sort 0 -bytes

result:

app:subcategory	generated_time	username	src_ip	application	src_zone	dest_zone	action	bytes_in	bytes_out	bytes
encrypted-tunnel	8/25/2020 11:19	user123	10.24.144.81	ssh	GDC-ENET	ENET	allowed	3649914812	167157295	3817072107
encrypted-tunnel	8/25/2020 6:16	user546	10.21.132.48	ssh	SVS-In	SVS-In	allowed	259262655	871766	260134421

Then another query in Active Directory data (index=idx_ms_ad) something like this:

index=idx_msad sourcetype=ActiveDirectory
| eval username = sAMAccountName
| dedup username
| table username displayName mail
| sort -username

result:

username	displayName	mail
user123	Tommy Lee	tommy.lee@domain.com
user546	Richard White	richard.white@domain.com

What I need is to lookup the username from  index  ms_ad and get additional details like the displayname and mail to my paloalto query getting a result something like this:

app:subcategory

generated_time

username

displayName

mail

src_ip

application

src_zone

dest_zone

action

bytes_in

bytes_out

bytes

encrypted-tunnel

8/25/2020 11:19

user123

Tommy Lee

tommy.lee@domain.com

10.24.144.81

ssh

GDC-ENET

ENET

allowed

3649914812

167157295

3817072107

encrypted-tunnel

8/25/2020 6:16

user546

Richard White

richard.white@domain.com

10.21.132.48

ssh

SVS-In

SVS-In

allowed

259262655

871766

260134421

[richgalloway]

Have you tried combining the two searches into a single query?

index=idx_paloalto sourcetype=pan:traffic app:subcategory=encrypted-tunnel OR app:subcategory=gaming OR app:subcategory=proxy OR app:subcategory=remote-access NOT(application=ssl OR app:subcategory=storage-backup OR app:subcategory=email)
| search action=allowed bytes>=10000000
| eval username=mvindex(split(user,"\\"),-1)
| fields app:subcategory generated_time username src_ip application src_zone dest_zone action bytes_in bytes_out bytes
| append [ index=idx_msad sourcetype=ActiveDirectory
| eval username = sAMAccountName
| dedup username
| fields username displayName mail ]
| stats values(*) as * by username
| sort - bytes
| table app:subcategory generated_time username displayName mail src_ip application src_zone dest_zone action bytes_in bytes_out bytes