Loading
Home » Configuration, Siebel SQLs & Database

Decode Siebel 8.1.x Audit Trail using a SQL?

29 December 2011 5 Comments

In new versions of Siebel CRM application, Oracle has encoded the audit trail to store it in encoded format in Siebel, instead of Separate columns of New and Old values. Hence it is not possible to get those values by directly reading the S_AUDIT_ITEM table.

So, I have created a Oracle SQL Procedure which will help you decode the audit trail and store in a temporary table, just like it was earlier. Here, you would need to provide Table, Column for which you want to grab the audit trail. You can specify multiple Table and Column Names in the invoking procedure by pipe delimitation.

 

You need to compile the below procedures 1 by 1 and execute the last one to get the audit table loaded.

I am using Schema Name: SIEBELDW which is present in my Siebel Datawarehouse, to avoid extra processing in SIEBEL DB.

Also, I have used SIEBELDW.WC_AUDIT_ITEM table which is a copy of SIEBEL.S_AUDIT_ITEM to load the audit table

Also I have used SIEBELDW.WC_AUDIT_DHS table to load the audit data after decoding.

 

Prerequisite Tables: WC_AUDIT_DHS and WC_AUDIT_ITEM
This table stores the decoded audit trail records


CREATE TABLE SIEBELDW.WC_AUDIT_DHS (
AUDIT_ID VARCHAR2 (15 CHAR),
RECORD_ID VARCHAR2 (15 CHAR),
BUSCOMP_NAME VARCHAR2 (75 CHAR),
OPERATION_CD VARCHAR2 (30 CHAR),
USER_ID VARCHAR2 (15 CHAR),
OPERATION_STRT_DT DATE,
OPERATION_END_DT DATE,
NEW_VAL VARCHAR2 (2000 CHAR),
OLD_VAL VARCHAR2 (2000 CHAR),
TBL_NAME VARCHAR2 (30 CHAR),
TBL_COLUMN VARCHAR2 (100 CHAR))

 

This table stores the audit records fetched from SIEBEL.S_AUDIT_ITEM table from Siebel.


CREATE TABLE SIEBELDW.WC_AUDIT_ITEM (
ITEM_IDEN_NUM NUMBER (15),
ROW_ID VARCHAR2 (15 CHAR),
CREATED DATE,
CREATED_BY VARCHAR2 (15 CHAR),
LAST_UPD DATE,
LAST_UPD_BY VARCHAR2 (15 CHAR),
MODIFICATION_NUM NUMBER (10),
CONFLICT_ID VARCHAR2 (15 CHAR),
BUSCOMP_NAME VARCHAR2 (75 CHAR),
OPERATION_CD VARCHAR2 (30 CHAR),
RECORD_ID VARCHAR2 (15 CHAR),
USER_ID VARCHAR2 (15 CHAR),
AUDIT_LOG CLOB,
DB_LAST_UPD DATE,
OPERATION_DT DATE,
SYNC_DT DATE,
AUDIT_SOURCE_CD VARCHAR2 (50 CHAR),
BC_BASE_TBL VARCHAR2 (30 CHAR),
CHILD_BC_BASE_TBL VARCHAR2 (30 CHAR),
CHILD_BC_NAME VARCHAR2 (75 CHAR),
CHILD_RECORD_ID VARCHAR2 (15 CHAR),
DB_LAST_UPD_SRC VARCHAR2 (50 CHAR),
FIELD_NAME VARCHAR2 (75 CHAR),
GROUP_NUM VARCHAR2 (15 CHAR),
LINK_NAME VARCHAR2 (175 CHAR),
NEW_VAL VARCHAR2 (2000 CHAR),
NODE_NAME VARCHAR2 (50 CHAR),
OLD_VAL VARCHAR2 (2000 CHAR),
SRC_DEST_ID VARCHAR2 (15 CHAR),
TBL_NAME VARCHAR2 (30 CHAR),
TBL_RECORD_ID VARCHAR2 (15 CHAR),
W_INSERT_DT DATE,
W_UPDATE_DT DATE
)

 

Procedure: 1


