A trilha de auditoria ou audit trail do Siebel é muito boa, mas tem algumas características que, a depender da necessidade específica da sua empresa, podem inviabilizar seu uso.
A própria documentação do Siebel afirma que se você precisar dos dados de auditoria do Siebel num DW ou lê-los um BI da vida tem que fazer uso de mutretagem (trigger). Esse poderia ser considerado o motivador ou problema 0.
O motivo é fácil de explicar: Ao contrário das versões mais antigas do Siebel onde cada campo auditado de um Business Component gerava um registro na S_AUDIT_ITEM, as versões relativamente novas do Siebel armazenam um registro para cada bloco de atualizações.
Num mesmo registro da S_AUDIT_ITEM é possível ter N campos auditados desde que eles tenham sido alterados numa mesma operação. Todo o log de auditoria de uma transação, que poderia ter de 1 a N campos auditados, fica junto numa coluna CLOB da vida (AUDIT_LOG).
Se você soubesse exatamente quantos campos são auditados em um Business Component (fácil) e quantos campos são atualizadas por vez (difícil), você até conseguiria entender o padrão “field-oldval-newval-field1-oldval1-newval1…” armazenado no AUDIT_LOG da S_AUDIT_ITEM. Entendendo o padrão, seria possível ler os dados com SQL/PL, dividir, reescrever em outra tabela, etc. Não é o caso.
Só o problema 0 já traria consigo a imperativa necessidade do uso de artifícios para ler os dados de auditoria do Siebel fora do Siebel. Mas, fora disso, existem outras características da solução de auditoria do Siebel que talvez motivem algum tipo de customização do produto. Destaco algumas:
- Alterações feitas fora de Object Managers não são auditadas (EIM, DDL, etc.)
- Se as mesmas configurações de auditoria de um BC auditado não forem feitas para todos os BCs cópias (utilizados para fins de performance, por exemplo) alterações feitas não serão registradas deixando “buracos” de entendimento da evolução de valores num campo durante o ciclo de vida do registro
- Mesmo se todos os BCs cópias tivessem os dados de auditoria iguaizinhos, teríamos que juntar as informações de auditoria de todos os BCs cópia numa visualização única.
- Campos multivalorados ou calculados não são auditáveis por motivos óbvios.
O exemplo utilizado aqui traz algumas ideias para resolver os problemas 0, 1, 2 e 3 deixando o problema 4 (quase filosófico) para outro dia.
Enfeitando o Pavão da Audit Trail com AdoDb
Usei a base do cambalacho oficial recomendado pela Oracle, mas com um toque de “ah, vai. Não tenho mais nada pra fazer mesmo!” na solução porque é sábado, está chovendo, estou preso em casa… Oversharing.
Você vai perceber que o exemplo desse artigo contempla:
- Criação de objetos de banco customizados via Windows COM+AdoDB
- Leitura de dados históricos da S_AUDIT_ITEM
- Escrita de dados históricos em tabela customizada
- Criação de database trigger de auditoria para UM campo específico
Eis a solução com trigger
O código, testado no Siebel IP 15.18 e no IP 21.1, está comentado.
function MigraAudit(Inputs, Outputs)
{
try
{
//Y ou N... ou então não passa nada!
var vCriaObjetos = Inputs.GetProperty("CriaObjetos");
//Usuário DB User com privilégios para criar os objetos do script
var vUser = "siebel";
//Senha DB User
var vPass = "a1s2d3";
//Schema onde serão criados os objetos var vSchema = "SIEBEL";
//Tablespace var vTblsSpace = "TS_SBL";
//Nome do ODBC - Eu usei o Driver do Oracle Client 11.2.0.4 para IP15 e 12.1.0.1.0 para o IP 21.1
//O Driver do Siebel até funciona, mas não é licenciado para esse tipo de conexão e dá um "alerta" que sobe como erro
//Para não ter que tratar erro que não é erro, recomendo criar o ODBC com o driver do Oracle Client mesmo.
var vODBC = "ORACLEDB";
var vCom = "";
var vLog = "";
//Se o SO do seu server não for Windows, vai dar xabu! Tem que ser windows. //Se não tem servidor com windows, basta executar o script localmente com o Web Client Dedicado var objRec = COMCreateObject("ADODB.connection");
//Tabela que eu quero auditar
var vAudTblName = "S_SRV_REQ";
//Coluna que eu quero auditar
var vAudCol = "SR_STAT_ID";
var vAudSearchExpr = "[Table Name]='" + vAudTblName +"' AND [Column] = '" + vAudCol +"'";
objRec.Open(vODBC,vUser,vPass);
if (vCriaObjetos=="Y")
{
//Drop Objetos caso eles já existam
//A primeira vez que o código for executado é preciso criar os objetos
//Depois da primeira execução, se foi um de teste, por exemplo, talvez você queira recriar os objetos
//Daí eu coloquei um drop aqui para facilitar
try
{
vCom = "DROP TABLE " + vSchema + ".SBL_AUD_TABLE_LOG CASCADE CONSTRAINTS;";
objRec.Execute(vCom);
vLog = vLog + "SUCESSO: DROP TABLE" + "\n";
}
catch(e)
{
vLog = vLog + "ERRO: DROP TABLE:" + e + "\n";
}
try
{
vCom = "DROP SEQUENCE " + vSchema + ".SBL_AUD_SEQ;";
objRec.Execute(vCom);
vLog = vLog + "SUCESSO: DROP SEQUENCE" + "\n";
}
catch(e)
{
vLog = vLog + "ERRO: DROP SEQUENCE:" + e + "\n";
}
}
//Cria objetos obrigatórios
try
{
if (vCriaObjetos=="Y")
{
//Sequence
//Criada para gerar ID único para minha tabela customizada
vCom = "CREATE SEQUENCE " + vSchema + ".SBL_AUD_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE ORDER;";
objRec.Execute(vCom);
vLog = vLog + "SUCESSO: CREATE SEQUENCE" + "\n";
//Tabela de Auditoria
//Criada para armazenar os dados auditados
vCom = "CREATE TABLE " + vSchema + ".SBL_AUD_TABLE_LOG ";
vCom = vCom + "( ROW_ID VARCHAR2(15 CHAR) NOT NULL, ";
vCom = vCom + "OPERATION_CD VARCHAR2(30 CHAR) NOT NULL, ";
vCom = vCom + "TBL_NAME VARCHAR2(30 CHAR) NOT NULL, ";
vCom = vCom + "RECORD_ID VARCHAR2(15 CHAR) NOT NULL, ";
vCom = vCom + "USER_ID VARCHAR2(15 CHAR) NOT NULL, ";
vCom = vCom + "OPERATION_DT DATE, ";
vCom = vCom + "COL_NAME VARCHAR2(75 CHAR), ";
vCom = vCom + "OLD_VAL VARCHAR2(2000 CHAR), ";
vCom = vCom + "NEW_VAL VARCHAR2(2000 CHAR), ";
vCom = vCom + "IP_ADDRESS VARCHAR2(50 CHAR), ";
vCom = vCom + "DB_USER VARCHAR2(50 CHAR) ) ";
vCom = vCom + "TABLESPACE " + vTblsSpace + " ";
vCom = vCom + "PCTUSED 0 ";
vCom = vCom + "PCTFREE 10 ";
vCom = vCom + "INITRANS 1 ";
vCom = vCom + "MAXTRANS 255 ";
vCom = vCom + "STORAGE ( ";
vCom = vCom + "MAXSIZE UNLIMITED ";
vCom = vCom + "PCTINCREASE 0 ";
vCom = vCom + "BUFFER_POOL DEFAULT ) ";
vCom = vCom + "LOGGING ";
vCom = vCom + "NOCOMPRESS ";
vCom = vCom + "NOCACHE; ";
objRec.Execute(vCom);
vLog = vLog + "SUCESSO: CREATE TABLE" + "\n";
}
//Preenche a nova tabela de auditoria com os dados da S_AUDIT_ITEM
//Aqui nós lemos a S_AUDIT_ITEM conforme a Oracle indica e já vamos inserindo os registros "legados" na nova tabela
try
{
var boAudit = TheApplication().GetBusObject("Audit Trail");
var bcAuditItem = boAudit.GetBusComp("Audit Trail Item 2");
with(bcAuditItem)
{
SetViewMode(AllView);
ActivateField("Record Id");
ActivateField("Old Value");
ActivateField("New Value");
ActivateField("Date");
ActivateField("Operation");
ActivateField("Employee Id");
ClearToQuery();
//O SetSearchExpr é bastante impostante. Eu busquei pelo nome da tabela e pela coluna
//Poderia ser pelo nome de vários BCs e colunas diferentes
//Poderia ter uma data de corte
//O que retornar na query do BC Audit Trail Item 2 vai ser inserido na nova tabela já "denormalizado"
SetSearchExpr(vAudSearchExpr);
//Achei interessante ordernar pela data para que os inserts abaixo já sejam feitos na +- na ordem
SetSortSpec("Date");
ExecuteQuery(ForwardOnly);
var bRecord = FirstRecord();
var vCount = 0;
while(bRecord)
{
vCount = vCount+1;
var vAudId = GetFieldValue("Record Id");
var vAudOld = GetFieldValue("Old Value");
var vAudNew = GetFieldValue("New Value");
var vAudDt = GetFieldValue("Date");
var vAudOper = GetFieldValue("Operation");
var vAudUser = GetFieldValue("Employee Id");
vCom = "INSERT INTO SIEBEL.SBL_AUD_TABLE_LOG (ROW_ID, OPERATION_CD, TBL_NAME, RECORD_ID, USER_ID, OPERATION_DT, COL_NAME, OLD_VAL, NEW_VAL) ";
vCom = vCom + "VALUES (SIEBEL.SBL_AUD_SEQ.nextval,'"+ vAudOper +"', 'S_SRV_REQ', '"+ vAudId +"', '"+ vAudUser +"', to_date('"+ vAudDt +"', 'mm/dd/yyyy hh24:mi:ss'), 'SR_STAT_ID', '"+ vAudOld +"', '"+ vAudNew+"' );";
objRec.Execute(vCom);
//Commit a cada 10000 transações (depende da capacidade do seu database - UNDO tablespace, disco, carga)
if (vCount==10000)
{
objRec.Execute("COMMIT;");
vCount = 0;
}
bRecord = NextRecord();
}
//Commit final
objRec.Execute("COMMIT;");
vLog = vLog + "SUCESSO: POPULATE TABLE" + "\n";
}
}
catch(e)
{
vLog = vLog + "ERRO: POPULATE TABLE: " + e + "\n";
}
finally
{
boAudit = bcAuditItem = null;
}
}
catch(e)
{
vLog = vLog + "ERRO: CREATE TABLE/SEQUENCE: " + e + "\n";
}
// Cria trigger para auditar as novas alterações
// Veja que eu só nós criamos a trigger imediatamente depois que os dados legados foram migrados
//Isso garante que não ficará nada para trás e que não teremos dados de auditoria duplicados
try
{
vCom = "CREATE OR REPLACE TRIGGER " + vSchema +".T_AUD_"+vAudCol+"\n";
vCom = vCom + " BEFORE INSERT OR UPDATE OR DELETE\n";
vCom = vCom + " ON " + vSchema + "."+ vAudTblName +"\n";
vCom = vCom + " REFERENCING NEW AS new OLD AS old\n";
vCom = vCom + " FOR EACH ROW\n";
vCom = vCom + "DECLARE\n";
vCom = vCom + "PRAGMA AUTONOMOUS_TRANSACTION;\n";
vCom = vCom + " v_oper VARCHAR2 (15);\n";
vCom = vCom + " v_new VARCHAR2 (2000);\n";
vCom = vCom + " v_old VARCHAR2 (2000);\n";
vCom = vCom + " v_rid VARCHAR2 (15);\n";
vCom = vCom + " v_uid VARCHAR2 (15);\n";
vCom = vCom + " v_log char(1); \n";
vCom = vCom + "BEGIN\n";
vCom = vCom + " v_log := 'N'; \n";
vCom = vCom + " IF INSERTING\n";
vCom = vCom + " THEN\n";
vCom = vCom + " v_oper := 'Novo registro';\n";
vCom = vCom + " v_new := :NEW."+vAudCol+";\n";
vCom = vCom + " v_old := null;\n";
vCom = vCom + " v_rid := :NEW.ROW_ID;\n";
vCom = vCom + " v_uid := :NEW.LAST_UPD_BY;\n";
vCom = vCom + " v_log := 'Y'; \n";
vCom = vCom + " ELSE IF UPDATING('"+vAudCol+"')\n";
vCom = vCom + " THEN\n";
vCom = vCom + " v_oper := 'Modificar';\n";
vCom = vCom + " v_new := :NEW."+vAudCol+";\n";
vCom = vCom + " v_old := :OLD."+vAudCol+";\n";
vCom = vCom + " v_rid := :OLD.ROW_ID;\n";
vCom = vCom + " v_uid := :NEW.LAST_UPD_BY;\n";
vCom = vCom + " v_log := 'Y';\n";
vCom = vCom + " ELSE IF DELETING\n";
vCom = vCom + " THEN\n";
vCom = vCom + " v_oper := 'Excluir';\n";
vCom = vCom + " v_new := null;\n";
vCom = vCom + " v_old := :OLD."+vAudCol+";\n";
vCom = vCom + " v_rid := :OLD.ROW_ID;\n";
vCom = vCom + " v_uid := :OLD.LAST_UPD_BY;\n";
vCom = vCom + " v_log := 'Y'; \n";
vCom = vCom + " END IF;\n";
vCom = vCom + " END IF;\n";
vCom = vCom + " END IF; \n";
vCom = vCom + " if v_log = 'Y' then \n";
vCom = vCom + " INSERT INTO " + vSchema + ".SBL_AUD_TABLE_LOG (ROW_ID,\n";
vCom = vCom + " OPERATION_CD,\n";
vCom = vCom + " TBL_NAME,\n";
vCom = vCom + " RECORD_ID,\n";
vCom = vCom + " USER_ID,\n";
vCom = vCom + " OPERATION_DT,\n";
vCom = vCom + " COL_NAME,\n";
vCom = vCom + " OLD_VAL,\n";
vCom = vCom + " NEW_VAL,\n";
vCom = vCom + " IP_ADDRESS,\n";
vCom = vCom + " DB_USER)\n";
vCom = vCom + " VALUES (" + vSchema + ".SBL_AUD_SEQ.nextval,\n";
vCom = vCom + " v_oper,\n";
vCom = vCom + " '"+ vAudTblName +"',\n";
vCom = vCom + " v_rid,\n";
vCom = vCom + " v_uid,\n";
vCom = vCom + " SYSDATE,\n";
vCom = vCom + " '"+vAudCol+"',\n";
vCom = vCom + " v_old,\n";
vCom = vCom + " v_new,\n";
vCom = vCom + " SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),\n";
vCom = vCom + " SYS_CONTEXT ('USERENV', 'SESSION_USER'));\n";
vCom = vCom + " COMMIT;\n";
vCom = vCom + " end if;\n";
vCom = vCom + "END;";
objRec.Execute(vCom);
vLog = vLog + "SUCESSO: CREATE TABLE/SEQUENCE: " + "\n";
}
catch(e)
{
vLog = vLog + "ERRO: CREATE TRIGGER: " + e + "\n";
}
}
catch(e)
{
}
finally
{
objRec = null;
return(vLog);
}
}
function Service_PreInvokeMethod (MethodName, Inputs, Outputs)
{
var vReturn = ContinueOperation;
try
{
if (MethodName=="MigraAudit")
{
vReturn = CancelOperation;
TheApplication().RaiseErrorText(MigraAudit(Inputs, Outputs));
}
}
catch(e)
{
throw(e);
}
return (vReturn);
}
Não deixem de comentar os artigos.
Abraço.
Do you need help with Assessment and Troubleshootings in your Oracle Siebel CRM environment or in the Database? Contact us.