Skip to main content

[System Administration] Data Sources: Auditing

Caroline St.John
  • Updated

 

Set Up Auditing for Data Sources

Enable Audit Trail on a Page
Note: This information can also be viewed in the online help at this location.

There are two options to select when adding a new table:

  • Enable Auditing – If checked, any additions or changes made to data in the table are maintained in the audit tables.
  • Audit Procedures – If checked, a record of every stored procedure that runs against a record as part of a DSP Page Event is maintained in the audit tables.

Refer to Enable Audit Trail and Electronic Signature in the System Administration help for more information.

To enable Audit Trail on a page:

  1. Select System Administration > Data Sources in the Navigation pane.
  2. Click the Audit icon for the WebApp’s data source.
  3. Click Edit.
  4. Select the data source from the Audit Data Source ID list box.
    NOTE: The Audit Data Source ID is the database where the three audit tables are stored once the tables are built. This can be the same as the data source being audited; however, using a secondary database and data source can provide benefits in data management.
  5. Click Save.
    NOTE: The tables to be audited must be added.
  6. Click the Tables icon.
  7. Select the table from Table Name list box.
  8. Verify that the Enable Auditing check box is checked.
  9. Click Save.
    NOTE: These buttons become enabled on the Horizontal View:
    • Build Audit Tables button – Creates the audit tables in the specified Data Source. Once the tables are built, the Snapshot Data button is enabled.
    • Snapshot Data button – Creates a copy of the tables when the snapshot is taken. When a record is edited, the audit trail records both the before and the after values. However, the trail only shows values that are edited. 
      Snapshot Data can be viewed as an insert for existing records. If the audit is enabled after the table has values in it, the trail has no way of telling where the data came from, so the snapshot is a way of verifying that some data existed prior to auditing.
    • Check Columns button – Reports any differences between the columns in the table and the audit table. 
      When the audit tables are built, all the columns from the table that are being audited are included. However, it is possible to have a case where the columns in the table and the audit tables do not match. There can be two reasons for the misalignment: 1) the Designer deliberately removed some columns from the audit table because those values should not be audited or 2) the Designer added columns to the table after the audit tables were build and forgot to manually update the audit tables.
  10. Click the Build Audit Tables button; a validation message displays.
  11. Click the OK button.
  12. Click Snapshot button to take a snapshot of the data in the table if necessary. A validation message displays, click the OK button.

Drop Audit Tables

Two users are required to drop audit tables. The first user requests the drop and within 24 hours, the second user drops the audit tables.

Refer to Enable Audit Trail and Electronic Signature in the System Administration Help for general information.

To drop audit tables:

  1. Click Admin > Data Sources on the Navigation pane.
  2. Click Audit for the WebApp’s data source.
  3. Click the Tables icon.
  4. Click Vertical View.

The first user clicks the Request Drop button. A message displays, confirming a request has been submitted. Both the Request Drop and the Drop Audit Tables buttons are inactive.

If the second user does not click the Drop Audit Tables button within 24 hours, the button becomes inactive and the process needs to be initiated again. Once the button is clicked, the audit is dropped. When Audit is dropped, the audit tables remain in the database. Once the Audit is dropped, the record must be deleted on the Audit Tables page.

NOTE: Dropping an Audit table is not workflow enabled; verbal communication between the two
users involved is required.

Common Issues 

Audit Table Failing After Upgrade

If you have recently upgraded from a previous version to 6.6.0, the following query can correct the issue of failing audit tables. This issue was corrected in version 6.6.1.

UPDATE CranSoft.dbo.DataSource
SET [AuditDataSourceID] = '71DC4DE4-23BC-4CE3-B58B-E83EBA88707D'
WHERE [DataSourceID] = '71DC4DE4-23BC-4CE3-B58B-E83EBA88707D'

Once the query is executed, you will be able to follow these steps to view the delivered Audit tables that caused the error (and you should not have to delete them):

  1. Navigate to Admin > Data Sources in the Navigation pane.
  2. Locate the failing record.
  3. Click the Audit icon for the record.

If you are still experiencing any issues auditing data sources, please contact our support team at https://support.syniti.com.

Related articles