{"id":2429,"date":"2017-12-14T10:30:57","date_gmt":"2017-12-14T10:30:57","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2429"},"modified":"2017-12-14T15:53:12","modified_gmt":"2017-12-14T15:53:12","slug":"decoding-siebel-audit-trail-with-plsql","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/decoding-siebel-audit-trail-with-plsql","title":{"rendered":"Decoding Siebel Audit Trail with PLSQL"},"content":{"rendered":"

In this post we will understand how to decode Seibel Audit Trail using PLSQL.<\/p>\n

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.<\/p>\n

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 &amp; 8.1. It should also be applicable for newer 8.1.1.16 versions as well.
\nSo let\u2019s get started with this. We will go step by step and understand the purpose of this code while going along with a scenario.<\/p>\n

WHAT<\/i><\/u> is Audit Trail?<\/u><\/i><\/span><\/h3>\n

<\/p>\n

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<\/span><\/p>\n

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.<\/span><\/p>\n

Now getting little <\/span>techie<\/i><\/span>, 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. <\/span><\/p>\n

WHY<\/b><\/u><\/i><\/span> did we need to decode?<\/u><\/i><\/span><\/h3>\n

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 <\/span>MVG<\/i><\/span> field is updated, Audit trail logs a <\/span>ROW_ID<\/i><\/span> of new & old record. It does not store the data which was changed. So that was bump # 1.<\/span><\/p>\n

Well you say if we have a row id why not make a join and fetch the data? Well, you can\u2019t. 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 \u2026 Bump #2.<\/span><\/p>\n

So what else is not logged correctly? A child BC record as well. We face same issue as above.<\/span><\/p>\n

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.<\/span><\/p>\n

HOW <\/b><\/u><\/i><\/span>can we decode audit trail?<\/u><\/i><\/span><\/h3>\n

Table used to store Audit Information <\/span>S_AUDIT_ITEM<\/i><\/span> had details like <\/span>BC<\/i><\/span>, <\/span>Operation Date<\/i><\/span>, <\/span>Type of operation<\/i><\/span> and updated record\u2019s <\/span>ROW_ID<\/i><\/span> 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\u2026<\/span><\/p>\n

2*C19*STATUS_CD2*N111*In Progress2*O120*Ready For Automation<\/span><\/p><\/blockquote>\n

From above, I understood that <\/span><\/p>\n

C1 \u2013mean column 1<\/span><\/p>\n

N1 \u2013mean new value 1<\/span><\/p>\n

O1 \u2013mean old value 1<\/span><\/p>\n

So a column \u201cSTATUS_CD\u201d is getting updated from \u201cReady For Automation\u201d to \u201cIn Progress\u201d.<\/span><\/p>\n

But actual <\/span>Field Name <\/i><\/span>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 <\/span>\uf04a<\/span><\/span><\/p>\n

Another thing which struck me was \u20181\u2019 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.<\/span><\/p>\n

2*C216*ACTL_RESOLVED_TS10*X_CLEAR_CD2*N219*2015-07-24 09:00:103*1.52*O20*0*<\/span><\/p><\/blockquote>\n

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.<\/span><\/p>\n

Now one more thing which you might have noticed is the way data is stored. Let\u2019s look at in details<\/span><\/p>\n

2*C19*STATUS_CD2*N111*In Progress2*<\/span><\/p><\/blockquote>\n

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.<\/span><\/p>\n

Well that is simple when you have single field getting updated. If you have two fields getting updated, data looks something like below\u2026<\/span><\/p>\n

2*C216*ACTL_RESOLVED_TS10*X_CLEAR_CD2*N219*2015-07-24 09:00:103*1.5<\/span><\/p><\/blockquote>\n

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.<\/span><\/p>\n

So to decode this, I came up with simple logic with loops<\/span><\/p>\n

    \n
  • Find first number by going from using substring. Go from 1<\/span>st<\/span><\/sup> character till first separator \u2018*\u2019 (say pos1). Store this value as length to be read, say len1.<\/span><\/li>\n
  • Read the substring from first separator \u2018*\u2019 till len1. Store this in str1<\/span><\/li>\n
  • 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\u2026 You will see why this is important later. Store it in len3.<\/span><\/li>\n
  • Now again read substring from pos1 + len1 till next position of \u2018*\u2019(pos2). This gives length of next string to be read, say len2<\/span><\/li>\n
  • Store the substring using len2 from pos2 till len2. <\/span><\/li>\n
  • So on and forth till you reach end.<\/span><\/li>\n
  • 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\u2019t work. Hence we have to use an array to store each value. And guess what, the length of this array would be len3.<\/span><\/li>\n
  • Also, we need to read the value C, N & O to decide which array to update.<\/span><\/li>\n<\/ul>\n

    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.<\/span><\/p>\n

    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 <\/span>\uf04a<\/span><\/span><\/p>\n

    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.<\/span><\/p>\n

    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.<\/span><\/p>\n

     <\/p>\n

    WHERE <\/b><\/u><\/i><\/span>this could be used<\/u><\/i><\/span><\/h3>\n
      \n
    • Once we got this info as procedure output, it became input to a CX table. This table could use joins <\/span>\uf04a<\/span><\/span><\/li>\n
    • We also were able to backup audit information and purge old data quite quickly.<\/span><\/li>\n
    • Audit trail could now become a source for tracking and reporting. Even analytics can be configured.<\/span><\/li>\n<\/ul>\n

      How this can be configured<\/span><\/p>\n

      Best way I see is to configure this is a job running daily for a day\u2019s 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.<\/span><\/p>\n

      Important note:<\/u><\/span> Oracle could change their encoding as need for future upgrades. If you implement this, this would be one of your upgrade considerations.<\/span><\/p>\n

       <\/p>\n

      Solution: <\/b><\/u><\/i><\/span>IT IS what you think it is! The Code.<\/u><\/i><\/span><\/h3>\n

      Currently this code is only doing the DBMS output<\/span>\uf04a<\/span><\/span>. <\/span><\/p>\n

      \n
      declare\r\n    AuditLog varchar2(4000);\r\n    AuditLogLen int;\r\n    numOfOcc int;\r\n    prevpos int;\r\n    codedata varchar2(4000);\r\n    myvar2 int;\r\n    newpos int;\r\n    lennum int;\r\n    loopvar int;\r\n    lent int;\r\n    inrloop int;\r\n    type colN is varray(10) of varchar2(200);\r\n    type oVal is varray(10) of varchar2(200);\r\n    type nVal is varray(10) of varchar2(200);\r\n    ColName colN := colN();\r\n    newValue oVal := oVal();\r\n    oldValue nVal := nVal();\r\n    getLength int;\r\n    columnName varchar2(200);\r\n    displayName colN := colN();\r\n    BusCompName varchar2(200);\r\n    ChildBC varchar2(200);\r\n    OpDate varchar2(200);\r\n    employeeId  varchar2(200);\r\n    Oprtion   varchar2(200);\r\n    recordId varchar2(200);  \r\n    displayField varchar2(200); \r\n    fieldName varchar2(200);\r\n    currentState varchar2(2);\r\n    prevState varchar2(2);\r\n    printLoop int;\r\nbegin\r\n    dbms_output.put_line('Business Component-Child Business Comp-Operation Date-Employee Id-Operation-Field Name-New Value-Old Value-Record Id');\r\n    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')\r\n    loop\r\n        BusCompName:= x.BUSCOMP_NAME;\r\n        ChildBC := x.CHILD_BC_NAME;\r\n        OpDate := x.OPERATION_DT;\r\n        employeeId := x.USER_ID;\r\n        Oprtion := x.OPERATION_CD;\r\n        recordId := x.RECORD_ID;\r\n        AuditLog := x.AuditTrailStr; \r\n        AuditLogLen:=length(AuditLog);\r\n        numOfOcc := AuditLogLen - length(replace(AuditLog,'*',null))  ;\r\n        loopvar:=1; \r\n        prevpos:= 0; \r\n        lent:=0;\r\n        inrloop := 1;\r\n        getLength := 0;\r\n        printLoop := 0;\r\n        loop\r\n        exit when loopvar > numOfOcc;\r\n            myvar2 := INSTR( AuditLog,'*',1,loopvar);\r\n            newpos:= prevpos+lent+1;\r\n            lennum:=myvar2-lent-prevpos-1;\r\n            lent := to_number(substr(AuditLog,newpos,lennum));\r\n            codedata := substr(AuditLog,myvar2+1,lent);\r\n            currentState := substr(codedata,1,1);\r\n\r\n            if (loopvar = 1) then  \r\n                inrloop := substr(codedata,2); \r\n                prevState := currentState;    \r\n                ColName.EXTEND(inrloop);\r\n                displayName.EXTEND(inrloop);\r\n                oldValue.EXTEND(inrloop);\r\n                newValue.EXTEND(inrloop);\r\n            elsif((prevState = 'C' or  prevState= 'O' or prevState = 'N' or  prevState= 'J' or prevState = 'K' or prevState = 'L') ) then \r\n               \r\n                if inrLoop > getLength then                \r\n                    if (prevState = 'C' or prevState = 'J')  then\r\n                        columnName := codedata;                      \r\n                        begin\r\n                            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;\r\n                        end;\r\n                        ColName(getLength+1) := fieldName;\r\n                    elsif (prevState = 'O' or prevState = 'K')  then\r\n                        oldValue(getLength+1) := codedata;\r\n                    elsif (prevState = 'N' or prevState = 'L')   then\r\n                        newValue(getLength+1) := codedata;\r\n                    end if;\r\n                    getLength := getLength +1;\r\n                else \r\n                    getLength := 0;\r\n                    prevState := currentState;\r\n                end if;\r\n            elsif ( (currentState = 'O' or prevState = 'K')  AND (currentState = 'N' or prevState = 'L')  ) then \r\n                getLength := 0;\r\n                prevState := currentState;\r\n            end if; \r\n            prevpos := myvar2;\r\n            loopvar := loopvar+1;\r\n        end loop;\r\n        loop     \r\n        exit when printLoop = inrLoop;\r\n            dbms_output.put_line(BusCompName || '-' || ChildBC || '-' || OpDate || '-' || employeeId || '-' || Oprtion || '-' || ColName(printLoop+1) || '-' || newValue(printLoop+1) || '-' || oldValue(printLoop+1) || '-' ||  recordId);\r\n            printLoop := printLoop + 1;\r\n        end loop;\r\n    end loop ;\r\nend;\r\n<\/pre>\n<\/blockquote>\n
      Have fun coding!!<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":4201,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[6],"tags":[429,441,430,431,439,428,433,440],"class_list":{"0":"post-2429","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-technical","7":"tag-audit-trail","8":"tag-decode","9":"tag-decoding","10":"tag-plsql","11":"tag-reporting","12":"tag-siebel","13":"tag-siebel-crm","14":"tag-tracking"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2429","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/4201"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2429"}],"version-history":[{"count":4,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2429\/revisions"}],"predecessor-version":[{"id":2433,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2429\/revisions\/2433"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2429"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2429"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}