Monday, June 18, 2012

SOA Composites Dashboard

So I wanted to create a simple SSRS report, and have it emailed on a regular basis that showed some basic information about our running Composites instead of clicking into each Group/Composite in SOA Enterprise Manager (EM).

Here is the query I initially came up with that seems to work pretty well:


SELECT SOAGroup,
         SOAComposite,
         SOAVersion,
         max(id) as composite_id,
         decode(min(state), 0, 'Running',
         1, 'Completed',
         2, 'Running with faults',         
         3, 'Completed with faults',
         4, 'Running with recovery required',
         5, 'Completed with recovery required',
         6, 'Running with faults and recovery required',
         7, 'Completed with faults and recovery required',
         8, 'Running with suspended',
         9, 'Completed with suspended',
         10,'Running with faults and suspended',
         11,'Completed with faults and suspended',
         12,'Running with recovery required and suspended',
         13,'Completed with recovery required and suspended',
         14,'Running with faults, recovery required, and suspended',
         15,'Completed with faults, recovery required, and suspended',
         16,'Running with terminated',
         17,'Completed with terminated',
         18,'Running with faults and terminated',
         19,'Completed with faults and terminated',
         20,'Running with recovery required and terminated',
         21,'Completed with recovery required and terminated',
         22,'Running with faults, recovery required, and terminated',
         23,'Completed with faults, recovery required, and terminated',
         24,'Running with suspended and terminated',
         25,'Completed with suspended and terminated',
         26,'Running with faulted, suspended, and terminated',
         27,'Completed with faulted, suspended, and terminated',
         28,'Running with recovery required, suspended, and terminated',
         29,'Completed with recovery required, suspended, and terminated',
         30,'Running with faulted, recovery required, suspended, and terminated',
         31,'Completed with faulted, recovery required, suspended, and terminated',
         32,'UNKNOWN',
         64,'???????') run_state, 
         MAX (Created_time) AS last_run_time,
         SYSDATE - MAX (Created_time) AS Elapsed,
         'http://soaprod.ocint.com:7001/em/faces/ai/soa/messageFlow?target=/Farm_base_domain/base_domain/soa_server1/'
         || SOAGROUP ||'/' || SOACOMPOSITE || '%20[' || SOAVERSION || ']&type=oracle_soa_composite&soaContext='
         || SOAGROUP || '/' || SOACOMPOSITE || '!' || SOAVERSION || '/' || max(id) AS URL       
    FROM (SELECT SUBSTR (composite_dn, 1, INSTR (composite_dn, '/') - 1) AS SOAGroup,
                 SUBSTR (composite_dn, INSTR (composite_dn, '/') + 1, (INSTR (composite_dn, '!') - INSTR (composite_dn, '/') - 1)) AS SOAComposite,
                 SUBSTR (composite_dn, INSTR (composite_dn, '!') + 1, (INSTR (composite_dn, '*') - INSTR (composite_dn, '!') - 1)) AS SOAVersion,
                 Source_Name,
                 Created_time,
                 id,
                 state
            FROM prd_soainfra.composite_instance)
GROUP BY SOAGroup, SOAComposite, SOAVersion
ORDER BY SOAGroup, SOAComposite, SOAVersion

I then created a Data Source to the SOA (PRD_SOAINFRA user) Server and scheduled a report with email delivery. The Composite ID column was hyperlinked using the URL column (not displayed) in SSRS. This allows you to click and be taken directly to the Composite in EM once logged in.


No comments:

Post a Comment