Enabling Auditing in PeopleSoft

It is very important that you keep track of the changes happening to your key or sensitive transaction records. This will help you in determining who has changed what record and at what time. Instead of searching in the darkness after having a mishap with your data, it is always better to keep track (audit) the changes that is happening to your key records. Towards this purpose PeopleSoft has provided two different methods to audit your records.

PeopleSoft provides two levels of auditing, Field Level Auditing & Record Level Auditing.

Field Level Auditing

You can use this level of auditing if you want to track each and every change happening to a particular field in a record.  You can enable as many as fields for auditing in a record. To enable field level auditing, open up the record field properties. There you will get three options, select the desired options.

Fig 1. Field Level Auditing

Field Add option will track whenever a value is added to that selected field. Checking Field Change option will create an entry whenever you change the value of the field. By checking Field Delete option, a new row is created in the audit record whenever the field value is deleted.

All your audit options are tracked in a PeopleSoft delivered audit record called PSAUDIT. The audit record will capture the details like who changed the field, what time the change was made, what was the change, the field name, old values and new values and the keys for your parent record.

AUDIT_ACTN field stores what was the change made to the field. The values of this field can be interpreted as follows.

A – Added new value or row

C – Changed the existing value or row

D – Deleted the old value or row

K – Row updated, Old Value

N –Row Updated, New Value

O – Original Value

You can check the changes to your base record field by using a query similar to the one below.

select * from psaudit where recname = '<Your Record Name>' and fieldname = '<Your Field Name>' and key1 = '<First Key Value for your base record>' and key2 = '<Second Key Value for your base record>' <and so on till you map all the keys>

Record Level Auditing

Field level auditing is auditing each fields on your record and will be creating a new row for change in all the fields. This can grow up your audit record size and end up in performance issue. Also if you have many fields to audit in a record, then it becomes difficult for you to see through the report to see all the actions done on one particular record as it will result in multiple rows.

To tackle this situation PeopleSoft has provided another level of auditing called Record Level Auditing. With record level auditing, you can enable the audit for entire record and select the fields to be included in the audit. This will be particularly helpful when you have multiple fields in a record to be audited.

For creating record level audit, you need to create a new audit record for your base record. This record should follow certain standards. The easiest way to create an audit record is to open your audit record and save it as AUDIT_<Your Name>. Once the audit record is saved you can delete the unwanted fields (fields which need not be audited).

Other important steps you need to make are as follows.

1.       Remove all your keys. Audit records are not supposed to contain keys.

2.       Remove any Query Security record associated with it.

3.       If there are any Parent record associated, then remove it.

4.       Remove any PeopleCode associated with the audit record.

5.       Add the below delivered fields as required

   a.       AUDIT_OPRID – This field will capture the operator id of the person who has made the change.

   b.      AUDIT_STAMP – This field stores the date time stamp at which the change is made.

   c.       AUDIT_ACTN – This field stores what action was taken to the record. The values are as below.

                                                               i.      A: Row is inserted

                                                             ii.      D: Row deleted

                                                            iii.      C: Row changed (updated), but no key fields changed.

The system writes existing values to the audit table.

                                                           iv.      K: Row changed (updated), and at least one key field changed.

The system writes existing values to the audit table.

                                                             v.      N: Row changed (updated), and at least one key field changed.

                                    The system writes new values to the audit table.

   d.      AUDIT_RECNAME – This field stores which record was audited. Include this field only if you are using the same audit record for multiple base records.

Once you are done with it, you are ready with your audit record. Now to enable the auditing for your base table, open the base record properties and go to the use tab. There you should specify the name of the new audit record you have created and check the audit actions that you need to enable for the base record.

Fig 2. Record Level Auditing

As opposed to the field level auditing, you have an additional option called Selective. This will insert a row in an audit table whenever a value of the field included in the audit table is changed in the base table. If you click on change option, then the audit record will capture the row regardless of whether it is included in the audit record or not.

With this, you need to make a special note that the auditing will happen only if the user adds/deletes/changes the data from PeopleSoft application. If the data is changed by sqls in backend or via any third party, PeopleSoft audit tables will not capture these details. To capture that details as well, then you need to consider enabling database triggers.

To enable database level triggers, PeopleSoft has delivered some options by going to the online portal. You can define your triggers by going to People Tools > Utilities > Audit > Update Database Level Auditing

Fig 3. Database level auditing