is it possible to incorporate Salesforce setupaudittrail in the same integration?

[ISanthosh22]

We are currently ingesting the Salesforce Setup audit trail into NewRelic through a flex integration but we wonder if we can incorporate this integration which reduce the efforts of having multiple integration and also multiple connected apps within Salesforce.

Background for the setup audit trail: https://blog.cloudanalogy.com/audit-trail-in-salesforce-how-to-setup-and-use-audit-trail/

Rest API:
https://instance/.my.salesforce.com/services/data/v56.0/query?q=SELECT+Action,+CreatedByContext,+CreatedById,createdby.name,createdby.profile.name,createdby.usertype,+CreatedByIssuer,+CreatedDate,+DelegateUser,++Display,+Id,+ResponsibleNamespacePrefix,+Section+FROM+SetupAuditTraiL

Here is our Flex config:

variable_store:
rest_host: https://instance. my.salesforce.com/
client_id: XXXX
client_secret: XXXXX
# Your query timestamp and offset, utc time is being used in the following example
startDatetime: ${timestamp:datetimeutc-5min}
username: $username
password: $password+securitytoken
rest_api: /services/data/v56.0/query?q=
oauth_token_endpoint: /services/oauth2/token
# Your Salesforce query string
queryString: SELECT+Action,+CreatedByContext,+CreatedById,createdby.name,createdby.profile.name,createdby.usertype,+CreatedByIssuer,+CreatedDate,+DelegateUser,++Display,+Id,+ResponsibleNamespacePrefix,+Section+FROM+SetupAuditTraiL
# Your Salesforce query where clause
queryWhere: " where+createddate=today "
# Timezone, 0000 for utc
Timezone: "%2B0000"

  apis:
    # Get access_token
    - name: authentication
      url: ${var:rest_host}${var:oauth_token_endpoint}
      method: POST
      store_variables:
        storedtoken:  access_token
      # This payload assumes oauth2 grant_type=password.
      # This should be configured based on your oauth flow
      payload: >-
        client_id=${var:client_id}&grant_type=password&client_secret=${var:client_secret}&username=${var:username}&password=${var:password}
      ignore_output: true
      # Get data using access_token
    - name: Salesforce Audit Trail
      event_type: Salesforce Audit Trail
      #url: ${var:rest_host}${var:rest_api}${var:queryString}${var:queryWhere}${var:startDatetime}${var:Timezone}
      headers:
        Authorization: Bearer ${var:storedtoken}

[asllop]

@ISanthosh22 we are working on a PR that should resolve your issue. One of the key features will be the ability to run custom SOQL queries, like the one you noted. We expect to release it by last week of January or the first week of February.

[ISanthosh22]

@asllop this would be great, thank you for the confirmation and looking forward. Does the new release for custom SOQL embed a logic to truncate the historic data and collect only the new occurrences every time it polls? I guess yes, as it has for the event log file. flex integrations have this limitation.

[asllop]

@asllop this would be great, thank you for the confirmation and looking forward. Does the new release for custom SOQL embed a logic to truncate the historic data and collect only the new occurrences every time it polls? I guess yes, as it has for the event log file. flex integrations have this limitation.

@ISanthosh22 yes, we use Redis for that, as you said, just like we do with event logs. We cache the IDs of processed events to avoid processing them again.

[ISanthosh22]

@asllop Thanks again for the confirmation

[ISanthosh22]

Hi @asllop , just checking if you have ETA for the release?

[asllop]

Hi @ISanthosh22 ! We are releasing it by end of next week. All the features are already implemented, now we are in the beta-testing stage.

[asllop]

@ISanthosh22 we just released the new version.

[ISanthosh22]

Hi @asllop ,

Thank you for the new release

I tried including event log file + setup audit trail API in the same config. Setup audit trail integration is working without issues but event logs are not reporting. same thing even though I run manually or run as service. when I removed the setup audit trail from config file and run only the event logs alone then the events data is reporting to NewRelic. I don't see any error executing the event log file alone or running with setup audit trail. I'm still not clear where the event logs are going when we run as a combined integration. Output says it is posting event API response.

