How to join two sources with common one common field ?

[zacksoft_wf]

I have sourcetype A that has info about service_accounts such as name, AU, email , full_name, manager_name.
But some of the events in source A, do not contain the field  email , manager_name, full_name field. In those cases I have to look into another index and sourcetype, say B to fetch those data. AU is the common field name in both . Can we join the data, without having to use 'join' for performance issue ?

[isoutamo]

Hi

this is doable and even preferred way to join those without join. Here is couple of links how to do it and why those are better ways.

• https://conf.splunk.com/files/2020/slides/TRU1761C.pdf
• Here is another answer about replacing joins https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi... check woodcock’s answer with examples.

r. Ismo

[zacksoft_wf]

I am trying to make it work. Here is my SPL, and this one works and gives me the expected results. and I am having trouble converting it to a stats .

===================================================================
`index=bayseian` account_type="Service Account" OR cred="False" OR type=W
| join type=left au
[ search index=alts sourcetype=auxilary 
| fields service_account_id,service_account_name,au,owner_elid,au_owner_name,au_owner_email ]
| eval pwd_expires=if(nopassexpire==1, "True", "False"), account_type=if(type=="S", "Service Account", account_type)
| eval elid=coalesce(elid,owner_elid)
| eval au_owner_email=coalesce(email_address,au_owner_email)
| eval au_owner_name=coalesce(full_name,au_owner_name)
| eval service_accout_name=coalesce(cn,service_account_name)
| eval service_account_id=coalesce(service_account_id,app_id)
| rename acct_name as user, account_type as type
| eventstats dc(sourcetype) as dc_st
| where dc_st>1
| eval user=lower(user)
| dedup user
| rex field=user "[^:]+:(?<user>[^\s]+)"
| table user type pwd_expires is_interactive service_account_id service_account_name au au_owner_name job_title au_owner_email owner_elid manager_name lob
| eval _key=user+".key"
==============================================================

Any help please?