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.
