Audit Log

Millennium can audit and log all data modifications (data inserts, updates, and deletions) made to specific data tables. By default, audit logging for data tables is not turned on. However, Millennium system administrators or the database administrator can configure Millennium system options to turn on audit logging for one or more data tables. When a Millennium system administrator or the database administrator turns on audit logging for a table, the system will perform the audits in the background.

Data Tables Available for Audit Logging

Millennium system administrators or the database administrator can turn audit logging on for one or more of the following data tables:

• Actions
• Addresses
• Attendance
• Attribute
• Basic Data
• Citizenship
• Comments
• Correspondence
• Coverage
• Custom Bio
• Custom Prospect
• Death
• Demographics
• Dues Detail
• Education
• Employment
• Financials
• Giving Detail
• Guest
• Internet Addresses
• Media Reference
• Membership
• Names
• Phone Numbers
• Planned Giving
• Proposals
• Planned Gift or Proposal Ratings
• Ratings
• Registration
• Relationships
• Research Source
• Screening
• Tasks
• Tracking

Top of Page

Configuring Audit Logging

Millennium system administrators or the database administrator turn audit logging on for tables when they configure audit logging options. Audit logging options specify the tables that you want to audit. This configuration is stored in the sysinfo system table. When a table is audited, every insert, edit, and deletion will generate new rows in the table's audit log table counterpart. See audit_{tablename} Field Descriptions for more information.

To configure audit logging:

1. Access the System Options form by using Millennium Explorer, Tools > System Upkeep > System Options.
2. Click the Maintenance button located below and to the left of the Auditing header.
3. Select the check box for each table for which you want to turn on audit logging.
4. If you want to turn on auditing for all tables, click the Select all button.
5. Click OK.

Top of Page

Generating Audit Log Reports

Millennium system administrators or the database administrator generate audit log reports. Generate audit log reports when you want to view changes made to constituent data. You can generate a report for all changes made to a table, or you can generate a report for specific changes made to a table. You can also specify if you want a report on changes made to the data for some or all constituents, and if you want a report on changes made by some or all users.

To generate audit log reports:

1. Access the Audit Log Report form by using Millennium Explorer, Tools > Audit > Report.
2. Select the check box for each change type you want to report on. For example, if you want a report on deleted data, select the Delete check box.
3. In the Table field, select the table for which you want to generate the report from the drop-down list.
4. If you want the report to include any change made to the table, click Any change.
5. If you want the report to include changes to specific columns in the table, specify which column you want to report on by clicking the column name, and then clicking Add.
6. Specify a start and end date for the audit log entries you want to include in the report.
7. If you want the report to include data changes for all constituents, click All constituents.
8. If you want the report to include data changes for a specific constituent, click Specific ID, and then type the number of the constituent ID.
9. If you want the report to include data changes made by all users, click All users.
10. If you want the report to include data changes made by a specific user, click Specific user, and then select the name of the user from the drop-down list.
11. If you want the report to also include the rows modified by triggers, select the Include rows modified by triggers check box. For more information about triggers, see System Triggers and Stored Procedures and Trigger Behaviors.
12. Specify the sorting option you want to use in the report. For example, you can sort the audit log data by date, by change type, by user and data, or by constituent ID and date.
13. Click the Run button.

The system generates the audit log report and displays the audit log report in a new browser window. You can save the generated Audit Log report as a .PDF file on your local computer or on a network share.

Top of Page

Purging Audit Logs

Millennium system administrators or the database administrator can purge audit log entries. Keep audit log entries based on the audit log policy defined for Millennium by your institution. Monitor the size of the audit logs periodically. You should purge the audit log only as needed, such as when specified by an internal site policy, or when the audit log files (tables) grow very large in size. The audit log file size can grow very large based on the amount of data entry done to the table being audited. The purging action deletes rows from the audit log tables.

To purge audit logs:

1. Access the Audit Log Report form by using Millennium Explorer, Tools > Audit > Purge.
2. Select the check box for each audit log change type that you want to purge.
3. Specify a start and end date for the audit log entries you want to purge.
4. Select the check box for each table for which you want to purge audit log entries.
5. If you want to purge audit log entries for all tables, click the Select all button.
6. Click the Purge button.

Top of Page

Auditing Form

This section provides field and button descriptions for the Auditing form. The Auditing form is used to turn audit logging on for one or more data tables.

Access the Auditing form by using Millennium Explorer, Tools > System Upkeep > System Options and then clicking the Maintenance button located below and to the left of the Auditing header.

Select All Button

Selects all the data tables on the form. Click this button when you want to turn audit logging on for all tables listed on the form.

Clear All Button

Clears all the data tables on the form. Click this button when you want to clear auditing for all tables listed on the form.

TableName

