Siebel Applications Administration Guide > Siebel Audit Trail >

Decoding the Audit Log File

Audit trail data is stored in an encoded form in the Siebel database to maximize the performance of the overall Siebel application.

Audit Trail Item 2 Virtual Business Component

You can use the Audit Trail Item 2 virtual business component to export your audit trail data to a data warehouse. To maintain compatibility with data from previous Siebel versions, you must decode the new information and store the newly formatted records into the old format.

All audit trail values for a single transaction are grouped together into one record in the S_AUDIT_ITEM table. Previously, values were stored in the OLD_VAL and NEW_VAL fields. The NEW_VAL and OLD_VAL columns are no longer populated in S_AUDIT_ITEM table. Instead, the fields and values for an audit transaction are grouped into the AUDIT_LOG column of the S_AUDIT_ITEM table.

To decode audit items, you can call the Audit Trail Item 2 virtual business component. The Audit Trail Item 2 business component is no longer based on S_AUDIT_ITEM table. Instead, this business component is now a virtual business component that is based on a highly specialized class that has its own business logic to retrieve data from S_AUDIT_ITEM table.

NOTE:  Using the virtual business component to decode audit trail data is the only method that Oracle supports. The virtual business component is updated to reflect any changes in the underlying data structure.

Workflow to Decode Audit Data

The workflow that you use to decode audit data depends on how you use the data. If you are implementing an archival solution, then you might want to implement a solution that reads all created records. If you are implementing a more data-oriented solution, then you must implement a solution that has more control of the data.

The following is an example of a high level flow for reconstructing data:

1. Start the Audit Trail Item 2 virtual business component.
2. In a search time window, run a query for Audit Trail Item 2.
3. Run an query for Audit Trail Item - Data, where the date and time match the previous record.
4. Write out the decoded record ID: New Val, Old Val.

   This step is entirely dependant on your specific requirements and can range from writing to a custom table in the Siebel database to sending the decoded data to an external data source using one of the EAI Transports.

You can modify this flow to restrict the data returned by business component, user, position, and so on. However, the flow must always include the fundamental aspect of querying both the Audit Trail Item 2 virtual business component and the Audit Trail Item - Data business component to construct a record consistent with format of a previous version of a Siebel Audit Trail record.

Use this flow to make sure that duplicate records are not included in the export of audit trail data to a data warehouse. In this flow, row IDs are not obtained from the Audit Trail Item - Data business component to create records with unique row IDs but to identify already-exported records. This identification occurs in the data warehouse. (Due to the encoded format of the Audit Trail Item - Data business component, a virtual record in the Audit Trail Item 2 virtual business component does not always map one-to-one to a physical database record in the Audit Trail Item - Data business component. Consequently, a record for the Audit Trail Item 2 virtual business component does not always have a unique row ID.)

Next, you must create a trigger for the workflow you are using to decode audit data. You can use a reactive trigger through runtime events or workflow policies. If you need real-time or near-real-time onward transmission of the audit trail data, then consider using a reactive trigger. However, consider that the volume of transactions occurring on the audit tables is high, so any per-record solution might cause unwanted performance effects on the entire Siebel application. Any deployment using a reactive trigger on audit data requires extensive performance profiling.

For any situation that does not require real-time data transmission, consider an asynchronous batch approach using a mechanism such as repeating component requests. You can tune and adjust these requests so that the frequency of the job matches the data and performance requirements of the Siebel application. In many cases, you can time these requests to run during periods of reduced activity in the Siebel application. It is recommended that you use this approach for the majority of use cases.

You can use either a custom business service in conjunction with a workflow process, or you can use a workflow process on its own to implement your solution to decode audit trail data. Leveraging only a workflow process might assist in the long term maintenance of the solution if you think that it might require modification over time; however this approach also adds complexity to the solution due to the requirements of stepping through data sets and restricting records.

Example Business Service

This example provides sample code for a business service that you can use to retrieve and decode data; note however that the search specification is arbitrary. Carefully consider this search specification for individual implementations. You can apply a search specification to only fields that map to physical columns, and not to encoded fields.

/***********************************************************************************/
/* function: decodeAuditTrail
/*
/* The top level function to read audit trail data encoded in Siebel 8.x format    */
/* allowing the data to be exported as simple value pairs for purposes of archival */
/* or business intelligence.                                                       */
/************************************************************************************/

function decodeAuditTrail(strTimediff)

{

try {

// Create objects

var boAudit = TheApplication().GetBusObject("Audit Trail");

var bcAuditItem = boAudit.GetBusComp("Audit Trail Item 2");

with(bcAuditItem) {

SetViewMode(AllView);

ClearToQuery();

// Set the search specification. In this example we are only

// using time differential.

SetSearchSpec("Date", ">= " + strTimediff);

ExecuteQuery(ForwardOnly);

var bRecord = FirstRecord();

while(bRecord) {

//Retrieve field values

var strRecordId = GetFieldValue("Record Id");

var strBC = GetFieldValue("Business Component");

var strFieldName = GetFieldValue("Field");

var strOldVal = GetFieldValue("Old Value");

var strNewVal = GetFieldValue("New Value");

var strDate = GetFieldValue("Date");

//Query for underlying Row Id

/**************************************************************************/
/* function: getAuditRowId                                                */
/*
/* Retrieves the physical row ID associated with the decoded audit record to */
/* provide a defined cutoff for the audit export operation. In cases where  */
/* more than one record is retrieved, an error is thrown because the defined */
/* cutoff is not unique, and the operation is retried with new parameters.   */
/**************************************************************************/

var strAuditId = getAuditRowId(strRecordId, strDate);

//Placeholder for function to write out values

writeAuditValues(strAuditId, strBC, strFieldName, strRecordId,

strOldVal, strNewVal, strDate);

bRecord = NextRecord();

}

}

}

catch(e)

{

throw(e);

}

}

function getAuditRowId(strAuditRecordId, strAuditRecordDate)

{

var strReturn = "";

try

{

// Create objects

var boAudit = TheApplication().GetBusObject("Audit Trail");

var bcAuditData = boAudit.GetBusComp("Audit Trail Item - Data");

with(bcAuditData) {

SetViewMode(AllView);

ClearToQuery();

// Set the search specification for the combination of

// record Id and date

SetSearchSpec("Date", strAuditRecordDate);

SetSearchSpec("Record Id", strAuditRecordId);

ExecuteQuery(ForwardOnly);

var bRecord = FirstRecord();

if(bRecord) {

strReturn = GetFieldValue("Id");

// Check to see that we only have one record

bRecord = NextRecord();

if(bRecord) {

throw("Error: Multiple Record Id and Date records

identified");

}

}

else

strReturn = "";

}

}

catch(e)

{

throw(e);

}

return(strReturn);

}