1

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.

sb709057
  • 45
  • 6
  • when you delete object it isn't available anymore in its original table, therefore this JOIN on dm_document table doesn't make any sense. I doubt this is achievable – Miki Aug 13 '20 at 13:02

1 Answers1

2

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.

cgrim
  • 4,890
  • 1
  • 23
  • 42
  • Great, actually I tried like this for my requirement now. select * from dm_audittrail a where a.event_name='dm_destroy' and a.time_stamp between date('01/01/2018 00:00:00','mm/dd/yyyy hh:mi:ss') and date(today) and acl_name like '%imms_clin%' and (a.current_state = 'Effective' or a.current_state='Approved'); The acl_name is something related to cabinet what i needed. – sb709057 Aug 14 '20 at 14:32
  • Good to hear, that you found a way how to do that ;-) – cgrim Aug 14 '20 at 15:28