In This Article:

    Advanced Query Wizard: Create an Audit Trail Query

    In This Article:

      Audit Trail Query results provide information on when users logged in (or attempted to log in) to Salsa CRM, when a user added, updated, or deleted information, and updated global configurations.

      Query Field Definitions

      Field Name Description

      Time Stamp

      The date and time of the action.

      User ID

      The unique ID of the user who was logged in at the time and performed the action.

      User Name

      The user who was logged in at the time and performed the action.

      Table Name

      The data table involved in the recorded action.

      Unique ID

      The ID of the record involved in the recorded action.

      Action

      The action taken by the user.

      Description

      An enhanced description, if any, recorded about the action taken.


      From the main menu, choose Reports, Queries, and then Advanced Query Wizard. The Advanced Query Wizard launches.

      1. Select the Create new query option at the top of the Query Wizard window.
      2. From the Query Type frame to the left of the window, choose Audit Trail.
      3. In the Purpose of this Query frame, choose General, and then click Next. The Step 2: Select Query Criteria window will open.
      4. Ensure that Output is selected at the bottom of the Available Fields frame. To add fields to the Filter Fields and Sort Fields frames, select Filter and Sort respectively, and then follow the rest of the steps below.
        Pro Tip: Add all the Audit Trail fields to the Output Fields for best results.
      5. Once the fields, sort order, and filters are set up the way you want them, click Next. The Step 3: Preview, Exclude, and Modify Data screen will open. It will be populated with the data from your query.

      Pro Tip: Select the Back button to go back one step in the Wizard if you want to change query parameters to get a different result. The Gear button provides for additional functionality. See Advanced Query Wizard Overview for details.

      Action Table Definitions

      Table Action Description

      address

      Delete

      This action defines an address was deleted by the actor in the User ID column.

      campaign

      Delete

      Lots of extra information included here, but essentially the system reports the following information was updated by the actor in the User ID column:

      • Campaign Name

      • Campaign Description

      configuration

      Update

      System reports the following information was updated by the actor in the User ID column:

      • Previous Update timestamp

      • Current Update timestamp

      • User ID

      • Name of configurable item

      • "Before" value

      • "After" value

      configuration_value

      Delete

      Indicates updates to Patient Services Configuration by the actor in the User ID column.

      constituent

      Add

      Individual or Organization record added for the first time. System reports the following information was created by the actor in the User ID column:

      • Name

      • Address

      • UUID

      constituent

      De-Dupe

      Automatic deduplication of records based on email.

      For example: 

      Single match found on Alex Smith, alex.smith@gmail.com. Overlaying UUID 2623a6ca-e551-11e5-bd56-12cbbac38619 with f3ec2893-c1c0-44e3-94a6-8d76feafed34

      constituent

      Delete

      Indicates a Constituent record was deleted permanently by the actor in the User ID column. System reports on name, address, UUID.

      constituent

      Merge

      Indicates that the C2 constituent record was merged into C1 record by the actor in the User ID column. For example:

      c1=Manion, Hans 10475 Perry Hwy, Wexford, PA 15090-9274 uuid: 410acc3f-db42-458f-b641-9d1d2a90d9da, c2=Manion, Hans uuid: 58354869-b184-415b-aad8-e122f96e78f1

      constituent

      Update

      Individual or Organization record updated at the time of the timestamp. System reports the following information was updated by the actor in the User ID column:

      • Name

      • Address

      • UUID 

      constituent group

      Add

      Reports when X number of constituents are added to a Group by the actor in the User ID column. Group ID reported only, not the Group Name. 

      constituent group

      Delete

      Reports when X number of constituents are removed from a Group by by the actor in the User ID column deleting the Group.  Group ID reported only, not the Group Name. 

      contact

      Delete

      No other details are provided on the contact that was deleted.

      credit_card_info

      Delete

      Reports on credit card authorizations deleted from the system by the actor in the User ID column, including the following:

      • Constituent ID
      • [Donor] Name
      • Card Type
      • Card #
      • Expiration Date

      custom_constit_fields

      Update

      Reports on global updates via Advanced Query Wizard of custom Constituent fields, including the following:

      • # of records affected
      • Field affected
      • Timestamp of activity

      Example: 

      Global Custom Field Update of 10 records using field value_1 and value "cat" at time 2014-08-05 16:18:18.663

      custom_my_fields

      Update

      Reports on global updates via Advanced Query Wizard of custom My Salsa CRM fields by the actor in the User ID column, including the following information:

      • # of records affected
      • Field affected
      • Timestamp of activity

      Example: 

      Global Custom Field Update of 38 records using field value_22 and value "DC" at time 2018-06-01 10:37:08.308

      custom_n_fields

      Add

      Reports on global additions via Configuration menu of new custom fields by the actor in the User ID column, including the name of the custom field and whether the new field applies to only new Constituent records.

      Example:

      Adding custom field Ice Cream Flavor? with Apply To New Only set to FALSE

      custom_n_fields

      Delete

      Any time a donation or constituent record is deleted by the actor in the User ID column, subsequent custom fields are deleted as well. This entry tracks deletions of custom fields, including the Constituent to which the field was associated and the affected table.

      Example:

      Owner ID=5321, Domain=Donation

      data_import

      Delete

      A Resolution Queue was deleted from the database by the actor in the User ID column. No other details are provided on the contact that was deleted.

      data_import_row

      Resolve All

      Resolve All was used in a Resolution Queue to resolve unsuccessful imports by the actor in the User ID column.

      Example:

      User 818977 is invoking resolveAll on Data Import 534 for statuses of "Potential Duplicate" using the following parameters: {BUSINESSEMAIL=USE_EXISTING, MOBILEPHONE=USE_EXISTING, PHONE=USE_EXISTING, GOVTID=USE_EXISTING, BIRTHDATE=USE_EXISTING, EMAIL=USE_EXISTING, URL=USE_EXISTING, DECEASEDDATE=USE_EXISTING, NAME=USE_EXISTING, CONTACTREASON=USE_EXISTING, FORCE_UPDATE=Y, RELIGION=USE_EXISTING, CONTACTMETHOD=USE_EXISTING, ADDRESS=USE_EXISTING, RACE=USE_EXISTING, BUSINESSPHONE=USE_EXISTING, CUSTOM=USE_EXISTING, GENDER=USE_EXISTING, INDORGTYPE=USE_EXISTING, FAX=USE_EXISTING}

      dc_constituent_phase

      Delete

      Each phase of a process can have multiple constituents involved and added to them. This indicates that a phase of the global process was deleted by the actor in the User ID column. No other details are provided on the process or phase that was deleted.

      dc_constit_phase_history

      Delete

      When a phase has changed status, that change is displayed in the Phase History tab of the Cultivation Phase window. This indicates that a phase in the global process was deleted by the actor in the User ID column and subsequent Phase Histories were also deleted. No other details are provided on the process or phases that were deleted.

      dc_constit_phase_responsible

      Delete

      In a cultivation process, a constituent must be held responsible for each phase of a process. This indicates that a process and its subsequent phases were deleted from a Constituent record by the actor in the User ID column. No other details are provided on the contact that was deleted.

      dc_process

      Delete

      A Process is a list of Phases that have a list of goals that are to be completed to finish the Process. This indicates that a global Process was deleted by the actor in the User ID column. No other details are provided on the contact that was deleted.

      donation

      Delete

      There are three usages for donations deleted from the Donation table:

      • A single donation was deleted from the Donation screen by the actor in the User ID column.
      • Bulk donations were deleted at one time, either from the Advanced Query Wizard or because the Constituent was deleted, by the actor in the User ID column.
      • A Donation Reversal was deleted from the Donation screen by the actor in the User ID column.

      Single Donation Deletion - shows Received Date, gift amount, List As Name, [Constituent Number], UUID.

      Example:

      2001-04-17 1000.00 Alexander & Mary Jones [800049] uuid: 266ea848-e551-11e5-bd56-12cbbac38619

      Bulk Deletion - shows the User ID that deleted the donations and the affected Constituent Number.

      Example: 

      INFO ONLY: user 800214 is deleting donations for constituent 819183

      Single Reversal Deletion - shows the User ID that deleted the Reversal and the affected Constituent Number.

      Example: 

      INFO ONLY: user 800214 is deleting donation reversal for 4606

      em_expense

      Delete

      The expense refers to the cost to make the event possible. This indicates that an expense was deleted from the Event Management module by the actor in the User ID column. No other details are provided on the exact event or the room that was deleted.  

      em_room

      Delete

      Rooms are needed for an event. This indicates that a room was deleted from the Event Management module by the actor in the User ID column. No other details are provided on the exact event or the room that was deleted.

      form_header

      Delete

      This is the Header that defines the Form. There are two usages here for deleted Forms:

      • All forms that are deleted because the Constituent was deleted. The actor is in the User ID column, and the Constituent who was affected is in the Description column.

      Example: 

      Deleting all Forms owned by ID:819259

      • The original Form Template was deleted.

      Example: 

      Form Name: "Common Grant Cover" Keyword 1: 2016-03-06;Keyword 2: ;Keyword 3: ;Keyword 4: ;Keyword 5: ;Keyword 6: Sent;Keyword 7: (N/A);Keyword 8: (N/A);Keyword 9: (N/A);Keyword 10: (N/A)

      fund

      Delete

      Indicates that a Fund was deleted by the actor in the User ID column. Example:
      Name: Golf Outing 2009; Description: GO'09; Credit Account: N/A; Debit Account: N/A;

      fund

      Update

      Indicates that a field on the Fund table was updated by the actor in the User ID column. Only the name is noted in the Description column, so if it was some other value on the Fund table, that is not explicit. Example: 

      Name from database: Cash fund Sewickley Updated name: Corporate Contributions

      saved_criteria

      Delete

      Indicates that a Saved Query was deleted by the actor in the User ID column. No other details are provided on the exact query that was deleted.

      solicit_finder_file

      Delete

      Indicates that a Solicitation finder file was deleted by the actor in the User ID column and includes the following information:

      • File Name Description
      • File Stored Date/Time
      • Row Count
      • ID of the Vendor
      • Original Path of the file location

      Example: 

      Description: Test Finder File, Stored: 2017-08-03 11:26:14.0, Row Count: 14301, Vendor ID: 2, File: C:\Users\Documents\TEST\6863_FINDER_FILE.csv

      solicit_keycode_file

      Delete

      Indicates that a Solicitation keycode file was deleted by the actor in the User ID column and includes the following information:

      • File Name Description
      • File Stored Date/Time
      • Row Count
      • ID of the Vendor
      • Original Path of the file location

      Example:

      Description: Current Keycodes 2017-08-03, Stored: 2017-08-03 11:25:39.0, Row Count: 7, Vendor ID: 1, File: C:\Users\Documents\TEST\6863_KEYCODES.csv

      users

      Login Failure

      Indicates a User login failure by the login in the Description column and includes the following information:

      • Attempted User Name
      • Remote IP Address
      • User Agent (operating system and browser used)

      Mac Example:

      ID: hjones; Remote Address: 10.1.3.20 User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.94 Safari/537.36

      Windows Example:

      ID: hjones; Remote Address: 10.1.3.20 User Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:46.0) Gecko/20100101 Firefox/46.0

      users

      Login Success

      Indicates a User login success by the login in the Description column and includes the following information:

      • Attempted User Name
      • Remote IP Address
      • User Agent (operating system and browser used)

      Mac Example:

      ID: hjones; Remote Address: 10.1.3.20 User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.94 Safari/537.36

      Windows Example:

      ID: hjones; Remote Address: 10.1.3.20 User Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:46.0) Gecko/20100101 Firefox/46.0

      web_mapping

      Delete

      When mapping fields from a custom web form that directs gifts into Salsa CRM via the Web Donation Queue, mapping the fields used on the form to the fields in Salsa CRM is required. This notice indicates a web mapping was deleted by the actor in the User ID column.

      Example:

      keyName=aiWebMappingName, formFieldName=null, inputValue=salsaDonationDefault, type=7

      Was this article helpful?
      0 out of 0 found this helpful
      Have more questions? Submit a request

      Comments

      0 comments

      Article is closed for comments.