PeopleSoft Audit

To help us keep track of changes done to sensitive data by appropriate user, we use Audit.
Later we can query the Audit record to see the list of changes happened.

In PeopleSoft, we can set Audit at
1. Record Level
2. Field Level
3. Database Level

For Record Level and Field Level, Audit captures changes done to data for the online page. If the data is changed by any program using a SQL statement then Audit is not captured.

For Database Level, It covers both online and database changes done to the transaction record.

Base Records 

The base record is the record that you want to monitor, or audit, as in BU_ITEMS_INV. Presumably; the base record contains fields that you want to monitor. Limit the auditing of tables to the application tables and avoid auditing People Tools tables.

Audit Record

The audit record is a custom record that you create with Application Designer. It stores the audit information for the fields on the base record that the trigger collects. A sample name for audit record might be DPI_BUITM_AUDIT.

Implement Record level Audit 

Record level auditing allows you to have separate audit tables dedicated to one database record.

A)     Choose the Record:

Not all records in PeopleSoft need auditing. When there is such a business requirement, choose the record(s) that needs to be audited.

B)      Create the Audit Record:

The Audit record is essentially a copy of the record being audited. The easiest way to create a copy of your record is to do a Save As. Name the new record under standard record naming convention prefixed with AUDIT_

C)      Remove the unwanted: Delete fields that won’t be audited and Remove all key structures

D)     Add Audit Fields:

Insert the below fields (also known as audit fields) at the top of the new record in the below order

AUDIT_OPRID (Captures the User who performed an action)
AUDIT_STAMP (Captures the date and time when the action was performed)
AUDIT_ACTN (Captures the type of action performed)
AUDIT_RECNAME (Captures the name of the audited record definition. This field is used only when the same audit record is used for auditing multiple records)

On the Record Field Properties for AUDIT_STAMP, we need to check the Auto-Update checkbox. This is essential to correctly populate the date-time stamp.

E)  Build the audit record:

Your Audit record must be a Database record and you should be able to access it using Query.

F) Link them up
Open the Base Record that is to be audited. Open its properties. On the Use tab, under the Record Audit, specify the name of the audit record that we just created as the Record Name. Choose Audit Options based on your requirement.

 

Add – An Audit table row is inserted when a row is inserted in the base record
Delete – An Audit table row is inserted when a row is deleted from the base record
Change – Audit table row(s) is inserted when a row changes in the base record
Selective – Audit table row(s) is inserted when common field (present in both base record and audit record) changes

G)     Test the Auditing:  

                                                  Based on the Audit option that you have chosen, perform some transaction that can trigger the audit. Now query the audit record and check for new rows. Try to infer the meaning of each row that got added.

H)     AUDIT_ACTN :

A – Row inserted
D – Row deleted.
C – Row changed (updated), but no key fields changed.
K – Row changed (updated), and at least one key field changed.
N – Row changed (updated), and at least one key field changed.

In case of C and K the system writes old values to the audit table. While for N, the new values are written into the audit table.

Implement Field level Audit 

Field-level audits record when you insert, update, or delete a field value. The system writes a row of audit data to the PSAUDIT table for each field marked for audit. You may write multiple rows of audit data for a single transaction.

1.        The PSAUDIT table contains the following fields:

 • AUDIT_OPRID
• AUDIT_STAMP
• AUDIT_ACTN
• RECNAME
• FIELDNAME
• OLDVALUE
• NEWVALUE
• KEY1, KEY2, …

              2.      A field level audit is a record field property. To configure a field level audit

a)     Open the desired record.

b)     Double click the field you would like to audit.

c)      In the Audit section, select Field Add, Field Change, or Field Delete.

 

Field Add: Audits this field whenever a new row of data is added

Field Change: Audits this field whenever the content changes.

Field Delete: Audits this field whenever a row of data is deleted.

 

d)     SELECT * FROM PSAUDIT WHERE RECNAME='YOUR RECORD' AND FIELDNAME ='FIELD NAME'; 

 

Implement Database level Audit 

PeopleSoft's delivered Audit feature works only for changes made from online. We depend on RDBMS's trigger feature to track changes made through non-PeopleSoft methods. PeopleSoft has come up with a Database Level auditing feature using which we can combine both the PS delivered Audit and Database's Trigger feature

Trigger

The trigger is the mechanism that a user invokes upon making a change to a specified field. The trigger stores the audit information in the audit table. PeopleSoft enables you to create triggers. A sample name for a trigger might be VENDOR_TR.

Note: If you modify the record definition of the base record, then you must modify the audit record and re-create the associated trigger.

     Update Database Level Auditing:

Navigation: People Tools > Utilities > Audit > Update Database Level Auditing

1) Search for the PeopleSoft record name to audit. The system by default will create audit trigger record with the base record name suffixed by _TR

2) Select the Audit record name.

3) Audit Options available are Add, Change or Delete

4) Click on "Generate Code". This will create the SQL statement to use for the Trigger creation

5) "Create Trigger Statement" section should have the SQL statement generated by above action

Note:

1) All the information entered above, gets captured in the PeopleTools trigger definition table – PSTRIGGERDEFN.

2) EnableDBMonitoring domain parameter must be set in PSADMIN for this feature to work

Perform Database Level Audit:

Navigation: People Tools > Utilities > Audit > Update Database Level Auditing

1) Select or create a Run Control ID

2) Select either "Create All Triggers" or "Create Trigger(s) On" depending on the requirement

3) Click Run which will trigger the Audit Application Engine - TRGRAUDPROG(Auditing Triggers). This process will fetch every row in PSTRIGGERDEFN and writes Create Trigger statements to the file - TRGCODEX.SQL, where Z represents a digit determined by the number of files that already exist in the output directory.

4) This step only creates the SQL Statement, and it should be run using SQL tool to create trigger(s)

 

To Delete Audit Trigger:

            1) Select People Tools, Utilities, Audit, Update Database Level Auditing

            2) Open the trigger that you want to delete

            3) Clear all the Audit options (Add, Change, and Delete)

           4) Click Generate Code

           5) Click Save

           6) Drop the trigger name from the database

 

Migrating:-

When migrating projects containing records that had audit record changes made to them, ensure the “Set Target Audit Flags from Source” flag is set in Copy Options:

Otherwise, the audit properties for these records won’t get copied across to the target environment.