Try OpenEdge Now
skip to main content
Core Business Services - Security and Auditing
Auditing : Querying and Reporting on Audit Data : Audit data querying and reporting : Reporting modified field old/new values
Reporting modified field old/new values
The recording of modified old and new field values in the child _aud-audit-data-value table is optional and driven by the level settings in the table policy. It is also currently relevant only to database CREATE, UPDATE, and DELETE events.
It is optional whether to record modified field values at all. When modified field values are recorded, using the child table to record each modified value in a separate record is also optional—driven by file and field policy. The default is rather to record a delimited list of modified field values in the parent table _aud-audit-data in the _event-detail field, as this is generally the better performing option but is harder to query and report against.
One option is not to record any old or new values, in which case no child records will exist, and the only means to determine what the change relates to is by the identifying field values in the event context.
A second option is to record only new, modified values. Determining what the value changed from could require the reading of many prior records until a match is found to modify the same field.
The final option is to record both old and new values to make it easier to report on exactly what the value changed from and to.
Note: The recording of old and new values for BLOBs and CLOBs is not supported.
These settings can be modified for individual fields, perhaps to record only the fact that a record was changed for the majority of fields and to record the actual value change for just a few specific fields. Additionally, specific (large) fields could be set to record modified values in separate child records while other fields are recorded in a delimited string in the parent record. Therefore, determining why and where certain values exist in the audit data tables would require knowledge of the audit policy that was in place at the time. Reports should handle the fact that these detail records might or might not exist.
Given the flexibility permitted with recording modified field values, reports should always check both the parent record _event-detail field and the child records in order to gather up all of the modified field values. The simplest approach is to turn the delimited list of modified field values back into separate child records in memory for reporting (using a ProDataSet). Even if the file or field policy is set not to record modified values in separate child records, if the data does not fit into the record, then the default mode is ignored and separate records are recorded for each modified field value. This is another reason why both locations must always be checked, regardless of the policy settings.
Apart from BLOBs and CLOBs, the modified values themselves are stored in a character field using an American format for type conversions. To facilitate casting of the value back to the correct data type and appropriate format, the originating data type of the field is also recorded.
The data type is an integer code with the sample values shown in the following table:
Table 7. Sample data type values
Data type
Sample value
Because the field name, old value, and new value are all being stored, it is possible that the record limit of 32K could be achieved if the field values are very large. To avoid this, the audit data value record supports continuation records through the _continuation-sequence field. The purpose of the continuation sequence is, therefore, to facilitate breaking a single audit record value into multiple parts to avoid breaking record limits where the audit data value is too large to fit into a single record.
When reporting is being done on old or new values, take into consideration the fact that the data can be spread over multiple records. Ordinarily, the continuation sequence will be 0. If continuation records exist for the same _Audit-data-guid and _Field-name, then additional records will exist with a continuation sequence greater than 0, each containing as much of the remaining data as possible.
Where a modified field value is for an array field, the value will be stored chr(8) delimited as follows:
Format: E[n]:<value1>chr(8)E[n]:<value2>chr(8)
Here is an example:
E[n] means the nth element in the array; the array index starts from 1.
When recording modified field values in the parent record in _aud-audit-data._event-detail, the structure of the data is as follows:
fieldname + chr(6) + data type code + chr(6) + old value + chr(6) + new value + chr(7)
The old value entry is only relevant to update level 13, so in all other cases it will be left blank to ensure a consistent number of entries to simplify reporting.
When modified field values are recorded this way it is not possible to efficiently search for where a specific field changed and to what value. If this is important, then store the field in separate child records, balancing the operational performance overhead against reportability.