How to get the deleted documents list in a cabinet using DQL in Documentum?

I'm trying to find the list of documents deleted using DQL with few conditions, but I need to find the list that belongs to only a particular cabinet. I'm not able to frame query using audittrail with r_object_id of dm_cabinet.

Maybe something like this?

select distinct a.r_object_id, a.event_name, a.user_name, a.audited_obj_id, a.time_stamp from dm_audittrail a, dm_document d where a.event_name='dm_destroy' and a.time_stamp > date(today) and (a.current_state = 'Effective' or a.current_state='Approved') and d.i_cabinet_id='0c0033xxxx...';

Please help me.

Solution

When you delete object in Documentum then it is really deleted (at least its metadata, document content persists until dm_clean job is executed). There is no trash feature like in other DMS systems (like Nuxeo for example). This is why the SELECT query from dm_audittrail with join on dm_document cannot return expected information.

But there is a way how to do it if you also log dm_link events into audit trail. Then you can use query like this one:

SELECT * FROM dm_audittrail d, dm_audittrail l 
  WHERE d.event_name = 'dm_destroy' 
  AND l.event_name = 'dm_link' 
  AND d.audited_obj_id = l.audited_obj_id
  AND (l.id_1 = '0c00ad3f80000106' 
    OR l.id_1 IN (SELECT r_object_id FROM dm_folder WHERE CABINET(ID('0c00ad3f80000106'), DESCEND)))

It finds information about the deleted document from audit trail and joins it with information about linking of that document also from audit trail which contains parent folder ID in id_1.

But keep in mind that when you delete documents also with folders then this will not work using this one query and you have to traverse audit trail also for deleted folders.