topic Re: JOIN with search from 2 sources in Splunk Search

How to join with search from 2 sources?

garrywilmeth — Fri, 24 Feb 2023 04:48:03 GMT

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

Re: JOIN with search from 2 sources

scelikok — Fri, 24 Feb 2023 04:07:11 GMT

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

Re: JOIN with search from 2 sources

garrywilmeth — Wed, 15 Mar 2023 18:14:27 GMT

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