/* Formatted on 12/30/2011 2:27:30 PM (QP5 v5.126.903.23003) */
CREATE OR REPLACE PROCEDURE siebeldw.find_audit_values (
audit_log IN VARCHAR2,
column_name IN VARCHAR2,
old_val OUT VARCHAR2,
new_val OUT VARCHAR2
)
AS
n NUMBER; -- number of columns
n_new NUMBER; -- number of new values
n_old NUMBER; -- number of new values
col_n NUMBER;
pos1 NUMBER;
pos2 NUMBER;
len NUMBER;
i NUMBER;
format_type CHAR (1);
BEGIN
/* begin: go to '*c' */
pos2 := 0;
len := INSTR (audit_log, '*') - 2;

LOOP
pos1 := pos2 + len + 1;
pos2 := INSTR (audit_log, '*', pos1);

IF ( (pos2 = 0) OR (SUBSTR (audit_log, pos2 + 1, 1) = 'C'))
THEN
EXIT;
END IF;

len := TO_NUMBER (SUBSTR (audit_log, pos1, pos2 - pos1));
n := TO_NUMBER (SUBSTR (audit_log, pos2 + 2, len - 1));

FOR i IN 1 .. n
LOOP
pos1 := pos2 + len + 1;
pos2 := INSTR (audit_log, '*', pos1);
len := TO_NUMBER (SUBSTR (audit_log, pos1, pos2 - pos1));
pos1 := pos2 + 1;
END LOOP;
END LOOP;

/* end: go to '*c' */

-- exception - the string
IF (pos2 = 0)
THEN
RETURN;
END IF;

/* begin: get number of columns */
pos2 := INSTR (audit_log, '*', pos1);
len := TO_NUMBER (SUBSTR (audit_log, pos1, pos2 - pos1));
n := TO_NUMBER (SUBSTR (audit_log, pos2 + 2, len - 1));
/* end: get number of columns */

/* begin: find column number for the column we are looking for */
col_n := 0;

FOR i IN 1 .. n
LOOP
pos1 := pos2 + len + 1;
pos2 := INSTR (audit_log, '*', pos1);
len := TO_NUMBER (SUBSTR (audit_log, pos1, pos2 - pos1));
pos1 := pos2 + 1;

IF (column_name = SUBSTR (audit_log, pos1, len))
THEN
col_n := i;
--even if the match is found, the loop still has to continue in order to move the pos1 to the new values.
END IF;
END LOOP;

/* end: find column number corresponding to the column name */

-- exception - column name not found
IF (col_n = 0)
THEN
RETURN;
END IF;

/* begin: get number of new values */
pos1 := pos2 + len + 1;
pos2 := INSTR (audit_log, '*', pos1);
len := TO_NUMBER (SUBSTR (audit_log, pos1, pos2 - pos1));
n_new := TO_NUMBER (SUBSTR (audit_log, pos2 + 2, len - 1));

IF (SUBSTR (audit_log, pos2 + 1, 1) = 'N')
THEN
format_type := 'N'; -- it leads a list of new values
ELSIF (SUBSTR (audit_log, pos2 + 1, 1) = 'O')
THEN
format_type := 'O'; -- it leads a list of old values
END IF;

/* end: get number of new values */

/* begin: get 'new values' */
FOR i IN 1 .. n_new
LOOP
pos1 := pos2 + len + 1;
pos2 := INSTR (audit_log, '*', pos1);
len := TO_NUMBER (SUBSTR (audit_log, pos1, pos2 - pos1));
pos1 := pos2 + 1;

IF (i = col_n)
THEN -- find the corresponding value
IF (format_type = 'N')
THEN -- if it is a list of new values
new_val := SUBSTR (audit_log, pos1, len); -- assign to the corresponding output variable
ELSIF (format_type = 'O')
THEN -- if it is a list of old values
old_val := SUBSTR (audit_log, pos1, len); -- assign to the corresponding output variable
RETURN; -- the string only contains old values. normally it means there is a delete.
END IF;
END IF;
END LOOP;

/* end: get 'new values' */

/* begin: get number of old values */
pos1 := pos2 + len + 1;
pos2 := INSTR (audit_log, '*', pos1);
len := TO_NUMBER (SUBSTR (audit_log, pos1, pos2 - pos1));
n_old := TO_NUMBER (SUBSTR (audit_log, pos2 + 2, len - 1));

IF (SUBSTR (audit_log, pos2 + 1, 1) != 'O')
THEN
RETURN; -- the string only contains new values. normally it means there is an insert.
END IF;

/* end: get number of old values */

/* begin: get 'old values' */
FOR i IN 1 .. n_old
LOOP
pos1 := pos2 + len + 1;
pos2 := INSTR (audit_log, '*', pos1);
len := TO_NUMBER (SUBSTR (audit_log, pos1, pos2 - pos1));
pos1 := pos2 + 1;

