AX2010

Reporting on audited table data

You can use an Axiom query to report upon changes made to table data, for tables that have auditing enabled. These "audit tables" store information on the updated or deleted records in a table.

Any user with at least read-only permission to a particular table has the same level of permission to its corresponding audit table.

Audit table information

If a table is configured to allow auditing, then the audit version of the table is named as follows:

AU_TableName

For example, if the table is named DEPT, then the corresponding audit table is named AU_DEPT. This is the name that you would specify as the primary table for the Axiom query.

The audit table contains the same columns as the original table. While the original table only has one record per unique key combination, the audit table may have multiple records for a particular key combination, representing each time that key combination was modified. These "duplicate" rows are differentiated using the following columns:

  • RecordModifiedBy: The user who modified the record.
  • RecordModifiedDTM: The date/time the record was modified.
  • AuditModifiedDTM: The date/time the record was added to the audit table.

For example, imagine a new record is added to a table on 1/1/2019 by user jdoe. Initially this record is not reflected in the audit table because audit tables do not track insertions. This record is then modified on 1/15/2019 by user jsmith. Now both tables have a record as follows:

Source table - this reflects the record as it exists currently
ACCT DEPT <Other columns> RecordModifiedBy RecordModifiedDTM
1000 40000 ................ jsmith 1/15/2019 12:00:00
Audit table - this reflects the prior version of the record
ACCT DEPT <Other columns> RecordModifiedBy RecordModifiedDTM AuditModifiedDTM
1000 40000 ................ jdoe 1/1/2019 12:00:00 1/15/2019 12:00:00

If the record is modified again in the source table, then another record will be added to the audit table to capture the prior version of the record.

Axiom query design considerations

Keep in mind the following when querying an audit table using an Axiom query:

  • Sum By: The grouping level for the Axiom query should be set to the unique keys in the table, plus RecordModifiedBy and RecordModifiedDTM. This will return unique rows for each record/user/change combination. For example, if the key columns of the table are ACCT and DEPT, the grouping should be set to:

    Acct, Dept, RecordModifiedBy, RecordModifiedDTM

    You can also use AuditModifiedDTM in place of the RecordModified columns (this is the default sum level if you do not specify one). This column tracks when the record was added to the audit table.

  • Filter: If you want to filter the data returned by the query, it is best to use filters that apply to the overall query to the database (such as the Axiom query data filter), rather than filters that apply at the sheet level (such as data range filters). The audit table is likely many times larger than the source table, so server-side filtering is recommended to improve performance.

  • Deleted rows and columns: If a column has been deleted in the source table, that column still exists in the audit table but you cannot query it using an Axiom query. However, if a row has been deleted in the source table, that deleted row can be returned from the audit table by an Axiom query.

  • Security: Your current security read filter for the source table will be applied to any queries to the audit table.

  • Outer joins: The audit table query can only use the audit table and any relevant lookup tables. Outer joins to other data tables / audit tables are not supported.

Using the Report Wizard to create an audit table report

You can use the Report Wizard to automatically create an audit report for a particular table.

The report shows all current records in the table, as well as the available history for each record. The report uses nested Axiom queries, where the first query is for the selected table (example: ACCT) and the second query is to the audit table (example: AU_ACCT).

NOTE: Because this report is based on the current records in the table, deleted records are not included. If you want to view deleted records, then you must manually create a query directly to the audit table instead of using the Report Wizard.

For more information, see Report Wizard: Creating an audit report.