Oracle SOA Audit Trail Parser.
Oracle SOA Suite's Enterprise Manager provides detailed view of the transactions happening within SOA container. In Enterprise landscape, Oracle SOA plays just the glue part, orchestrating Process flow across various Systems. A Complete Business Process Visibility, requires Single window view of Key Milestones, spanning all participating systems.
Lack of such Process transparency leads to waste of Time and efforts spent on maintenance and troubleshooting across all Systems and respective stakeholders.
In this article we will discuss on mining the information from Oracle SOA Suite, so as to access it from a Common Enterprise Third Party Portal, rather restricting the information to Enterprise Manager.
Methodologies to gather Transaction Details.
The diagram below illustrates the Order – Subscription – Cash Scenario applicable for any Utility Service Scenario. The information gathered in SOA could be gathered with any of the below approaches.
- Push Methodology: Oracle SOA pushes the Major Events to an Event Sink which then gets accessed by Third party Repositories for various Reporting and Analysis.
-
Pull Methodology: The Transactions details across Composites, reside within Oracle SOA Infra Database. The data is retrieved On-demand basis, for specific transaction, in case of troubleshooting / Metrics calculation scenarios
The Push methodology is explained in the previous Blog Post. Here we will focus on Pull mechanism, extracting audit trail information from Oracle SOA Environment. Let us explore the various approaches we can employ for fetching Audit Trail information.
Approach 1: Retrieve Audit Trail using SOA Facade API.
We can retrieve the Audit trail pertaining to a specific transaction, using the SOA Facade API.
The sample code is provided below. The Key is to use the Like method, providing the Transaction number on Composite Instance Title, to be searched upon.
public class SOAAuditTrailExporter
{
public static void doExport(SOAAuditTrailExporterArgs args)
throws Exception
{
Hashtable jndiProps = new Hashtable();
jndiProps.put("java.naming.provider.url", "t3://" + args.getHost() + ":" + args.getPort() + "/soa-infra");
jndiProps.put("java.naming.factory.initial", "weblogic.jndi.WLInitialContextFactory");
jndiProps.put("java.naming.security.principal", args.getUsername());
jndiProps.put("java.naming.security.credentials", args.getPassword());
jndiProps.put("dedicated.connection", "true");
Locator locator = LocatorFactory.createLocator(jndiProps);
CompositeDN compositeDN = new CompositeDN(args.getCompositePartition(), args.getCompositeName(), args.getCompositeRevision());
Composite composite = locator.lookupComposite(compositeDN);
if (composite == null) {
throw new RuntimeException("Composite [" + compositeDN.getStringDN() + "] not found.");
}
CompositeInstanceFilter instanceFilter = new CompositeInstanceFilter();
if (args.getLike() != null) {
instanceFilter.setLike(args.getLike());
}
if (args.getInstanceId() != null) {
instanceFilter.setId(args.getInstanceId().toString());
}
List compositeInstances = composite.getInstances(instanceFilter);
if ((compositeInstances == null) || (compositeInstances.size() == 0)) {
throw new RuntimeException("No composite instances found.");
}
for (CompositeInstance compositeInstance : compositeInstances)
{
System.out.println("Processing instance: " + compositeInstance.getId());
ComponentInstanceFilter componentInstanceFilter = new ComponentInstanceFilter();
List childComponentInstances = compositeInstance.getChildComponentInstances(componentInstanceFilter);
for (ComponentInstance componentInstance : childComponentInstances) {
exportAuditTrailToFile(componentInstance, args.getOutBaseFolder());
}
}
}
private static void exportAuditTrailToFile(ComponentInstance componentInstance, File outBaseFolder)
throws IOException
{
if (componentInstance.hasAuditTrail())
{
String compositeDN = componentInstance.getCompositeDN().getStringDN();
String compositeInstanceId = componentInstance.getCompositeInstanceId();
String componentName = componentInstance.getComponentName();
File outFolder = new File(outBaseFolder, compositeDN);
if (!outFolder.exists()) {
outFolder.mkdirs();
}
String outFileName = compositeInstanceId + "-" + componentName + ".xml";
File outFile = new File(outFolder, outFileName);
System.out.println(componentInstance.getAuditTrail());
}
}
public static void main( String args[]) throws Exception
{
SOAAuditTrailExporterArgs arg = new SOAAuditTrailExporterArgs();
arg.setHost("hostname");
arg.setPort(9101);
arg.setUsername("xxxxx");
arg.setPassword("xxxxx");
arg.setLike("645699052089");
arg.setCompositeName( "xxxxxx");
arg.setCompositePartition( "default");
arg.setCompositeRevision("1.0");
SOAAuditTrailExporter.doExport(arg);
}
}
The API scans for the keywords, provided as arguments, in the instance title and retrieves the composite instances matching the criteria. The filter works for few latest patch sets and older versions may not. You can verify the functionality by directly searching in EM Console, with the LIKE pattern (%PATTERN%) on the Search Composite Instances Dashboard.
Approach 2: Retrieve Audit Trail from SOA_INFRA Database.
Another way to retrieve the Audit trail, will be directly querying on SOA_INFRA DB schema. The Cube instance table can be queried to obtain the list of instances for the "Transaction ID". As below.
SELECT CUBE_INSTANCE.CIKEY as CIKEY, CUBE_INSTANCE.STATUS as COMPONENT_STATUS, CUBE_INSTANCE.COMPONENT_NAME as COMPONENT_NAME, COMPOSITE_INSTANCE.ECID as ECID, COMPOSITE_INSTANCE.ID as ID, COMPOSITE_INSTANCE.TITLE as TITLE, COMPOSITE_INSTANCE.PARENT_ID as PARENT_ID, COMPOSITE_INSTANCE.CONVERSATION_ID as CONVERSATION_ID, COMPOSITE_INSTANCE.COMPOSITE_DN as COMPOSITE_DN, COMPOSITE_INSTANCE.SOURCE_NAME as SOURCE_NAME, COMPOSITE_INSTANCE.SOURCE_ACTION_NAME as SOURCE_ACTION_NAME, (CASE WHEN COMPOSITE_INSTANCE.STATE=0 THEN 'OPEN AND RUNNING'
WHEN COMPOSITE_INSTANCE.STATE=1 THEN 'COMPLETED'
WHEN COMPOSITE_INSTANCE.STATE=2 THEN 'RUNNING WITH FAULTS'
WHEN COMPOSITE_INSTANCE.STATE=3 THEN 'COMPLETED WITH FAULTS'
WHEN COMPOSITE_INSTANCE.STATE=4 THEN 'RUNNING WITH RECOVERY REQUIRED'
WHEN COMPOSITE_INSTANCE.STATE=5 THEN 'COMPLETED WITH RECOVERY REQUIRED'
WHEN COMPOSITE_INSTANCE.STATE=6 THEN 'RUNNING WITH FAULTS - RECOVERY REQUIRED'
WHEN COMPOSITE_INSTANCE.STATE=7 THEN 'COMPLETED WITH FAULTS - RECOVERY REQUIRED'
WHEN COMPOSITE_INSTANCE.STATE=8 THEN 'RUNNING - SUSPENDED'
WHEN COMPOSITE_INSTANCE.STATE=9 THEN 'COMPLETED - SUSPENDED'
WHEN COMPOSITE_INSTANCE.STATE=10 THEN 'RUNNING WITH FAULTS - SUSPENDED'
WHEN COMPOSITE_INSTANCE.STATE=11 THEN 'COMPLETED WITH FAULTS - SUSPENDED'
WHEN COMPOSITE_INSTANCE.STATE=12 THEN 'RUNNING WITH RECOVERY REQUIRED - SUSPENDED'
WHEN COMPOSITE_INSTANCE.STATE=13 THEN 'COMPLETED WITH RECOVERY REQUIRED - SUSPENDED'
WHEN COMPOSITE_INSTANCE.STATE=14 THEN 'RUNNING WITH FAULTS - RECOVERY REQUIRED - SUSPENDED'
WHEN COMPOSITE_INSTANCE.STATE=15 THEN 'COMPLETED WITH FAULTS - RECOVERY REQUIRED - SUSPENDED'
WHEN COMPOSITE_INSTANCE.STATE=16 THEN 'RUNNING - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=17 THEN 'COMPLETED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=18 THEN 'RUNNING WITH FAULTS - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=19 THEN 'COMPLETED WITH FAULTS - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=20 THEN 'RUNNING WITH RECOVERY REQUIRED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=21 THEN 'COMPLETED WITH RECOVERY REQUIRED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=22 THEN 'RUNNING WITH FAULTS - RECOVERY REQUIRED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=23 THEN 'COMPLETED WITH FAULTS - RECOVERY REQUIRED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=24 THEN 'RUNNING WITH SUSPENDED AND TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=25 THEN 'COMPLETED WITH SUSPENDED AND TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=26 THEN 'RUNNING WITH FAULTS - SUSPENDED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=27 THEN 'COMPLETED WITH FAULTS - SUSPENDED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=28 THEN 'RUNNING WITH RECOVERY REQUIRED - SUSPENDED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=29 THEN 'COMPLETED WITH RECOVERY REQUIRED - SUSPENDED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=28 THEN 'RUNNING WITH FAULTS - RECOVERY REQUIRED - SUSPENDED - TERMINATED'
WHEN COMPOSITE_INSTANCE.STATE=29 THEN 'COMPLETED WITH FAULTS - RECOVERY REQUIRED - SUSPENDED - TERMINATED'
ELSE COMPOSITE_INSTANCE.STATE || '- UNKNOWN'
END) AS STATE, COMPOSITE_INSTANCE.CREATED_TIME as CREATED_TIME
FROM #SCHEMA_NAME#_SOAINFRA.COMPOSITE_INSTANCE, #SCHEMA_NAME#_SOAINFRA.CUBE_INSTANCE where COMPOSITE_INSTANCE.ECID IN (select DISTINCT(COMPOSITE_INSTANCE.ECID) from #SCHEMA_NAME#_SOAINFRA.COMPOSITE_INSTANCE where COMPOSITE_INSTANCE.title like '%#SEARCH_PATTERN#%' ) and #SCHEMA_NAME#_SOAINFRA.COMPOSITE_INSTANCE.ID=#SCHEMA_NAME#_SOAINFRA.CUBE_INSTANCE.CMPST_ID order by CIKEY asc
The Audit trail pertaining to the CIKEY - Composite Instance Key can be retrieved as below.
DECLARE
V_AUDIT_BLOB BLOB;
V_AUDIT_CLOB CLOB;
V_CUR_AUDIT SYS_REFCURSOR;
TYPE TP_AUDIT_RECORD IS RECORD(
BLOCK NUMBER(38,0),
LOG BLOB
);
TYPE TP_AUDIT_ARRAY IS TABLE OF TP_AUDIT_RECORD;
V_AUDIT_ARRAY TP_AUDIT_ARRAY;
V_AUDIT_COMPLETE BLOB;
V_BUFFER_LENGTH PLS_INTEGER := 32767;
V_BUFFER VARCHAR2(32767);
V_READ_START PLS_INTEGER := 1;
BEGIN
DBMS_LOB.CREATETEMPORARY(V_AUDIT_BLOB, TRUE);
DBMS_LOB.CREATETEMPORARY(V_AUDIT_CLOB, TRUE);
DBMS_LOB.CREATETEMPORARY(V_AUDIT_COMPLETE, TRUE);
OPEN V_CUR_AUDIT FOR 'SELECT BLOCK, LOG FROM #SCHEMA_NAME#_SOAINFRA.AUDIT_TRAIL WHERE CIKEY IN ( #CIKEY# ) ORDER BY COUNT_ID';
FETCH V_CUR_AUDIT BULK COLLECT INTO V_AUDIT_ARRAY;
CLOSE V_CUR_AUDIT;
FOR j IN 1..V_AUDIT_ARRAY.COUNT LOOP
IF j = 1 THEN
DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
ELSE
IF ( V_AUDIT_ARRAY(j).BLOCK = V_AUDIT_ARRAY(j-1).BLOCK ) THEN
DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
ELSE
DBMS_LOB.APPEND (V_AUDIT_COMPLETE, UTL_COMPRESS.LZ_UNCOMPRESS(V_AUDIT_BLOB));
DBMS_LOB.CREATETEMPORARY(V_AUDIT_BLOB, TRUE);
DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
END IF;
END IF;
END LOOP;
DBMS_LOB.APPEND (V_AUDIT_COMPLETE, UTL_COMPRESS.LZ_UNCOMPRESS(V_AUDIT_BLOB));
V_AUDIT_ARRAY.DELETE;
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(V_AUDIT_COMPLETE) / V_BUFFER_LENGTH) LOOP
V_BUFFER := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(V_AUDIT_COMPLETE, V_BUFFER_LENGTH, V_READ_START));
DBMS_LOB.WRITEAPPEND(V_AUDIT_CLOB, LENGTH(V_BUFFER), V_BUFFER);
V_READ_START := V_READ_START + V_BUFFER_LENGTH;
END LOOP;
? := V_AUDIT_CLOB;
END;
The Audit Trail CLOB could be too large and the Payload could be stored as separate CLOB with a Unique ID linked to CIKEY. The mechanism of retrieving the Audit trail is very well elaborated in this Post.
We can scan the Audit trail for complete CLOB List as detailed in the fragment.
public String getIDList(String auditXML) {
String findStr = "</message><details id=\";
StringBuffer idList = new StringBuffer();
if (auditXML.contains(findStr)) {
int lastIndex = 0;
int count = 0;
while (lastIndex != -1) {
lastIndex = auditXML.indexOf(findStr, lastIndex);
if (lastIndex != -1) {
System.out.println('Value' +
auditXML.substring(lastIndex + findStr.length(),
auditXML.indexOf('\',
lastIndex +
findStr.length() +
1)));
if (count > 0)
idList.append(",");
idList.append(auditXML.substring(lastIndex + findStr.length(),
auditXML.indexOf("\"",
lastIndex + findStr.length() +
1)));
lastIndex += findStr.length();
count++;
}
}
}
return idList.toString();
}
The Query below will provide the Audit payloads missing in the Parent Audit Trail.
SELECT UTL_COMPRESS.LZ_UNCOMPRESS(BIN) AS AUDIT_DETAILS_XML
FROM #SCHEMA_NAME#_SOAINFRA.AUDIT_DETAILS
WHERE CIKEY = '#CIKEY#'
AND DETAIL_ID IN ( #ID_LIST# ) ORDER BY DETAIL_ID ASC
Making use of the Audit Trail Information.
The Audit Trail could be parsed for the important Process Milestones, as below. We just need to search for the patterns in Audit trail to analyze the Milestone outcome.
Sample Milestone Validation Configuration:
ValidateAuditTrailBean validationConfig = new ValidateAuditTrailBean();
validationConfig.setBPELName("ProvisionIntegrationBPELProcess"); //Check for this Business Process
BPELTestCaseBean tc = new BPELTestCaseBean();
tc.setBPELVariableToRetrieve("Invoke_ProvisionTransactionService_Transaction"); // BPEL Invocation Variable Name
tc.setValidateSuccessPattern("Invoke_ ProvisionTransactionService _Transaction_OutputVariable>");
tc.setValidateSuccessValue("diagnostic=\"SUCCESS\" status=\"SUCCESS\""); // Search for Success pattern. Else the transaction has failed.
tc.setTestCaseName("PROVISION_TRANSACTION");
validationConfig.addBPELTestCase(tc);
We now have collated enough transactional data. Let us form an XML Output as below code, which can be further reused for reports.
public String getXMLOutput() {
StringBuffer XMLOutput = new StringBuffer();
XMLOutput.append( "<SearchResult>" );
XMLOutput.append( "<InstanceSearchResult>" );
for(InstanceSearchResultBean bean : searchInstances ) {
String compositeName = bean.getCompositeDN().substring( bean.getCompositeDN().indexOf('/') + 1, bean.getCompositeDN().indexOf('!'));
String compositeVersion = bean.getCompositeDN().substring( bean.getCompositeDN().indexOf('!') + 1, bean.getCompositeDN().indexOf('*'));
XMLOutput.append( "<Instance>" );
XMLOutput.append( "<CompositeName>" + bean.getCompositeDN().substring( bean.getCompositeDN().indexOf('/') + 1, bean.getCompositeDN().indexOf('!')) + "</CompositeName>" );
XMLOutput.append( "<CompositeRevision>" + bean.getCompositeDN().substring( bean.getCompositeDN().indexOf('!') + 1, bean.getCompositeDN().indexOf('*')) + "</CompositeRevision>" );
XMLOutput.append( "<ComponentName>" + bean.getComponentName() + "</ComponentName>" );
XMLOutput.append( "<CompositeID>" + bean.getCompositeID() + "</CompositeID>" );
XMLOutput.append( "<Cikey>" + bean.getCikey() + "</Cikey>" );
XMLOutput.append( "<Title>" + bean.getTitle() + "</Title>" );
XMLOutput.append( "<PartnerSource>" + bean.getSourceName() + "</PartnerSource>" );
XMLOutput.append( "<PartnerAction>" + bean.getSourceActionName() + "</PartnerAction>" );
XMLOutput.append( "<State>" + bean.getState() + "</State>" );
XMLOutput.append( "<CreatedTime>" + bean.getCreatedTime() + "</CreatedTime>" );
XMLOutput.append( "<EMCompositeURL><![CDATA[http://" + PropertyHelper.getConsoleHost() + ":" + PropertyHelper.getConsolePort() + "/em/faces/ai/soa/messageFlow?target=/Farm_sbpbps_domain/sbpbps_domain/Managed_soa_server01/default/" + compositeName + "%20[" + compositeVersion + "]&type=oracle_soa_composite&soaContext=" + bean.getCompositeDN() + "/" + bean.getCompositeID() + "]]></EMCompositeURL>");
// Format : http://<hostname>:<port>/em/faces/ai/soa/messageFlow?target=/Farm_<domain_name>/<domain_name>/<server>/<partition>/composite_name>%20[<version>]&type=oracle_soa_composite&soaContext=<partition>/<composite_name>!<version>/<instance_id>
XMLOutput.append( "<EMInstanceFlowURL><![CDATA[http://" + PropertyHelper.getConsoleHost() + ":" + PropertyHelper.getConsolePort() + "/em/faces/ai/soa/messageFlow?target=/Farm_bps_domain/bps_domain/Managed_soa_serverName/default/" + compositeName + "%20[" + compositeVersion + "]&type=oracle_soa_composite&soaContext=" + bean.getCompositeDN() + "/" + bean.getCompositeID() + "#%2Fai%2Fsoa%2FbpelInstance%40%3FcurrentTab%3Dflow%26target%3D%252FFarm_bps_domain%252Fbps_domain%252FManaged_soa_serverName%252Fdefault%252F" + compositeName + "%2B%255B" + compositeVersion + "%255D%26soaContext%3Ddefault%252F" + compositeName + "%2521" + compositeVersion + "%252F" + bean.getComponentName() + "%252Fbpel%253A" + bean.getCikey() + "%26type%3Doracle_soa_composite]]></EMInstanceFlowURL>");
XMLOutput.append("<TestResults>");
for (ValidationResultBean result :
bean.getValidationResults() )
{
XMLOutput.append("<TestResult>");
if( null != result.getValidationTestCaseName() )
XMLOutput.append( "<ValidationTestCase>" + result.getValidationTestCaseName() + "</ValidationTestCase>");
if( null != result.getValidationOutput() )
XMLOutput.append( "<ValidationOutput><![CDATA[" + result.getValidationOutput() + "]]></ValidationOutput>");
if( null != result.getValidationResult() )
XMLOutput.append( "<ValidationResult>" + result.getValidationResult() + "</ValidationResult>");
XMLOutput.append("</TestResult>");
}
XMLOutput.append("</TestResults>");
XMLOutput.append( "</Instance>" );
}
XMLOutput.append( "</InstanceSearchResult>" );
System.out.println("XML output : " + XMLOutput.toString() );
The Composite details are obtained from the DB Query results and Audit Trail files, and stored in a Java Bean. The Direct Composite Instance Flow and Direct Composite Flow links, on the EM console can also be drafted as per the code above.