Skip to main content
Splunk Lantern

New tables queried by a Salesforce cloud user

You might need to detect first-time query attempts to sensitive tables by a user who has previously not accessed the tables when doing the following:

Prerequisites 

In order to execute this procedure in your environment, the following data, services, or apps are required:

Example

A search might show first-time query attempts to sensitive tables by a user that has previously not accessed the tables in question. This detection can help prove that individuals within the organization are not abusing or misusing legitimate access to assets that store and process personal data. Because so many people in your organization have access to Salesforce, you want to monitor regularly for new table queries.  

To optimize the search shown below, you should specify an index and a time range.

  1. Populate the lookup_sfdc_usernames lookup provided by the Salesforce Add-on with live values from your site. 
  2. Create a lookup that maps the user id to user names and a delimited list of peers. In this example, we call it lookup_sfdc_peers.
USER_ID USER_NAME peergroup

005j000000BDZNp

Bruno

Bruno|Ken|Jerome

  1. Run the following search:
QUERY=* EVENT_TYPE=API OR EVENT_TYPE=BulkAPI OR EVENT_TYPE=RestAPI 
| rex field=QUERY max_match=12 "\s(?i)from\s*(?<QUERY_TABLE>[\w_]*)" 
| search QUERY_TABLE=Account* OR QUERY_TABLE=Contact* OR QUERY_TABLE=Opportunity* 
| lookup lookup_sfdc_usernames USER_ID OUTPUT FirstName AS USER_NAME 
| stats earliest(_time) as earliest latest(_time) as latest by USER_NAME, QUERY_TABLE 
| eval maxlatest=now() 
| lookup lookup_sfdc_peers USER_NAME OUTPUT peergroup 
| makemv peergroup delim="|" 
| multireport 
    [| stats values(*) as * by USER_NAME QUERY_TABLE ] 
    [| stats values(eval(if(earliest>=relative_time(maxlatest,"-1d@d"),QUERY_TABLE ,null))) as peertoday values(eval(if(earliest<relative_time(maxlatest,"-1d@d"),QUERY_TABLE ,null))) as peerpast by peergroup QUERY_TABLE ] 
| eval user=coalesce(user, peergroup) 
| fields - peergroup 
| stats values(*) AS * BY USER_NAME QUERY_TABLE 
| where isnotnull(earliest)
| eval temp="temp", peergroup_name="lookup_sfdc_peers.csv", isOutlier=case(len(peergroup_name)>0 , if(isnotnull(earliest) AND earliest>=relative_time(maxlatest,"-1d@d") AND isnull(peerpast),1,0), earliest >= relative_time(maxlatest, "-1d@d"), 1, 1=1, 0)

Search explanation

The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment. 

Splunk Search Explanation

QUERY=* 

EVENT_TYPE=API OR EVENT_TYPE=BulkAPI OR EVENT_TYPE=RestAPI 

Filter for API-related events where a query is defined. 

|rex field=QUERY max_match=12 "\s(?i)from\s*(?<QUERY_TABLE>[\w_]*)" 

Extract the table name from the QUERY field and name it QUERY_TABLE.

|search QUERY_TABLE=Account* OR QUERY_TABLE=Contact* OR QUERY_TABLE=Opportunity*

Filter for queries of sensitive tables (Account, Contact, or Opportunity), or their derivatives. 

| lookup lookup_sfdc_usernames USER_ID OUTPUT Username AS USER_NAME

Convert the SFDC USER_ID into a friendly username via a lookup. This lookup is provided with the add-on required for this search. Plan on populating it with live values from your site. 

|stats earliest(_time) AS earliest latest(_time) AS latest BY USER_NAME, QUERY_TABLE 

| eval maxlatest=now() 

Calculate the earliest and the latest time the user queried the table. Set maxlatest to the current time. 

| lookup lookup_sfdc_peers USER_NAME OUTPUT peergroup

Enrich the event with the peer group.

It will be necessary to build this lookup to map user_id, Userner to peer group. 

| makemv peergroup delim="|"

Make a multi value field from the delimited values from the lookup. 

| multireport 

    [| stats values(*) as * by USER_NAME QUERY_TABLE ] 

    [| stats values(eval(if(earliest>=relative_time(maxlatest,"-1d@d"),QUERY_TABLE ,null))) as peertoday values(eval(if(earliest<relative_time(maxlatest,"-1d@d"),QUERY_TABLE ,null))) as peerpast by peergroup QUERY_TABLE ] 

Compare the number of times a ‘secondary field’ is viewed today and historically by the ‘Primary Field’. Multireport allows for the use the stats command multiple times. 

| eval user=coalesce(user, peergroup) 

Return the first value this is not null. 

| fields - peergroup 

Remove peergroup field from the events as it is no longer needed. 

| stats values(*) AS * BY USER_NAME QUERY_TABLE

Bring the stats output together into one for further analysis. 

| where isnotnull(earliest)

Filter out null earliest will handle corner cases and make a cleaner report. 

| eval temp="temp", peergroup_name="lookup_sfdc_peers.csv", isOutlier=case(len(peergroup_name)>0 , if(isnotnull(earliest) AND earliest>=relative_time(maxlatest,"-1d@d") AND isnull(peerpast),1,0), earliest >= relative_time(maxlatest, "-1d@d"), 1, 1=1, 0)

Check to see when the earliest time value is and if it is within the last day, if so we consider it anomalous. 

Result

While there are no traditional false positives in this search, there will be a lot of noise. Every time this search runs, it will accurately reflect the first occurrence in the time period you're searching over (or for the lookup cache feature, the first occurrence over whatever time period you built the lookup). You should not review these alerts directly (except for high sensitivity accounts), but instead use them for context or to aggregate risk.

For most environments, these searches can be run once a day, often overnight, without a lag. If you want to run this search more frequently, or if this search is too slow for your environment, leverage a lookup cache. 

When this search returns values, initiate your incident response process and identify the user demonstrating this behavior. Capture the time of the event, the user's role, and tables queried. If possible, determine the system used and its location. Contact the user and their manager to determine if the download is authorized, and then document that it was authorized and by whom. If you cannot find authorization, the user credentials may have been used by another party and additional investigation is warranted.

GDPR Relevance: This search can be considered an effective security control, as required by Article 32. This is applicable to processing personal data from the controller and needs to also be addressed if contractors or sub-processors from third countries or international organizations access and transfer personal data (Article 15).