Advanced Historical Auditing

A common request for anyone utilizing m-Power maintainers is the ability to track user actions. Previously, m-Power allowed you to audit which user last touched a record. However, getting a historical logging of actions required extra coding. A new auditing approach records all actions to a separate table which serves as a historical change log for the master maintainer. From a high-level explanation, every maintainer action that is submitted calls another m-Power maintainer in the background to insert a new change record. Depending on your level of logging needed, you can log the values of the record after each add, update, or delete. You can also log what the values were before AND after each action. This document will cover the different steps involved to create a full-fledged historical log of your maintainers.

Creating the Change Log Table

Using this advanced auditing features requires a specific logging table to be used. You can create a table directly on your database or use m-Power's built-in method of creating tables. Typically this table will contain all of the fields of the table being audited. Additionally, these optional fields:

Audit ID — Can be used as a unique ID field for each record in your table.
Audit User — Records the user making the change.
Audit Date — Record the date the change is made.
Audit Time — Records the database time the change is made.
Audit Action — Records the action submitted (add, upd, del, afteradd, before upd, after up, before del, after del)
Audit Program — Records the maintainer number used to make the change.
Audit Change ID — If logging before and after actions, you can map this field to a unique ID parameter that links the before and after records. This field needs to be a character field with at least a length of 23.
Audit Change Note — A reason/explanation input can be added to your master maintainer that will record the note to the audit table.

In my example, I will be auditing the Products Master table and will create my logging table with the same fields from my Products table in addition to all of the aforementioned optional fields:

Creating the Audit Maintainer

Once the logging table has been created, register the table to m-Power and create a maintainer over said table. Sequence by a uniquely identifying field(s). In my case I will be using my AUDITID field and making it an auto-sequence key.

From the Field Settings screen, we need to map the auditing logic to the appropriate audit fields.

Notice, I did not map the CHANGEID or the CHANGENOTE. Those will be mapped in the next step with the master maintainer.

Applying the Audit Maintainer to the Master Maintainer

Go to the master maintainer app, in my case the products master maintainer. We will be applying the auditing using an external object. Create a new External Object. At the bottom of the Location dropdown, you will find two audit options:

*AUDITAFTR — Logs a single record to the audit table AFTER an action has been submitted.
*AUDITBOTH — Logs two records. One of the record BEFORE the action and another AFTER the action. To link the two records together, you will want to map a unique ID parameter to a char(100) field in your audit table (explained further below).

Once an audit location has been selected, the object dropdown will become a list of all maintainers in the current dictionary. Find and select the audit maintainer set up previously. The master maintainer fields should automatically map to the matching fields of the audit maintainer. However, you may want to map your audit fields to special parameters.

Map a Constant — (Optional) Enter the name attribute of an input from the master maintainer. Typically used for adding comments as to the reason a record is being changed.
Map a Unique ID — (Optional). When using the *AUDITBOTH location to record the before and after action records, the two records can be linked together with a unique ID. The ID is a 36 character string that will be identical for the matching before and after records.

Reading the Audit Logs

Depending on which audit location you select, there will be a single log entry of how the record exists AFTER the maintainer is submitted (*AUDITAFTER) or two log entries, one for how the record existed BEFORE the submission and how the record exists AFTER the submission (*AUDITBOTH).

How you decide to interpret and use the logging table is your choice. One method to read/analyze the data is by creating m-Power applications over the audit table.

*AUDITAFTR

Above you can see a record for every change made from the maintainer. The date, time, user, program, and action were all recorded automatically based off the audit logic added in the Field Settings screen of the audit maintainer. Additionally, there is a comment field that was written from the master maintainer as well.

*AUDITBOTH

With the audit both location set, two records are made for the maintainer actions. Mapping a Unique ID from the external object to the Change ID field links the before and after records together. Notice the Action field indicates the before and after actions, except for adding a new record where there is only logging of the record after added.