Specifies that you want to turn audit logging on when you select the check box in front of the table name, where TableName is the name of a table that you can audit. For more information about which data tables are available for auditing, see Data Tables Available for Auditing Logging.

OK Button

Turns auditing on for each selected data table.

Cancel Button

Cancels any changes you made on the auditing form.

Top of Page

Audit Log Report Form

This section provides field and button descriptions for the Audit Log Report form.

Access the Audit Log Report form by using Millennium Explorer, Tools > Audit > Report.

Insert

Specifies that you want the audit log report to display audit log data logged when data was inserted into a table.

Update

Specifies that you want the audit log report to display audit log data logged when data in a table was updated.

Delete

Specifies that you want the audit log report to display audit information when data in a table was deleted.

Include column data for deleted records

Specifies that you want to include column data for deleted records in the audit log report.

Table

Specifies the table for which you want to generate the audit log report.

Any change

Specifies if you want to the audit log report to display any change made to data in the table.

Specific columns changed

Specifies if you want the audit log report to display changes made to specific columns in the table.

Add Button

Adds data from the selected columns to the audit log report.

Remove Button

Removes data from the selected columns to the audit log report.

Add All

Adds data from all columns to the audit log report.

Clear All

Removes data from all columns from the audit log report

Start

Specifies the start date you want to use when specifying the audit log entries you want to include in the report.

End

Specifies the end date you want to use when specifying the audit log entries you want to include in the report.

All constituents

Specifies that you want to include audit log entries for all constituents in the audit log report.

Specific ID

Specifies that you want to include audit log entries for only the specified constituent ID in the audit log report.

All users

Specifies that you want to include audit log entries for all users in the audit log report.

Specific user

Specifies that you want to include audit log entries for only the specified user in the audit log report.

Include rows modified by triggers

Specifies that you want to include information about rows modified by triggers in the audit log report. For more information about triggers, see System Triggers and Stored Procedures and Trigger Behaviors.

Date

Specifies that you want audit log entries in the audit log report sorted by date.

Change type

Specifies that you want audit log entries in the audit log report sorted by change type.

User, Date

Specifies that you want audit log entries in the audit log report sorted by user name and then by date.

ID, Date

Specifies that you want audit log entries in the audit log report sorted by constituent ID and then by date.

Run Button

Runs the audit log report using the options you specified on the form.

Top of Page

Audit Log Purge Form

This section provides field and button descriptions for the Audit Log Purge form.

Access the Audit Log Purge form by using Millennium Explorer > Tools > Audit > Purge.

Insert

Specifies that you want to purge audit log information about when data was inserted into a table.

Update

Specifies that you want to purge audit log information about when data in a table was updated.

Delete

Specifies that you want to purge audit log information about when data in a table was deleted.

Start

Specifies the start date you want to use when specifying the audit log entries you want to purge.

End

Specifies the end date you want to use when specifying the audit log entries you want to purge.

Select All Button

Selects all the data tables on the form. Click this button when you want to purge audit log entries for all tables listed on the form.

Clear All Button

Clears all the data tables on the form. Click this button when you want to clear audit log purging for all tables listed on the form.

Purge

Purges audit log entries using the options you specified on the form.

Top of Page

audit_{tablename} Field Descriptions

Millennium uses audit tables to track the changes made to fields in the tables. When you enable audit logging for a data table, the audit logging feature automatically creates rows (representing actual data inserts, updates and deletes) in an audit table that is, with the exception of the first five fields in the audit table, a mirror image of that data table. The audit table is named audit_{tablename} table, where tablename is the name of the data table. For example, if audit logging is turned on for the address data table, then each address data maintenance task performed generates a new row in the audit_address table. The audit table has the fields listed in the table below at the beginning of the table, followed by a copy of the data table fields for the table.

Field Name	Type (SQL/Oracle)	Length {Constraints} [Default]	Description
primarykey	numeric/number	13	Specifies the primary key that uniquely identifies the row in the table.
changetype	char	6	Identifies the type of change to the row in the table, such as insert, update, or delete.
changerowtype	char	6	Used only for updates:if the row is an update, this equals 'before'. Else, its value is Null
changedate	datetime/date		Identifies the date and time that the row was last updated.
changeuser	char	30	Specifies the user name of the user making the change to the row.

Indices - audit_{tablename}

Index	Primary	Foreign	Unique	Fields	Sort
pk_audit_<tablename>	Y		Y	primarykey	ASC
audit_<tablename>01				The field in the audit table that holds the data table's primary key. For example, addrkey for address.	ASC
audit_<tablename>02				The field in the audit table that holds the data table's ID number. For example, addrid for address.	ASC
audit_<tablename>03				changeuser	ASC
audit_<tablename>04				changedate	ASC

Top of Page