IF (i = col_n)
THEN
old_val := SUBSTR (audit_log, pos1, len);
END IF;
END LOOP;
/* end: get 'old values' */

END;
/

 

Procedure: 2


/*
--How to invoke this procedure
--SIEBELDW.INSERT_AUDIT_TRAIL (TBL_NAME, COLUMN_NAME, PARM_FRM_DT , PARM_TO_DT, REC_COUNT);
*/
/* formatted on 12/21/2011 9:11:20 pm (qp5 v5.126.903.23003) */
create or replace procedure siebeldw.insert_audit_trail (
param_tbl_name in varchar2,
param_tbl_column in varchar2,
parm_frm_dt in date,
parm_to_dt in date,
rec_count out number
)
as
old_value varchar2 (100);
new_value varchar2 (100);
audit_log varchar2 (500);
colmn varchar2 (100);
prev_new_val varchar2 (100);
prev_old_val varchar2 (100);
temp_field varchar2 (100);
temp_audit_id varchar2 (100);
temp_count number;
-- rec_count number;
prev_audit_id varchar2 (255);
operation_end_dt_out date;
temp_record_id varchar2 (100);
exist_audit_id varchar2 (100);
cursor c1
is
select wc_audit_item.row_id,
wc_audit_item.buscomp_name,
wc_audit_item.operation_cd,
wc_audit_item.user_id,
wc_audit_item.tbl_name,
wc_audit_item.record_id,
to_char (wc_audit_item.operation_dt, 'MM/DD/YYYY hh:mi:ss')
as operation_dt,
wc_audit_item.audit_log
from siebeldw.wc_audit_item
where tbl_name = param_tbl_name
and (audit_log like '%' || param_tbl_column || '%')
and ( (trunc (created) >= parm_frm_dt)
and (trunc (created) <= parm_to_dt))
--and wc_audit_item.record_id in ('1-RDDJ', '1-NCCW', '1-3N1KV') -- this is for testing the procedure
order by wc_audit_item.created asc;
begin
temp_count := 0;

for item in c1
loop
temp_count := temp_count + 1;
rec_count := temp_count;
temp_field := param_tbl_name;
colmn := param_tbl_column;
siebeldw.find_audit_values (item.audit_log,
colmn,
old_value,
new_value);

if new_value is null
then
new_value := prev_new_val; -- assigining prev record values
old_value := prev_old_val;

if prev_new_val is null
then
new_value := temp_field;
end if;
end if;

--dbms_output.put_line( item.row_id || '|'|| item.record_id || '|' || new_value || '|' || old_value || '|' || item.operation_dt);

 

begin --- This block provides if the audit record already exist in the dhs table
select a.audit_id
into exist_audit_id
from siebeldw.wc_audit_dhs a
where a.audit_id = item.row_id
and a.tbl_column = colmn
and a.record_id = item.record_id;
--dbms_output.put_line ('audit record existence: ' || exist_audit_id);
exception
when no_data_found
then
exist_audit_id := '';
end;
if exist_audit_id is null
then
begin

prev_audit_id := '';
operation_end_dt_out := null;

select operation_end_dt
into operation_end_dt_out
from (select operation_end_dt
from ( select *
from siebeldw.wc_audit_dhs b
where b.tbl_name = temp_field
and b.tbl_column = colmn
and b.record_id = item.record_id
order by b.operation_strt_dt desc) x
where rownum = 1);
select audit_id
into prev_audit_id
from (select audit_id
from ( select *
from siebeldw.wc_audit_dhs b
where b.tbl_name = temp_field
and b.tbl_column = colmn
and b.record_id = item.record_id
order by b.operation_strt_dt desc) x
where rownum = 1);
exception
when no_data_found
then
prev_audit_id := '';
operation_end_dt_out := null;
end;

--dbms_output.put_line( 'OUTPUT: '|| temp_field|| ' '|| colmn|| ' '|| item.record_id|| ' '|| prev_audit_id|| ' '|| to_char (operation_end_dt_out,'MM/DD/YYYY HH24:MI:SS AM'));

if prev_audit_id is not null
then
update siebeldw.wc_audit_dhs c
set c.operation_end_dt =
to_date (item.operation_dt, 'MM/DD/YYYY HH24:MI:SS')
where c.audit_id = prev_audit_id
and c.record_id = item.record_id
and c.tbl_column = colmn;

