Decoding Audit Trail Data

I guess there is not need for introduction to Audit Trail but here it is anyway. Audit trail has changed drastically from Siebel 7.X to Siebel 8.X.

In addition to the enhancements there has been one major change in the way data is stored in the Audit Trail.

In earlier versions of Siebel you could find 1-to-1 mapping of fields on UI and columns on database for Audit Trail.With Siebel 8.X Audit Trail information is stored in the encoded format to improve the overall performance for the application.  So, there is no column storing Old Value and New Value. It is all dumped in encoded format in a column called AUDIT_LOG The Audit Trail business componentAudit Trail Item 2” is a VBC which decodes information and then display it in UI. Here is a screenshot showing the how the data is store in S_AUDIT_ITEM

AuditTrailTable

I was able to find a nice article in Oracle Support explaining how to decode Audit Trail information. So, if you guys are interested, follow this link and  you can download small PDF explaining the details.

8 Responses to Decoding Audit Trail Data

  1. For newly created audit records there would be id of foreign key in this string. for modify will look like the following.

    2*C111*EVT_STAT_CD2*N19*Completed2*O14*Open

    2*C1 — number of columns that has changed. in this case 1. first 2* is number of char following
    11*EVT_STAT_CD — name of the column
    2*N1 — represent new
    9*Completed — represent new value
    2*O1– represent old
    4*Open — represent Old value

    any questions you can mail me.

  2. Hi Neel,
    Thanks for sharing the article…I couldn,t access the link which you have shared ..Can you please share a new link to download that pdf…

    Thanks,
    Siri

  3. Hi there,

    I’ve followed the link but i couldn’t find the pdf.
    Can you send a new link or email that file?

    Thanks in advance.

    Best Regards,

  4. Has anyone implemented such decoding that actually writes to a custom table and can be read? Doesn’t look possible.

  5. For all future readers here,
    Extracting Audit trail from within Siebel via Workflow or Business Service, here is the link
    http://docs.oracle.com/cd/E14004_01/books/AppsAdmin/AppsAdminAuditTrail16.html

    If you are looking to build a package on your database, here are 2 links I came across
    http://it.toolbox.com/blogs/siebel-answers/how-to-sql-query-for-siebel-audit-trail-data-in-siebel-8-39693
    http://www.siebelnova.com/how-to-decode-siebel-8-audit-trail/

    Hope that helps.

  6. We have implemented a simple method to decode the Siebel 8.0 audit data into old structure. Basically it is a simple sql script and it gives the output in following format. Try it and let me know if it works for you.

    TBL_NAME, RECORD_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE

    Here is the script:

    create table temp_aud1 as
    select
    aud.operation_cd,
    aud.operation_dt,
    aud.tbl_name,
    aud.record_id,
    replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, ’2*C’)+4, DBMS_LOB.INSTR( audit_log, ’2*N’)- (DBMS_LOB.INSTR( audit_log, ’2*C’)+4)),’1234567890′,’ ‘),’*’,’,’)Column_List,
    replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, ’2*O’)+4),’1234567890′,’ ‘),’*’,’,’) Old_Value,
    replace(translate(SUBSTR(DBMS_LOB.SUBSTR (audit_log), DBMS_LOB.INSTR( audit_log, ’2*N’)+4, DBMS_LOB.INSTR( audit_log, ’2*O’)- (DBMS_LOB.INSTR( audit_log, ’2*N’)+4)),’1234567890′,’ ‘),’*’,’,’) New_Value
    FROM siebel.s_audit_item aud
    WHERE
    aud.buscomp_name = ‘Account’
    AND aud.OPERATION_DT >= sysdate – 1 ;

    select
    tbl_name,
    Record_id,
    Operation_dt,
    Operation_cd,
    substr(column_list, instr(column_list,’,’,1,seq)+1, instr(column_list||’,’,’,’,1,seq+1) – instr(column_list,’,’,1,seq)-1) Field_name,
    substr(old_value, instr(old_value,’,’,1,seq)+1, instr(old_value||’,’,’,’,1,seq+1) – instr(old_value,’,’,1,seq)-1) Old_value,
    substr(new_value, instr(new_value,’,’,1,seq)+1, instr(new_value||’,’,’,’,1,seq+1) – instr(new_value,’,’,1,seq)-1) New_value
    from temp_aud1, (select level seq from dual connect by level 0
    ;

Leave a Reply

Contribute