What is Audited?

When there's an issue, a frequent question that comes up is: what is being audited? This is closely followed by, "can we tell what was changed, and by whom?"

The following SQL will help you determine which records and fields have field and record auditing enabled.

 NOTE: this article is specific to PeopleSoft auditing, and does not include trigger based auditing or other database level auditing.

Record Auditing

The following SQL identifies all records that have an audit record associated with them:

select

 RECNAME,

 RECDESCR,

 AUDITRECNAME,

 RECUSE 

from PSRECDEFN

where AUDITRECNAME != ' '

order by RECNAME;

The RECUSE field is a bit field with the following combinations:

bit 0 add = 1

bit 1 change = 2

bit 2 delete = 4

bit 3 selective = 8

So if you have a RECUSE value of 7, that is a combination Add (1) + Change (2) + Delete (4) which gives you a total of 7.

In Oracle, you can use the bitand operator to work this out for you like this:

select

 RECNAME,

 RECDESCR,

 AUDITRECNAME,

 case when bitand(RECUSE,1) > 0 then 'Y' else 'N' END AUDIT_ADD,

 case when bitand(RECUSE,2) > 0 then 'Y' else 'N' END AUDIT_CHANGE,

 case when bitand(RECUSE,4) > 0 then 'Y' else 'N' END AUDIT_DELETE,

 case when bitand(RECUSE,8) > 0 then 'Y' else 'N' END AUDIT_SELECTIVE

from PSRECDEFN

where AUDITRECNAME != ' '

order by RECNAME;

In SQL Server, use the & operator like so:

select

 RECNAME,

 RECDESCR,

 AUDITRECNAME,

 RECUSE,

 case when RECUSE & 1 > 0 then 'Y' else 'N' end AUDIT_ADD,

 case when RECUSE & 2 > 0 then 'Y' else 'N' end AUDIT_CHANGE,

 case when RECUSE & 4 > 0 then 'Y' else 'N' end AUDIT_DELETE,

 case when RECUSE & 8 > 0 then 'Y' else 'N' end AUDIT_SELECTIVE

from PSRECDEFN

where AUDITRECNAME != ' '

order by RECNAME

One problem you may run into is that not every field is audited in the audit record. The following SQL gives the fields that match between the record and the audit record (which means they are actually audited):

select

 D.RECNAME, 

 D.RECDESCR,

 D.AUDITRECNAME,

 D.RECUSE,

 FA.FIELDNAME,

 F.FIELDNUM

from 

 PSRECDEFN D inner join PSRECFIELD F

 on D.RECNAME = F.RECNAME

 inner join PSRECFIELD FA

 on FA.RECNAME = D.AUDITRECNAME

 and FA.FIELDNAME = F.FIELDNAME 

where 

 D.AUDITRECNAME != ' ' 

order by D.RECNAME, F.FIELDNUM;

Field Auditing

The following SQL identifies fields on records that have field level auditing to PSAUDIT enabled. Note that it uses the USEEDIT field, specifically the following:

bit 3 add = 8

bit 7 change = 128

bit 10 delete = 1024

This is the script to use for an Oracle database:

select

 F.RECNAME,

 F.FIELDNUM,

 F.FIELDNAME,

 F.USEEDIT,

 case when bitand(F.USEEDIT,8) > 0 then 'Y' else 'N' end AUDIT_FIELD_ADD,

 case when bitand(F.USEEDIT,128) > 0 then 'Y' else 'N' end AUDIT_FIELD_CHANGE,

 case when bitand(F.USEEDIT,1024) > 0 then 'Y' else 'N' end AUDIT_FIELD_DELETE

from

 PSRECFIELD F

where

 F.FIELDNAME = (

 select

 case when (

 bitand(USEEDIT,8) > 0 or

 bitand(USEEDIT,128) > 0 or

 bitand(USEEDIT,1024) > 0

 ) then FIELDNAME else '' end as FIELD_AUDITED

 from PSRECFIELD

 where RECNAME = F.RECNAME

 and FIELDNAME = F.FIELDNAME

 )

order by F.RECNAME, F.FIELDNUM;

This is the equivalent script for a SQL Server database:

select

 F.RECNAME,

 F.FIELDNUM,

 F.FIELDNAME,

 F.USEEDIT,

 case when F.USEEDIT & 8 > 0 then 'Y' else 'N' end AUDIT_FIELD_ADD,

 case when F.USEEDIT & 128 > 0 then 'Y' else 'N' end AUDIT_FIELD_CHANGE,

 case when F.USEEDIT & 1024 > 0 then 'Y' else 'N' end AUDIT_FIELD_DELETE

from

 PSRECFIELD F

where

 F.FIELDNAME = (

 select

 case when (

 USEEDIT & 8 > 0 or

 USEEDIT & 128 > 0 or

 USEEDIT & 1024 > 0

 ) then FIELDNAME else '' end as FIELD_AUDITED

 from PSRECFIELD

 where RECNAME = F.RECNAME

 and FIELDNAME = F.FIELDNAME

 )

order by F.RECNAME, F.FIELDNUM