Output running both APIs together:
C:newrelic-logs-salesforce-eventlogfile-1.0.0>python .\src_main_.py
{"message": "Integration start. Using program arguments []", "timestamp": 1707781578789, "level": "info"}
{"message": "Running instance 'sfdc-logs'", "timestamp": 1707781578820, "level": "info"}
{"message": "Correctly authenticated with user/pass flow", "timestamp": 1707781579227, "level": "info"}
{"message": "Query object = [{'query': 'SELECT+Action,+CreatedByContext,+CreatedById,createdby.name,createdby.profile.name,createdby.usertype,+CreatedByIssuer,+CreatedDate,+DelegateUser,++Display,+Id,+ResponsibleNamespacePrefix,+Section+FROM+SetupAuditTraiL', 'timestamp_attr': 'CreatedDate', 'rename_timestamp': 'actualTimestamp', 'api_ver': '58.0'}]", "timestamp": 1707781579227, "level": "info"}
{"message": "Running query SELECT+Action,+CreatedByContext,+CreatedById,createdby.name,createdby.profile.name,createdby.usertype,+CreatedByIssuer,+CreatedDate,+DelegateUser,++Display,+Id,+ResponsibleNamespacePrefix,+Section+FROM+SetupAuditTraiL", "timestamp": 1707781579227, "level": "info"}
{"message": "NR Event API response body = {"success":true, "uuid":"dca58bfe-0001-b366-87f0-018d9fb6e913"}", "timestamp": 1707781581109, "level": "info"}
{"message": "Posted 1000 events from log file /", "timestamp": 1707781581109, "level": "info"}
{"message": "NR Event API response body = {"success":true, "uuid":"a8898ec6-0001-b314-c7b1-018d9fb6e988"}", "timestamp": 1707781581219, "level": "info"}
{"message": "Posted 1000 events from log file /", "timestamp": 1707781581219, "level": "info"}
{"message": "No telemetry data", "timestamp": 1707781581219, "level": "info"}
{"message": "Integration end.", "timestamp": 1707781581219, "level": "info"}

Config.yml:
integration_name: com.newrelic.labs.sfdc.eventlogfiles
run_as_service: False
cron_interval_minutes: 60
service_schedule: { "hour": "*", "minute": "0,15,30,45" }
instances:

• name: sfdc-logs
  arguments:
  api_ver: "55.0"
  token_url: "https://XXXX.my.salesforce.com/services/oauth2/token"
  auth: {
  "grant_type": "password",
  "client_id": "",
  "client_secret": "",
  "username": "",
  "password": ""
  }
  auth_env_prefix: "WDUAT"
  cache_enabled: False
  redis: {
  "host": "",
  "port": "",
  "db_number": 0,
  "password": "",
  "ssl": True,
  "expire_days": 2
  }
  date_field: "LogDate"
  generation_interval: "Hourly"
  time_lag_minutes: 300
  labels:
  environment: WDUAT
  queries: [
  {
  query: "SELECT+Action,+CreatedByContext,+CreatedById,createdby.name,createdby.profile.name,createdby.usertype,+CreatedByIssuer,+CreatedDate,+DelegateUser,++Display,+Id,+ResponsibleNamespacePrefix,+Section+FROM+SetupAuditTraiL",
  timestamp_attr: CreatedDate,
  rename_timestamp: actualTimestamp,
  api_ver: "58.0"
  },
  ]
  newrelic:
  data_format: "events"
  api_endpoint: "US"
  account_id: "XXXXXX"
  license_key: "XXXXX"

Please let me know if you can find anything.

Thank you!

[asllop]

Hi @ISanthosh22, a couple of notes:

• If you don't need a time_lag_minutes, set it to zero.
• If you don't need to rename the timestamp attribute, remove the rename_timestamp option.
• Try adding specific EventLogFile requests in queries, so you can control the exact data you are asking for.
• When viewing the generated data at New Relic, please consider that timestamps are the original ones, so if the data you requested from SF was generated 5 hours ago, you will have to adjust the time range accordingly (NR's default time window use to be, since 30 minutes ago until now).

For more info about config options, please read the docs carefully.

[ISanthosh22]

Hi @asllop, hope all is well.

Thank you for the previous notes, adding EventLogfile requests in queries resolved the issue.

However, the integration is unable to process all the available fields for the SetUp Audit trail queries like how the flex integration does.

Here is the eventlogfile integration config (with custom SOQL for setup audit trail):
date_field: "LogDate"
generation_interval: "Hourly"
time_lag_minutes: 0
labels:
environment: WDUAT
queries: [
{
query: "SELECT Id,EventType,CreatedDate,LogDate,Interval,LogFile,Sequence From EventLogFile Where CreatedDate>={from_timestamp} AND CreatedDate<{to_timestamp} AND Interval='{log_interval_type}'"
},
{
query: "SELECT Id,EventType,CreatedDate,LogDate,Interval,LogFile,Sequence From EventLogFile Where LogDate>={from_timestamp} AND LogDate<{to_timestamp} AND Interval='{log_interval_type}'"
},
{
query: "SELECT+Action,+CreatedByContext,+CreatedById,createdby.name,createdby.profile.name,createdby.usertype,+CreatedByIssuer,+CreatedDate,+DelegateUser,++Display,+Id,+ResponsibleNamespacePrefix,+Section+FROM+SetupAuditTraiL+where+createddate=today",
timestamp_attr: CreatedDate,
api_ver: "58.0"
},

Issue:
These setup audit trail fields are not reporting but remaining fields in the query are reporting. Event log file fields are reporting as well.
CreatedByContext
Createdby.name
Createdby.profile.name
Createdby.usertype
CreatedByIssuer
DelegateUser

Test 1:
We initially thought this could be permission issue as some of the fields are reporting. we confirm these permissions are enabled to the user.
Access given to New Relic permission Set :
View Setup and Configuration
View users
View Profile

Test 2:
We have impersonated the user and manually executed the SOQL query in SF UI and it fetched all the fields which confirmed the user has desired permissions.

Test 3:
Executed the same SOQL query for setup audit trail in a flex integration with the same credentials and it reported all the fields. This is makes us think the new version of Eventlogfiles is not processing certain custom fields. please confirm?
Flex integration config remains same from my original trail mail. Attached NR screenshots for data from Eventlogfile integration and flex integration.

Can you please help troubleshoot the issue? Appreciate the help in advance.

Thank you!

[sdewitt-newrelic]

Reopening this issue to develop a fix. The issue here is that nested fields are ignored. For example:

        {
            "attributes": {
                "type": "SetupAuditTrail",
                "url": "/services/data/v55.0/sobjects/SetupAuditTrail/....."
            },
            "Action": ".....",
            "CreatedByContext": null,
            "CreatedById": ".....",
            "CreatedBy": {
                "attributes": {
                    "type": "User",
                    "url": "/services/data/v55.0/sobjects/User/....."
                },
                "Name": ".....",
                "Profile": {
                    "attributes": {
                        "type": "Profile",
                        "url": "/services/data/v55.0/sobjects/Profile/....."
                    },
                    "Name": "....."
                },
                "UserType": "....."
            },
            "CreatedByIssuer": null,
            "CreatedDate": ".....",
            "DelegateUser": null,
            "Display": ".....",
            "Id": ".....",
            "ResponsibleNamespacePrefix": null,
            "Section": "Manage Users"
            }

The nested fields of the CreatedBy field are not processed. The entire CreatedBy result field is ignored.

[ArunprasadGugan123]

Hello @asllop

We are not able to run the config,yaml file and it produces the below output as pasted

at-optimize-csv-parsing\src_main_.py" --config_dir "C:\Users\santosh.indukuri.adm\Documents\newrelic-logs-salesforce-eventlogfile-feat-optimize-csv-parsing\newrelic-logs-salesforce-eventlogfile-feat-optimize-csv-parsing"
Traceback (most recent call last):
File "C:\Users\santosh.indukuri.adm\Documents\newrelic-logs-salesforce-eventlogfile-feat-optimize-csv-parsing\newrelic-logs-salesforce-eventlogfile-feat-optimize-csv-parsing\src_main_.py", line 3, in
newrelic.agent.initialize('./newrelic.ini')
File "C:\Program Files\Python310\lib\site-packages\newrelic\config.py", line 4081, in initialize
_load_configuration(config_file, environment, ignore_errors, log_file, log_level)
File "C:\Program Files\Python310\lib\site-packages\newrelic\config.py", line 1003, in _load_configuration
raise newrelic.api.exceptions.ConfigurationError("Unable to open configuration file %s." % config_file)
newrelic.api.exceptions.ConfigurationError: Unable to open configuration file ./newrelic.ini.

[sdewitt-newrelic]

@ArunprasadGugan123 The latest release should resolve all issues. Would you please try and confirm?

[sdewitt-newrelic]

@ArunprasadGugan123 please test with the latest release. It should resolve all your issues. I will go ahead and close this issue next week if I haven't heard from you. Feel free to reopen as needed.

[ArunprasadGugan123]

@sdewitt-newrelic We have tested with the latest release and the new release resolved all our issues.
We are able to get all the event fields. Thanks for the support !

[sdewitt-newrelic]

@ArunprasadGugan123 great to hear! I will go ahead and close this issue.