Saturday, Oct. 21, 2017

How to Decode Siebel 8 Audit Trail

Written By:

|

August 27, 2013

|

Posted In:

Siebel Audit Trail Stores the changes in columns for the business components for which it is activated. Now, till Siebel 7.8 it was very simple. Take the columns which are being updated, store the new and old values for the column and the timestamp required. But in Siebel 8.0 and above, they dropped a bomb over the programmers who want to do the analysis of these old and new values using database scripts. Actually we can’t blame them, storing each column data in a separate row used to make the size of table insanely huge. So they gathered all the column data in a single column called AUDIT_LOG and they formatted the data in a way that can be decoded using their VBCs and internal Siebel scripts but becomes pain in the ass for DB programmers. So, to help my Siebel brothers and sisters I have created a PL/SQL procedure which can be used to decode this data via database scripts and manipulated further as required.

Understanding the AUDIT_LOG encryption method (Read this only if you want to dive into the details of audit trail storage, for PL/SQL ready to use procedure you can skip this section):

If you see any AUDIT_LOG, you’ll find crap like :

(CLOB) 2*J215*Service Account34*GEHC Physical Street Address Line12*L232*AMBULATORY SURGICAL CENTER OF NJ18*9900 Innovation Dr2*K20*0*2*C110*SERIAL_NUM2*N19*2345-89732*O19*2345-8970

Although this string looks like a mess, it’s a very intelligent and efficient method for storing data. I’ll give u just an overview. This string can be broken down into various entities. Each entity has the format :

<String Length L1>* (asterisk)<Entity String with Length L1>

For e.g. above string can be broken down in entities like :

2*J2
15*Service Account
34*GEHC Physical Street Address Line1
2*L2
32*AMBULATORY SURGICAL CENTER OF NJ
18*9900 Innovation Dr
2*K2
0*
0*
2*C1
10*SERIAL_NUM
2*N1
9*2345-8973
2*O1
9*2345-8970

Now, basically there are 6 markers which tell you the type of entity you are going to encounter:

J followed by L and K

C followed by N and O

When you encounter J or C, you’ll find column names specified after them

When you encounter L or N, you’ll find new column values specified after them

When you encounter K or O, you’ll find old column values specified after them

For e.g. for the above string,

2*J2
Means you’ll find 2 column names directly after it(in specified format)

2*L2
Means you’ll find 2 new column values directly after it(in specified format) for the columns specified after 2*J2

2*K2
Means you’ll find 2 old column values directly after it (in specified format) for the columns specified after 2*J2

Same thing happens for {C,N,O} set

And finally, 0* means a NULL value.

Now it’s time for ready to use PL/SQL package for decoding this column(AUDIT_LOG):

DECLARE

alog varchar2(1000) := ‘(CLOB) 2*J119*Territory Hierarchy2*L113*AM US Midwest2*K10*2*C413*ACTIVATION_DT9*STATUS_CD7*TYPE_CD4*NAME2*N419*2011-02-14 21:46:4311*In Progress5*Major4*TEST2*O40*0*0*0*’;

TYPE vcarray IS VARRAY(1000) OF VARCHAR2(100);
colum varchar2(100):=’STATUS_CD’;
cname varchar2(100);
cval varchar2(100);
idf  varchar2(10);
c NUMBER(4) := 1;
len NUMBER(4);
strarr vcarray;
strarr2 vcarray;
seed NUMBER(10);
total NUMBER(10) := 0;
elen NUMBER(10);
tempstr varchar2(1000);
noc NUMBER(10);
noc2 NUMBER(10);
old NUMBER(10);
new NUMBER(10);
found NUMBER(10):= 0;
foundin NUMBER(10):= 0;
break NUMBER(10):= 0;
itemcounter NUMBER(10):= 1;

BEGIN
len := LENGTH(alog);
strarr := vcarray(‘NULL’);
strarr2 := vcarray(‘NULL’);
strarr.extend(998,1);
strarr2.extend(998,1);
seed:= TO_NUMBER(SUBSTR(alog,8,1));
total:= 8 + seed;
strarr(1) := SUBSTR(alog,total,seed);
noc:= SUBSTR(strarr(1),2,LENGTH(strarr(1))-1);
tempstr := alog;
total:=total + seed;
c:=2;

