Stardust/Knowledge Base/SystemAdministration/SystemMonitoring/MonitoringSQLStatements

Monitoring the Audit Trail DB using SQL statements

List of interesting SQL statements

1. Analyze process instances in "interrupted" state

Function	SQL Statement
Get the activity instances for all process instances which are in state 'interrupted'.	select distinct ai.oid, ai.state, To_Char( To_Date( '01.01.1970 01:00:00','DD.MM.YYYY HH24:Mi:Ss') + ai.starttime / 86400000,'DD.MM.YYYY HH24:Mi:ss') Starttime, a.id, ai.processinstance from activity_instance ai, activity a where a.model=ai.model and a.oid = ai.activity and state=4 and processinstance in (select oid from process_instance where state=3);
Get the number of activity instances in state 'interrupted'.	select distinct count(ai.oid) from activity_instance ai, activity a where a.model=ai.model and a.oid = ai.activity and state=4 and processinstance in (select oid from process_instance where state=3);
Get number of activities in state 'interrupted' grouped by process definition.	select pd.id, a.id, count(ai.oid) from activity_instance ai, activity a, process_instance pi, process_definition pd where a.model=ai.model and a.oid = ai.activity and ai.state=4 and ai.model = pi.model and ai.processinstance = pi.oid and pd.model=pi.model and pd.oid=pi.processdefinition and pi.state=3 group by pd.id, a.id order by pd.id, a.id;

2. Get data values

Function	SQL Statement
Get data values of a process instance.	select d.id, dv.number_value, dv.string_value from data_value dv, data d where d.oid=dv.data and d.model=dv.model and dv.processinstance=TO_CHANGE order by d.id; (replace TO_CHANGE with OID of process instance whose data values you want to retrieve).

3. Other

Function	SQL Statement
Get start time in CET(MEZ) of a process instance.	select To_Char( To_Date( '01.01.1970 01:00:00','DD.MM.YYYY HH24:Mi:Ss') + starttime / 86400000,'DD.MM.YYYY HH24:Mi:ss') Starttime from process_instance where oid=TO_CHANGE; (replace TO_CHANGE with OID of process instance whose start time you want to retrieve).
Get all activities of a process instance.	select a.name, ai.state, ai.starttime, ai.lastmodificationtime, ai.processinstance from activity_instance ai, activity a where a.oid=ai.activity and a.model=ai.model and ai.processinstance in (select oid from process_instance where rootprocessinstance = TO_CHANGE) order by ai.starttime; (replace TO_CHANGE with OID of process instance whose activities you want to retrieve).
Get a list of the processinstances belonging to the same rootprocessinstance	select pd.id Processdefinition, pi.oid ProcessinstanceOID, pi.state State, pi.starttime Starttime, pi.terminationtime Endtime from process_instance pi, process_definition pd where pd.model=pi.model and pd.oid=pi.processdefinition and pi.oid in (select oid from process_instance where rootprocessinstance= TO_CHANGE); (replace TO_CHANGE with OID of root process instance).