commit;
--dbms_output.put_line ('previous date: ' || item.operation_dt);
end if;

 

insert into siebeldw.wc_audit_dhs (record_id,
buscomp_name,
operation_cd,
user_id,
tbl_name,
tbl_column,
audit_id,
new_val,
old_val,
operation_strt_dt)
values (item.record_id,
item.buscomp_name,
item.operation_cd,
item.user_id,
item.tbl_name,
colmn,
item.row_id,
new_value,
old_value,
to_date (item.operation_dt, 'MM/DD/YYYY HH24:MI:SS'));

commit;
end if;
temp_audit_id := item.row_id;
temp_record_id := item.record_id;
prev_new_val := new_value;
prev_old_val := old_value;
end loop;
end;
/

 
Procedure: 3


-- HOW TO INVOKE THIS PROCEDURE
--declare
--REC_COUNT number;
--begin

-- SIEBELDW.INVOKE_AUDIT_TRAIL ('S_OPTY,PR_POSTN_ID|S_OPTY,X_APP_STATUS','28-APR-2000', '28-APR-2012',REC_COUNT);

-- DBMS_OUTPUT.PUT_LINE(REC_COUNT);

--end;
CREATE OR REPLACE PROCEDURE SIEBELDW.INVOKE_AUDIT_TRAIL (
TABLE_COLUMN IN VARCHAR2, PARM_FRM_DT IN DATE ,PARM_TO_DT IN DATE , REC_COUNT OUT NUMBER
)
AS
TBL_NAME VARCHAR2 (1000);
COLUMN_NAME VARCHAR2 (1000);
TEMP_COUNTER NUMBER;

CURSOR C1
IS
SELECT EXPR
FROM (WITH T AS (SELECT '' || TABLE_COLUMN || '' AS TXT FROM DUAL)
SELECT REGEXP_SUBSTR (TXT,
'[^|]+',
1,
LEVEL)
EXPR
FROM T
CONNECT BY LEVEL <=
LENGTH (REGEXP_REPLACE (TXT, '[^|]*')) + 1) X;
BEGIN
TEMP_COUNTER:=0;
EXECUTE IMMEDIATE 'TRUNCATE TABLE SIEBELDW.WC_AUDIT_DHS';
FOR ITEM IN C1
LOOP
SELECT SUBSTR (ITEM.EXPR, 0, INSTR (ITEM.EXPR, ',') - 1)
INTO TBL_NAME
FROM DUAL;

SELECT SUBSTR (ITEM.EXPR,
INSTR (ITEM.EXPR, ',') + 1,
LENGTH (ITEM.EXPR))
INTO COLUMN_NAME
FROM DUAL;

--DBMS_OUTPUT.PUT_LINE(TBL_NAME || ' ' ||COLUMN_NAME);

SIEBELDW.INSERT_AUDIT_TRAIL (TBL_NAME, COLUMN_NAME, PARM_FRM_DT , PARM_TO_DT, REC_COUNT);
TEMP_COUNTER:=TEMP_COUNTER+REC_COUNT;
REC_COUNT:=TEMP_COUNTER;
END LOOP;
END;
/

 

Calling the Procedure by passing the arguments to deocde the audit trail.
You need to pass the table name and the column name, by a pipe | delimitation for any number of table|column combination
Also, pass the dates in which range you want to decode the audit records.


declare
REC_COUNT number;
begin

INVOKE_AUDIT_TRAIL ('S_OPTY,PR_POSTN_ID|S_OPTY,STATUS_CD','28-APR-2000', '28-APR-2012',REC_COUNT);

DBMS_OUTPUT.PUT_LINE(REC_COUNT);

end;

 

Random Related Posts

Loading…

Browse More Post from Same Category

  • Thani2512

    This is very useful, appreciate your contribution.

  • Memonja

    Did anyone try putting this in place? Does it work?

    • study.siebel

      yes it does works.. pls try it in a development environment

  • Nayan Mondal

    Great contribution. The solution worked perfectly. Thanks a lot.

  • Janus Tinkerdoom

    Lovely article – one of the best things I’ve recently read, and by far the most useful. I had a good experience filling forms online and happy to share it with you. I’ve forgotten the last time I filled out a form on paper. I mostly use PDFfiller to edit. You can easily fill AU Form 1008 here http://goo.gl/EgEYo0