topic Re: Help with join query for Salesforce in Splunk Search

Help with join query for Salesforce

linaaabad — Thu, 24 Oct 2024 18:33:07 GMT

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]

Re: Help with join query for Salesforce

MuS — Thu, 24 Oct 2024 23:45:39 GMT

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

Re: Help with join query for Salesforce

sainag_splunk — Fri, 25 Oct 2024 02:34:17 GMT

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_queries_in_Splunk_Search_Processing_Language

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