Setting up the Data Audit Configuration

Data Audit Configuration (DAC)

The Data Audit Configuration enables you to select tables for audit and assign Data Audit Maintenance Plans to specific tables.

Select Data Audit Configuration (DAC) from Navigation Manager to access the Data Audit Configuration Setup. All available tables are displayed from which you can filter to make selection more manageable.

26.2.1. Header

Use these fields to filter the tables that appear in the grid below:

Table Name

Click on the Query button to display the list of auditable tables. SunSystems contains a predefined selection of which tables are auditable that should cater for your auditing requirements.

Business Unit Code

This allows for a specific business unit to be displayed. It is normally used in conjunction with the Table Name field. Alternatively, you can enter a Business Unit Code and select Action, Next to display all tables in the business unit.

Column Name

Click on the Query button to display a full list of table columns that are auditable by table. If a column description is selected, click OK on the Find Column Names form, then OK on the Data Audit Configuration form, to display all auditable tables containing that column on the grid, ready to be Audit Activated. This means you can be certain that all tables containing a specific column/field have been selected. For example, every table containing Account Code or Warehouse Code.

If column names vary on different tables then not all the required tables will be returned. For example, Fourth Currency and 4th Currency.

All the columns in the selected table are recorded in the audit record, not just the specific column selected in the Column Name field.

This field does not work in conjunction with Table Name and Business Unit Code selections, but does work with Hide Business Unit Tables and Audit Activated Only flags, if appropriate.

Hide Business Unit Tables

Check this box to display only non-business unit tables.

Audit Activated Only

If this box is ticked, only those tables which have been made auditable are shown in the grid.

Grid

The grid shows the tables that are available for inclusion in the data audit, depending on any filter information you have entered in the header as detailed above. You can then assign Data Audit Maintenance Plans to specific tables.

1. Select Action, Amend. The grid becomes editable.

2. Highlight the required line and enter the following, as required:

Audit Activated

Check this box to select the table for auditing.

Maintenance Plan Code

The maintenance plan code to be assigned to the table.

The following fields are automatically populated once the Maintenance Plan Code field has been entered:

Short Heading Audit Insertions Audit Updates Audit Deletions

If a table is marked as Audit Activated, a Maintenance Plan must be supplied. If a table is not marked as Audit Activated, a Maintenance Plan may be supplied but this is not mandatory.

26.2.2. Extracting the Audit Data

Data Audit Extract (DAE)

To extract the data that has been audited, select Data Audit Extract (DAE) from the Navigation Manager. The Data Audit Extraction form is then displayed:

Enter the required selections for the audit in the following fields:

Audit Operator

If this is completed, the data extract contains audit information specific to this Operator Id. The list of audit operators available is determined by those operators who have performed actions on 'audit activated' tables. It is not the full list of available SunSystems operators.

Maintenance Plan Code

If this is completed, the data extract contains audit information specific to this maintenance plan. This enables you to track changes to tables using a maintenance plan.

Business Unit Code

If this is completed, the data extract only contains audit information specific to this business unit.

Audit Insertions

Check this box to include any insertions that have been specified in the Data Audit Configuration.

Audit Updates

Check this box to include any updates that have been specified in the Data Audit Configuration.

Audit Deletions

Check this box to include any deletions that have been specified in the Data Audit Configuration.

Audit Date From and To

The date range to be audited. If only the Audit Date From field is completed, the range covers up to the current login date. If only the Audit Date To field is completed, the range covers from the start of the data to the Audit Date To date.

Table Name From and To

The range of tables to be audited. If only the Table Name From field is completed, the range covers from that table to the last available table in alphabetical order. If only the Table Name To field is completed, the range covers from the first available table in alphabetical order to the Table Name To table. To audit one table, enter the required table name in both fields. You can further define the audit by specifying the parts of the table to be included in the Auditable Key field as described below.

Auditable Key Part 1 – 16

These are the SQL table keys. You can audit specific parts of a table by entering the required key parts in these fields. To use this, you must have specified the same table name in the Table Name From and To fields as described above. For example, you might want to audit the Item Master table and include specific Item Codes in these fields.

You must select at least one of the audit actions, that is, Audit Insertions, Audit Updates and Audit Deletions. If you do not enter any other

selection information, the output file contains all audit data for that chosen action.

26.2.3. The Output file

The output file is in an Excel workbook format. This file is placed in the following folder on the server: SunSystems root\Data Audit\Data.

The settings for this file are defined in the Data Audit Maintenance Plan Setup e.g.

password protection.

If the file is password protected, you must enter the Extract Password when requested in order to amend the file. Otherwise, you will have read only access.

The structure of the worksheet is also protected by a SunSystems defined password.

The results of the Data Audit Extract are shown in this workbook on a one table per sheet format for each business unit included in the audit. For example, if changes are identified across three tables (Item, Customer and Account) in two business units (PKP and ABC), the workbook contains six sheets called: Item, Customer and Account (for business unit PKP); Item, Customer and Account (for business unit ABC). The column headings on the worksheet are the column headings from the tables.

For an inserted row, the complete row is output in red bold.

For an updated row, the row after update is output and the actual updates are shown in red bold.

For a deleted row, the complete row prior to deletion is output and it is shown in red bold.

A deleted row is always shown as an updated row as well as a deleted row.

26.2.4. Clearing Down Audit Data

Data Audit Clear Down (DACD)

After you have extracted the audit data and produced the output file, you can clear down data that is no longer required.

Retention periods must be taken into consideration before data is cleared. These are shown on the Data Maintenance Plan.

Select Data Audit Clear Down (DACD) from Navigation Manager.

SunSystems 5 Administration