In this post we will understand how to decode Seibel Audit Trail using PLSQL.
Siebel Audit Trail is feature in Siebel CRM which allows customer to track changes to important UI fields. Though it is very useful feature, the changes are stored in encoded text in DB. We are aiming to decode this complex data and allow user to extract complete Audit Trail information in files or tables. This can be further used to enhance customer experience.
This extract process is written completely in PL-SQL procedure. Similarly a script based solution would follow. This PL SQL procedure is tested with version 8.0 & 8.1. It should also be applicable for newer 8.1.1.16 versions as well.
So let’s get started with this. We will go step by step and understand the purpose of this code while going along with a scenario.
WHAT is Audit Trail?
More often than not, we see that few important fields are always tracked for changes in Siebel objects. Be it status of order, approver of quote, status of activity or assignee or a service request. Examples are many and change as per business requirement
Siebel has provided us with very good Audit Trail feature which helps us track these fields using audit trail and this data is displayed to us in UI. Usually we have this view hidden and shown only to the admins.
Now getting little techie, we know that Siebel has implemented this functionality via a specialized class which we cannot (obviously) modify. For most scenarios also, these functionality is perfectly good to work with and display of data to admins need not be quite user friendly.
WHY did we need to decode?
I came a scenario where I need to show changes to the customer for who displayed info needed to be directly consumable. Siebel does not fails us when audit trail is happening on a direct BC field i.e. Status of an order. But when a MVG field is updated, Audit trail logs a ROW_ID of new & old record. It does not store the data which was changed. So that was bump # 1.
Well you say if we have a row id why not make a join and fetch the data? Well, you can’t. These are some restriction with specialized class. You cannot make a join to a virtual field that is. We tried few other methods but all failed. Customer is very anxious now … Bump #2.
So what else is not logged correctly? A child BC record as well. We face same issue as above.
After looking at many alternatives, I decided to take a look at the data itself and see if I can play around. I came across, not to my surprise, special encoding of audited data.
HOW can we decode audit trail?
Table used to store Audit Information S_AUDIT_ITEM had details like BC, Operation Date, Type of operation and updated record’s ROW_ID directly stored in database columns, which was the good part. The bad part was audited data for field which was stored in BLOB field which looks something like below…
2*C19*STATUS_CD2*N111*In Progress2*O120*Ready For Automation
From above, I understood that
C1 –mean column 1
N1 –mean new value 1
O1 –mean old value 1
So a column “STATUS_CD” is getting updated from “Ready For Automation” to “In Progress”.
But actual Field Name is nowhere to see in audit trail table or even in this encoded text. So the writing on the wall said, you need to get it the hard way
Another thing which struck me was ‘1’ used after C, N & O letters. So does that mean we might have C2, C3 and if yes how will that data look? Well yes we do have C2 and C3 and it looks as complex if not more.
2*C216*ACTL_RESOLVED_TS10*X_CLEAR_CD2*N219*2015-07-24 09:00:103*1.52*O20*0*
So here we have 2 columns, ACTL_RESOLVED_TS & X_CLEAR_CD. Respectively their old and new values are also mentioned. So above scenario is when two columns of same BC were updated in single commit.
Now one more thing which you might have noticed is the way data is stored. Let’s look at in details
2*C19*STATUS_CD2*N111*In Progress2*
First we have number 2, then * then C1 (length = 2) then 9 then * and then STATUS_CD (length = 9). So system length of next string then a separator * and then string itself. This is then repeated for each string till end. First comes column then comes new value and then in the last the old value in sequence.
Well that is simple when you have single field getting updated. If you have two fields getting updated, data looks something like below…
2*C216*ACTL_RESOLVED_TS10*X_CLEAR_CD2*N219*2015-07-24 09:00:103*1.5
Difference here is column record gets repeated with next column. So string will have two column values then two new values and two old values.
So to decode this, I came up with simple logic with loops
- Find first number by going from using substring. Go from 1st character till first separator ‘*’ (say pos1). Store this value as length to be read, say len1.
- Read the substring from first separator ‘*’ till len1. Store this in str1
- In str1, do a substring to know if we have 1 column or more than one column getting updated. This is by doing substring of str1 from position 2 till len1. Now we know if we have C1 or C2 etc… You will see why this is important later. Store it in len3.
- Now again read substring from pos1 + len1 till next position of ‘*’(pos2). This gives length of next string to be read, say len2
- Store the substring using len2 from pos2 till len2.
- So on and forth till you reach end.
- Now to store columns, new & old values we could have used variables but in case of scenario where more than one column are getting updated, this won’t work. Hence we have to use an array to store each value. And guess what, the length of this array would be len3.
- Also, we need to read the value C, N & O to decide which array to update.
Important Note: C N & O can easily be changed to J K & L as we have seen in few previous versions of Siebel. Procedure handles this easily.
So I started working on this logic and decoding the audit records one by one. PL/SQL is not as versatile in coding such complex code as I would have liked and arrays mainly are major cause of headache. But at the end, I was able to bend Oracle PLSQL to my wish
If you have read this much I believe you might like to give it a try based on logic flow above. I am adding my code, give it a try and let me know your how it goes.
I have tested this code for 8.1 version of Siebel and it should work with most other versions I believe. In case you have little different encoding, some tweak in code would do the job.
WHERE this could be used
- Once we got this info as procedure output, it became input to a CX table. This table could use joins
- We also were able to backup audit information and purge old data quite quickly.
- Audit trail could now become a source for tracking and reporting. Even analytics can be configured.
How this can be configured
Best way I see is to configure this is a job running daily for a day’s data. But as per requirement this could be added on insert record trigger of S_AUDIT_ITEM. These way custom and actual tables are in continuous sync.
Important note: Oracle could change their encoding as need for future upgrades. If you implement this, this would be one of your upgrade considerations.
Solution: IT IS what you think it is! The Code.
Currently this code is only doing the DBMS output.
declare
AuditLog varchar2(4000);
AuditLogLen int;
numOfOcc int;
prevpos int;
codedata varchar2(4000);
myvar2 int;
newpos int;
lennum int;
loopvar int;
lent int;
inrloop int;
type colN is varray(10) of varchar2(200);
type oVal is varray(10) of varchar2(200);
type nVal is varray(10) of varchar2(200);
ColName colN := colN();
newValue oVal := oVal();
oldValue nVal := nVal();
getLength int;
columnName varchar2(200);
displayName colN := colN();
BusCompName varchar2(200);
ChildBC varchar2(200);
OpDate varchar2(200);
employeeId varchar2(200);
Oprtion varchar2(200);
recordId varchar2(200);
displayField varchar2(200);
fieldName varchar2(200);
currentState varchar2(2);
prevState varchar2(2);
printLoop int;
begin
dbms_output.put_line('Business Component-Child Business Comp-Operation Date-Employee Id-Operation-Field Name-New Value-Old Value-Record Id');
for x in ( select BUSCOMP_NAME,CHILD_BC_NAME,OPERATION_DT, USER_ID, RECORD_ID,OPERATION_CD,to_char(substr(audit_log,0,3999)) AuditTrailStr from siebel.s_audit_item where record_id in '1')
loop
BusCompName:= x.BUSCOMP_NAME;
ChildBC := x.CHILD_BC_NAME;
OpDate := x.OPERATION_DT;
employeeId := x.USER_ID;
Oprtion := x.OPERATION_CD;
recordId := x.RECORD_ID;
AuditLog := x.AuditTrailStr;
AuditLogLen:=length(AuditLog);
numOfOcc := AuditLogLen - length(replace(AuditLog,'*',null)) ;
loopvar:=1;
prevpos:= 0;
lent:=0;
inrloop := 1;
getLength := 0;
printLoop := 0;
loop
exit when loopvar > numOfOcc;
myvar2 := INSTR( AuditLog,'*',1,loopvar);
newpos:= prevpos+lent+1;
lennum:=myvar2-lent-prevpos-1;
lent := to_number(substr(AuditLog,newpos,lennum));
codedata := substr(AuditLog,myvar2+1,lent);
currentState := substr(codedata,1,1);
if (loopvar = 1) then
inrloop := substr(codedata,2);
prevState := currentState;
ColName.EXTEND(inrloop);
displayName.EXTEND(inrloop);
oldValue.EXTEND(inrloop);
newValue.EXTEND(inrloop);
elsif((prevState = 'C' or prevState= 'O' or prevState = 'N' or prevState= 'J' or prevState = 'K' or prevState = 'L') ) then
if inrLoop > getLength then
if (prevState = 'C' or prevState = 'J') then
columnName := codedata;
begin
select FIELD_NAME into fieldName from siebel.s_audit_field a, siebel.S_AUDIT_BUSCOMP b where a.audit_bc_id = b.row_id and b.buscomp_name = BusCompName and a.col_name = columnName;
end;
ColName(getLength+1) := fieldName;
elsif (prevState = 'O' or prevState = 'K') then
oldValue(getLength+1) := codedata;
elsif (prevState = 'N' or prevState = 'L') then
newValue(getLength+1) := codedata;
end if;
getLength := getLength +1;
else
getLength := 0;
prevState := currentState;
end if;
elsif ( (currentState = 'O' or prevState = 'K') AND (currentState = 'N' or prevState = 'L') ) then
getLength := 0;
prevState := currentState;
end if;
prevpos := myvar2;
loopvar := loopvar+1;
end loop;
loop
exit when printLoop = inrLoop;
dbms_output.put_line(BusCompName || '-' || ChildBC || '-' || OpDate || '-' || employeeId || '-' || Oprtion || '-' || ColName(printLoop+1) || '-' || newValue(printLoop+1) || '-' || oldValue(printLoop+1) || '-' || recordId);
printLoop := printLoop + 1;
end loop;
end loop ;
end;
Have fun coding!!