Extracting Audit Log Data

Use this procedure to extract audit log data for the purpose of archiving, monitoring, or troubleshooting. This procedure explains where the data is located, how it can be interpreted, and how it can be extracted.

The audit log data is stored in tables with the following naming format: S@tablePrefix, where tablePrefix is a shortened version of the business class table name. You can view table prefixes via the displaydict utility. In these tables, the data is in SQL columns named AUDITLOG and EFFECTIVEDATEDLOG.

The data is in XML format. Below is a sample entry showing this format, followed by a description of its elements:

<entry systemtime="2007112720145823" applied="2007112720145823" timestamp="2007112720145823" actor="jill" authenticatedactor="frank" effective="20071127" iva="Employee.Create" ip="[172.99.00.001]" action="Create" type="create" s="SUBJECT" r="REASON" actionrequest="" id="01284ce5-24ee-0000-0000-f34448d9f56b" agent="01284ce5-24ee-0000-0000-f34448d9f56b" ia="WorkAssignment.Add" tid="01284ce5-24ee-0000-0000-f34448d9f56b" tz="">
 <c><![CDATA[this is a comment]]></c>
 <t><![CDATA[tag1,tag2,tag3]]></t>
 <field name="" view="" o="" t="" s="" d="">
 <ov>![CDATA[old value]]></ov>
 <nv>![CDATA[new value]]></nv>
 </field>
 </entry> 
 <correction systemtime="2007112720145823" timestamp="2007112720145823" actor="jill" authenticatedactor="frank" ne="20071127" iva="Employee.SomeAction" ip="[172.99.00.001]" action="AuditUpdate" type="correction" ns="NEWSUBJECT" nr="NEWREASON" actionrequest="" or="01284ce5-24ee-0000-0000-d34448d9f56b" id="01284ce5-24ee-0000-0000-f34448d9f56b" agent="01284ce5-24ee-0000-0000-f34448d9f56b" ia="WorkAssignment.AnotherAction" tid="01284ce5-24ee-0000-0000-f34448d9f56b" ntz="">
 <c><![CDATA[this is a new comment]]></c>
 <t><![CDATA[newtag1,tag2,tag3]]></t>
 <field name="" o="" t="" s="" d="">
 <nv>![CDATA[new value]]></nv>
 </field>
 </entry>
Item Description
systemtime This is the actual time this entry was added based upon the system time of the machine. It is in the format YYYYMMDDHHMMSSFF.
applied This is the actual time this entry was applied to the system. It will differ from the time stamp when it is for a future dated transaction.
timestamp This is the time this entry was created in the system. It will differ from the system time stamp if a date override was used in testing.
actor The actor the transaction was run as.
authenticatedactor The actor authenticated by the system.
effective The effective date of the entry.
iva This is the action next up the stack from the action responsible for creating this entry (invoking action). It will default to Action.
ip The IP address of the server.
action The name of the action that created this entry. It will default to Create/Update/Delete based upon the action type.
type

This is the action type. Possible values:

  • 0 - Unknown (based on state of row)

  • 1 - Create

  • 2 - Update

  • 3 - Delete

  • 4 - Correction (audit/effective log correction)

  • 5 - Override (audit beginning log insertion)

  • 6 - Reverse (audit ending log insertion)

  • 7 - Invalidate (invalidate effective log entry)
ia The name of the action that was the entry point to the system (initiating action).
s or ns This is one of the system-defined reason subjects for the action/class executed. It must be a system- or configuration-defined subject.
r or nr The reason code for this action (configuration defined).
actionrequest The unique ID of the associated ActionRequest record.
id The unique ID of this entry. Each entry element is assigned a unique ID. This ID is different from and unrelated to the ID of the master row.
or The original id if this is a correction to an existing audit entry
tid This is the transaction id, it is generally the ID of the originating business classes audit entry id for the transaction. This is used to tie a set of audit entries together as originating from the same logical transaction.
agent This is the unique id of the associated agent record. This only applies for those business classes that implement the agent pattern.
tz This is the time zone that a future effective transaction to become active at 12:00AM.
c The reason comment for the transaction, plain text, can have line breaks
t The tags associated with this transaction, application defined.
field

The information associated with an individual field:

  • name - The field's name.

  • view - The field's view.

  • o - The number of the occurrence if an array field

  • t - The field's type.

  • s - The field's size.

  • d - The number of decimals for the field.

  • ov - The field's old value.

  • nv - The field's new value.

To extract audit log data

  1. Determine which business classes you want to extract audit log data for. You can identify the business class associated with a form by performing a Ctrl-Shift-click in the top section of the form. You can also see the business classes by viewing those listed under each module in the Data menu.
  2. Determine the name of the table where the audit log data is stored. It will be in the format S@businessClassPrefix. You can see the prefixes for business classes by running the displaydict utility. For example, the command displaydict gen actor will display, among other information, that the prefix for this table is ACTR. The audit log data can thus be found in the S@ACTR table.
  3. Extract the data. You can use Landmark database utilities. For example, the dbexport utility can export the data to a CSV file. You can also use native database utilities to extract the data in the AUDITLOG and EFFECTIVEDATEDLOG columns.