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.

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