Need to have all ESB Services and their respective parent System
I have already mentioned in my previous blog with subject - Playing with ESB Schema-1, about what all details are required and the details on how to connect to ESB schema.
SELECT systems.name SYSTEMNAME, event.name ESBSERVICENAME, event.guid GUID,
DECODE(event.type, 'GROUP', 'Service', 'SERVICEGROUP', 'Service Group') TYPE, event.status STATUS
FROM wf_events event, wf_systems systems
WHERE event.type != 'EVENT'
AND event.system_guid = systems.guid
AND event.type IN ('GROUP')
AND systems.name != 'BPELSystem'
ORDER BY systems.name
1) event.type != 'EVENT' :- Condition to exclude esb activities
2) systems.name != 'BPELSystem' :- Condition to exclude BPEL processes
3) 'GROUP' :- Here GROUP means ESB service
4) 'SERVICEGROUP' :- Here SERVICEGROUP is the actual Service Group