Skip to main content
New to Salsa? Get going with New User support!
BACK TO SUPPORT HOME

Advanced Query Wizard: Create an Audit Trail Query

Mark Rickard
  • Updated

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.

For an overview of the Advanced Query Wizard and its controls, see Advanced Query Wizard Overview.

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.
  1. From the main menu, choose Reports > Queries > Advanced Query Wizard. The Advanced Query Wizard launches.
  2. Click the Create New Query button at the top of the Query Wizard window. This opens a new browser tab for your new query.
  3. From the Query Type frame to the left of the window, choose Audit Trail.
  4. In the Purpose of this Query frame, choose General. All output fields, filter fields, and sort fields will be blank.
  5. Click Next. The Step 2: Select Query Criteria window will open.
  6. 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.
  7. 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
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-12cbbac38619Bulk Deletion - shows the User ID that deleted the donations and the affected Constituent Number.Example: INFO ONLY: user 800214 is deleting donations for constituent 819183Single 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?
1 out of 1 found this helpful

Can't find what you're looking for in the Help Center?

Submit a Ticket