Audit Trail implementation

You can use the audit trail feature to examine audit trail records in the PI AF SQL database. Once enabled, audit trail records are created using SQL Server Agent jobs. You can view the audit trail with PI System Explorer.

Requirements

The audit trail feature uses SQL Server Change Data Capture (CDC) to generate an audit trail. CDC is supported in the following versions of SQL Server:

¹Development system only.

²Only supported in 2016 and later. For more details on CDC support, see the knowledge base article PI AF error: Could not find stored procedure sys.sp_cdc_parse_captured_column_list.

The audit trail feature has the following additional requirements:

• The SQL Server Agent must be running before you enable the audit trail feature.

• You must be a member of the sysadmin role on the SQL Server that contains the PI AF SQL database.

Enable audit trail

To enable the audit trail feature, you use the AFDiag utility and the EnableAuditTrail (/AT) parameter. For example:

afdiag /AT

Disable audit trail

To disable the audit trail feature and delete all audit trail records permanently, you must have sysadmin privileges on the SQL Server. To disable the audit trail feature, you use the AFDiag utility and the DeleteAuditTrail (/ATD) parameter. For example:

afdiag /ATD

Once you use this parameter, the audit trail is not recoverable.

Previous versions

The audit trail feature that was released in PI AF 2014 or later is supported by this installation and is upgraded when the PI AF SQL scripts are executed. At that time, the audit trail records are upgraded to the current format. If for some reason a failure occurs during the upgrade, you can use the AFDiag UpgradeAuditTrail (/ATU) parameter to fix the audit trail tables and records.

Versions of the audit trail feature prior to PI AF 2014 have a different format and are no longer updated with new change records after an upgrade of the PI AF SQL database. Existing tables and data remain intact, but new records cannot be added.

Audit trail support in SQL Server availability groups

The audit trail feature is supported in SQL Server availability groups. For instructions on how to enable audit trail on secondary machines in a SQL Server high availability environment, please see the installation topic Use PI AF Audit Trail in a SQL Server availability group.

Audit trail support in PI AF collectives

The audit trail feature is not supported on secondary members of PI AF collectives. If the audit trail feature is currently enabled on a server that you wish to add as a secondary server to a PI AF collective, you must run the AFDiag utility and disable the feature. Audit trail data is only stored on the primary member of a PI AF collective and is not replicated to any secondary member of a PI AF collective.

Note: When you are designing SQL Server backup procedures for your PI AF data, please be aware that if the primary member of a PI AF collective becomes unavailable, the audit trail data will also be unavailable. If the primary member of a PI AF collective cannot be recovered from a backup, the audit trail data cannot be recovered either.

Re-enabling audit trail

If you add an existing PI AF server with audit trail enabled to a collective as a secondary member, but later remove that server from the collective, you need to re-enable the audit trail feature on that particular server. Otherwise, auditing remains disabled.