WHILE total < len-2 LOOP
tempstr := SUBSTR(alog,total,len-total);
seed := INSTR(tempstr, ‘*’)-1;
elen:= TO_NUMBER(SUBSTR(alog,total,seed));
total := total + seed + 1;
strarr(c) := SUBSTR(alog,total,elen);
IF strarr(c) IS NULL THEN
strarr(c):= ‘NULL’;
END IF;
itemcounter:= itemcounter + 1;
total := total + elen ;
c:=c+1;
END LOOP;

elen:=0;

FOR c in 1..itemcounter LOOP
IF SUBSTR(strarr(c),1,1) = ‘C’ AND LENGTH(strarr(c)) < 4 THEN
found :=1;
break := c;
END IF;
IF SUBSTR(strarr(c),1,1) = ‘N’ AND LENGTH(strarr(c)) < 4 THEN
found := found + 1;
END IF;
IF SUBSTR(strarr(c),1,1) = ‘O’ AND LENGTH(strarr(c)) < 4 THEN
found :=found + 1;
END IF;
END LOOP;

IF found >= 3 THEN
FOR c in 1..itemcounter-break + 1 LOOP
strarr2(c) := strarr(break -1 + c);
strarr(break – 1 + c) := NULL;
END LOOP;
END IF;

FOR c in 1..itemcounter LOOP
IF strarr(c) = colum THEN
elen := c;
foundin:=1;
END IF;
END LOOP;

FOR c in 1..itemcounter-break + 1 LOOP
IF strarr2(c) = colum THEN
elen := c;
foundin:=2;
END IF;
END LOOP;

IF elen = 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Column Value doesnt Exist’);
ELSE
IF foundin = 1 THEN
new := elen  + noc + 1;
old := elen + noc*2 + 2;
DBMS_OUTPUT.PUT_LINE(‘OLD VALUE:’ || strarr(old));
DBMS_OUTPUT.PUT_LINE(‘NEW VALUE:’ || strarr(new));
END IF;

IF foundin = 2 THEN
noc2 := SUBSTR(strarr2(1),2,LENGTH(strarr2(1))-1);
new := elen  + noc2 + 1;
old := elen + noc2*2 + 2;
DBMS_OUTPUT.PUT_LINE(‘OLD VALUE:’ || strarr2(old));
DBMS_OUTPUT.PUT_LINE(‘NEW VALUE:’ || strarr2(new));
END IF;
END IF;
END;
/

Yes, it is long.. what were you expecting ? A 4 line procedure…? :)
Now, there are 2 input values for this procedure:
alog : AUDIT_LOG column data
colum : the name of field whose OLD/NEW value you want to take out.

You can convert above procedure into a function with above input parameters and use the output as you like(It is there at the end in DBMS_OUTPUT.PUT_LINE statements). The output for this procedure will look like :

anonymous block completed
OLD VALUE:NULL
NEW VALUE:In Progress

Just keep in mind one thing, I made this procedure on Oracle client, so if you are using IBM DB2 or Toad or some other client, you’ll have to look for similar functions Syntax (if they are different).



Share This Article

About Author

Rohit

Siebel Technical Consultant

  • Manjunath

    Very simple and nice explanation. We have implemented a similar procedure to do the needs. Unfortunately our audit table is growing in size and our report extraction process is increasing in execution times. Doing some research and came across your site. Well done job. Cheers!!

    • Rohit

      Happy to Help !!

  • Kannappan

    We found 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.

    Output Format:
    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
    ;

    • Priya

      This is really brilliant..We too have a very huge data set for Audit..this query is a charm..thnxx :)

    • Arun MS

      This one is amazing Kannappan…Thanks.

      Since I could not find a user id with DDL access on our DB, here is what I modified your query to, and it worked like charm!!

      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 (
      SELECT operation_cd, operation_dt, tbl_name, 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
      –MS:Searchspec to fetch audit item data record for post 8.0 audit records
      WHERE buscomp_name = ‘Service Request’ AND record_id = ‘2-1ERMZTN’
      ) MS_AUDIT_ITEM_RECORD_1,
      (
      SELECT LEVEL seq FROM dual CONNECT BY LEVEL 0;

      • Niranjan

        Hi Arun,

        I tried to use your query. It is working quite awesome. but, there is a small thing i observed in column list is “Attribute” fields are coming like ‘ATTRIB_’. it is not coming properly like ATTRIB_01,ATTRIB_02,etc.,. If possible could you please check the query and if it overcoming, Then it is awesome query to convert audit log.

        Thank You