Skip to Content

Full audit in Jitterbit App Builder

Overview

The Full Audit feature in App Builder is defaulted On for newly created data sources and Tables. Full Audit is an auditing feature added to complement the existing Audit lite capability. Full Audit records business level changes to your data, and allows you to view those changes. When Full Audit is enabled, a special audit log table named App BuilderAuditLog is created in your data source. Changes are captured in the audit log table for any tables with Full Audit enabled.

By default, App Builder does not automatically clean up or purge any records being stored in the Full Audit table. If you're looking to change this behavior, see the Audit config feature, which allows an Administrator user to configure a time period to retain data for, after which App Builder should purge audit records beyond the defined time frame.

Important

The Audit button to view audit logs can only accessed from the following panel types:

  • Form (Audit button on panel frame)
  • Grid (Audit icon near edit/delete icons)

CRUD Rules do not currently capture what the value was previously in the Audit table. If the CRUD Rule runs through the Business Layer, Audit will capture it.

App Builder may throw an error when Populating Baseline, if it encounters fields in excess of Nvarchar(700) or a binary column. As a workaround, you can manually remove field(s) in excess or binary from the Full Audit. A later release will address this behavior.

Features

Business level changes

Full Audit captures business level changes for enabled tables. That is to say, Full Audit will record any Insert, Update, or Delete event which goes through the Business Layer of App Builder. This includes:

  • Edits from the Application Layer
  • CRUD Business Rules

Modifications which are not supported:

  • CRUD Direct Rules
  • Migrations
  • Direct database manipulation
  • Stored Procedures

List resolution

Full Audit will attempt to record lookup values when an edit is made from the UI Layer using a List control type. For instance, if we have an Order page which includes a List control to select a Product, both the Key and Title column values (e.g. ProductID and ProductName) will be captured in the Audit Log.

Foreign key resolution

Similar to list resolution, audited tables containing foreign keys will attempt to have their values resolved. CRUD Business Rules do not have associated List controls (they do not run through the UI Layer), and so this technique allows Full Audit to continue logging resolved title values. Let's assume we have two tables with the following columns:

Order table

  • OrderID
  • ProductID (foreign key to Product table)

Product table

  • ProductID
  • Name (Title column usage)

Full Audit will recognize the foreign key in the Order table when logging edits to the Order.ProductID, and include the Title column "Name" value in the log.

Environment promotion

Enabling Full Audit requires that the Data Source (and related apps) be configured in the Development environment and promoted upwards. Currently, App Builder does not support configuring Audit upstream (e.g. in QA or Production but not Development).

Though the Audit Table itself will be created in upstream environments, none of the Audit Log data will be transfered.

Basline population

App Builder allows a designer to populate a baseline of all Full Audit enabled tables in the Audit Log. This must be done manually in each environment. The baseline populate can be run multiple times - only records which have no previous audit log will be populated.

App Builder includes full audit

Most of the tables within App Builder have Full Audit enabled. This allows developers to view audit logs for things like pages, tables, columns, controls, notification servers, etc.

To configure full audit in an App Builder app

The following section describes how to configure Full Audit. Full Audit is defaulted On for newly created data sources and tables.

To enable full audit for a data source

  1. Navigate to App Workbench > click the Data Sources button
  2. Select the Data Source you want to configure
  3. Click the Enable Audit button
  4. Click Proceed

To configure full audit options for a data source

To configure Full Audit options for your Data Source:

  1. Navigate to App Workbench > click the Data Sources tile
  2. Select the Data Source you want to configure
  3. Click the Audit Config button
  4. Make any changes required and click Save

Enable full audit for a table

Full Audit is enabled on tables by default for data sources with it enabled. Steps are provided below if needed. After enabling Full Audit on a data source, all tables will have Full Audit enabled by default. App Builder inserts a AppBuilderAuditRefId column to all tables to accommodate the audit data.

If needed, you can disable Full Audit for individual tables that should not be audited. To disable Full Audit on a table, manually click the Disable Full Audit button at the table level. In addition, you may need to delete the AppBuilderAuditRefID column for any table that doesn't have a single part UUID Primary Key. It's good practice to also review and clean up migration rules to avoid create and delete of the AppBuilderAuditRefID column.

Single part UUID primary key

For a table with a single part UUID primary key, enabling Full Audit is simple:

  1. Navigate to App Workbench
  2. Click the Tables tile
  3. Locate the Table you wish to enable Full Audit on and click the pencil edit icon
  4. From the Table panel, click More > Edge Case > Enable Full Audit

If you want to disable Full Audit on a table, follow the above directions to Step 3, click on Edge Case and then click the Disable Full Audit button.

Other primary key configurations

Tables that do not have a single part UUID primary key require a bit more work to enable Full Audit. Because Full Audit requires a unique UUID per table record, we will need to add and populate a new column. This new column will use the Audit column usage type.

  1. Navigate to App Workbench
  2. Locate the Table you wish to enable Full Audit on from Tables
  3. Click the table pencil edit icon
  4. Click + Column in the Columns panel to add a new column with the following properties:

    1. Type: UUID.
    2. Allow Nulls: True
    3. Autogenerate: True
    4. Name: AuditID (this name can be whatever you want)
  5. Create a new Migration Rule to populate the column for your existing records

    1. Purpose: Migration
    2. Action: Update
    3. Select from and target your table above
    4. Use the MvSQL function NewUUID() as the value for your AuditID column
  6. Execute the Migration Rule

  7. Navigate back to the column details and set the following:

    1. Allow Nulls: False
    2. Column Usage Type: Audit
  8. Navigate to the table and click on More > Edge Case > Enable Full Audit, Proceed. App Builder provides a success message when task is complete.

Enable full audit on a page

For users to be able to see Full Audit logs, the page panel needs to enable the Audit Link:

  1. Navigate to the Page you want to enable Full Audit display on
  2. Go to the Action Drawer > Live Designer
  3. Select the panel from Page Panel Layout
  4. From the Live Edit region on the right, click the Built-In tab
  5. Check to enable Show Audit
  6. Return to the Application view, refresh the web browser and confirm you see expected results

Security

To access the Full Audit log the user must:

  • Belong to at least one of the following roles:

    • App Builder - Administrators role
    • App Builder - Audit role
  • Have read access to the Data Object linked in the page panel

  • Have read access to the row itself

Populating baseline

Full Audit supports populating a baseline for all records in audited tables. This will write audit log records with Populate as an operation name for all existing records in the audited table. Any audit logs which already exist for a record will not be duplicated (so it's safe to run this multiple times if required).

  1. Navigate to Action Drawer > IDE > Additional Settings
  2. Click the Populate Audit Records button
  3. Locate your app Data Source
  4. Click the Populate All button on the Data Source or Populate on individual tables
  5. Click Proceed, this will kick off a background task that will run