Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
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). |