How to join with search from 2 sources?

[garrywilmeth]

Hi,

I am trying to figure out how to use join to table the results from 2 searches.

sourcetype=AAD_MSGraph_UserData

AAD_OnPremSID
AAD_Email
AAD_UserType
AAD_LastSignInDateTime
AAD_LastNonInteractiveSignInDateTime
AAD_LastPWChange

sourcetype=AD_UserData

AD_SID
AD_UserPrincipalName
AD_LastLogon

JOIN ON:

AAD_OnPremSID AND AD_SID

TABLE RESULTS:

AAD_OnPremSID, AAD_Email, AAD_UserType, AAD_LastPWChange, AAD_LastSignInDateTime, AAD_LastNonInteractiveSignInDateTime, AD_LastLogon

Thanks!

Garry

[scelikok]

Hi @garrywilmeth,

You can use below search without join.

index=your_index sourcetype=AAD_MSGraph_UserData OR sourcetype=AD_UserData 
| eval sid=coalesce(AAD_OnPremSID,AD_SID) 
| stats values(AAD_*) as * values(AD_LastLogon) as AD_LastLogon by AAD_OnPremSID 
| table AAD_OnPremSID AAD_Email AAD_UserType AAD_LastPWChange AAD_LastSignInDateTime AAD_LastNonInteractiveSignInDateTime AD_LastLogon

[garrywilmeth]

Hello,

I just got a chance to give this a try.  It populated the SID in the table, but no other data for any of the other columns.

Thanks,

Garry