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:
xxxxxxxxxxselect RECNAME, RECDESCR, AUDITRECNAME, RECUSE from PSRECDEFNwhere 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:
xxxxxxxxxxselect 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_SELECTIVEfrom PSRECDEFNwhere AUDITRECNAME != ' 'order by RECNAME;In SQL Server, use the & operator like so:
xxxxxxxxxxselect 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_SELECTIVEfrom PSRECDEFNwhere AUDITRECNAME != ' 'order by RECNAMEOne 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):
xxxxxxxxxxselect D.RECNAME, D.RECDESCR, D.AUDITRECNAME, D.RECUSE, FA.FIELDNAME, F.FIELDNUMfrom 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:
xxxxxxxxxxselect 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_DELETEfrom PSRECFIELD Fwhere 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:
xxxxxxxxxxselect 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_DELETEfrom PSRECFIELD Fwhere 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
No Comments