Help with join query for Salesforce

[linaaabad]

Hello Smarties...

Can someone offer some assistance; We recently started ingesting Salesforce into Splunk, Username are coming in as ID's (00000149345543qba), instead of Jane Doe. So was told to use the Join to get the Usernames or Names, and add to the sourcetype I need "joined" with;  So I am trying to get the "Login As"  events which is under the sourcetype="sfdc:setupaudittrail" - how do I get the Login As events with usernames, if usernames are under the user index and the login as events are under the setupaudittrail sourcetype? Here is my attempted search which doesn't come up with anything; But I know the events exist...

index=salesforce sourcetype="sfdc:user" 
| join type=outer UserAccountId [search index=salesforce sourcetype="sfdc:setupaudittrail" Action=suOrgAdminLogin]

[MuS]

Hi there,

without sample events this can be tricky but since you provided the SPL and you join on UserAccountId I assume this field is available in both sourcetypes.

If this is case, it would be as simple as

index=salesforce UserAccountId=* sourcetype="sfdc:user" OR ( sourcetype="sfdc:setupaudittrail" Action=suOrgAdminLogin )
| fields list of fields you want
| stats values(*) AS * by _time UserAccountId

Hope this helps ...

cheers, MuS

[sainag_splunk]

Hello @linaaabad!

@MuS solution should give you a good start. Please don't use "join" instead use stats .. by as  above.

Refer the below for documentation.
https://lantern.splunk.com/Splunk_Platform/Product_Tips/Searching_and_Reporting/Writing_better_queri...

https://conf.splunk.com/watch/conf-online.html?search=PLA1528B#/