Audit of Completed workflows

Once a WF is completed, Dctm removes its corresponding entries from dm_workflow, dmi_workitem and dmi_package.Only dmi_queue_item contains details for the workflow_id.On enabling audit on WF, you get only the corresponding workflow details from dm_audittrail.

Why no data showed when we choose completed workflows in workflow reporting?

When selecting "Completed Workflows" under Workflow, Reporting shows results under the tab but the results does not have any link to view the completed workflow.
Include "dm_all_workflow" event in the Audit Management, so the object id and rest of the details will be stored in the audittrial table after the workflow is completed.
From DA go to Audit Management node;
Click ok "Manage Auditing by events selected for all objects in the repository";
Add "dm_all_workflow" via the Add "Event Name";
(Note for creating/editing audittrial you need to have appropriate Extended Privilieges for Audit)

Select * from dmc_completed_workflow

Some audit related queries

select * from dm_audittrail where event_description='All Workflow Events'
select * from dm_audittrail where event_name='dm_addattachment'

select dmi_workitem.r_workflow_id, dmi_workitem.r_creation_date, dmi_workitem.r_performer_name, dm_workflow.object_name from dmi_workitem, dm_workflow where dmi_workitem.r_workflow_id = '4d00303980000501' order by r_creation_date

select * from dmi_package

select object_name,r_object_type from dm_sysobject(ALL) where any i_folder_id IN (select id_5 from dm_audittrail where event_source='workflow' and event_name= 'dm_addpackage' )

To see date wise workflow tasks of a user
select * from dmi_queue_item where router_id='4d00303980001100' order by date_sent desc

If you want the documents processed in the workflow(current/completed).
Presuming the audit trail is enabled for the workflow activities,try this query.

select object_name from dm_document( or your custom type) where r_object_id in
(
select distinct r_component_id
from dmi_package dp,dm_audittrail_sp da,dm_workflow dw
where
dp.r_workflow_id = da.workflow_id
and da.event_source = 'workflow'
and da.event_name = 'dm_startworkflow'
and dp.r_workflow_id = dw.r_object_id
)


What can I query on a workflow to find out what document is being routed in that ?

The r_object_id of the document in the workflow is the r_component_id attribute of the dmi_package object for that workflow. Below are some example queries.
select r_component_id from dmi_package where r_workflow_id = '4dxxxxxxxxxxxxxx'
This will return the r_object_id of the document that is being routed in the workflow.
select object_name from dm_document where r_object_id in (select r_component_id from dmi_package where r_workflow_id = '4dxxxxxxxxxxxxxx');
This will return the name of the document being routed in the currently running workflow.
select object_name from dm_document where r_object_id in (select r_component_id from dmi_package where r_workflow_id in (select router_id from dmi_queue_item where r_object_id = '1bxxxxxxxxxxxxxx'));
This will return the object_name of the document that is being referenced by a running workflow that has a dmi_queue_item associated with it. This is useful if you are trying to find the document name, but you only have information about the queue item in the Documentum Inbox.
The attributes of dmi_queue_item do not refer to the document directly (in this case the dmi_package) that is being routed, but refers to the associated dmi_workitem. You can find out the package information, but you will have to be creative in the query that you run (see above examples).