select t.name,r.event,r.user_name
from dmi_registry r, dm_type t
where r.is_audittrail = 1
and t.r_object_id = r.registered_id
select r.registered_id, r.event,r.user_name
from dmi_registry r
where r.is_audittrail = 1
How to get the deleted documents list in a cabinet using DQL in Documentum?
select r_object_id from dm_cabinet where object_name = ‘Reports’
0c0271838587137a
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 = ‘0c0271838587137a’
OR l.id_1 IN (SELECT r_object_id FROM dm_folder WHERE CABINET(ID(‘0c0271838587137a’), DESCEND)))
Audit Report by Month
In an audit trail, I want to count the number of users who viewed (dm_getfile event)
all the documents that belong to a particular object type every month. These documents are in a specific folder of a cabinet.
SELECT DATETOSTRING(time_stamp, ‘yyyy/mm’) AS period, COUNT(distinct user_name) AS viewers
FROM dm_audittrail
WHERE object_type = ‘cms_in_clearings_doc’
AND event_name = ‘dm_getfile’
AND DATEFLOOR(year, “time_stamp”) = DATEFLOOR(year, DATE(’01/01/2024′, ‘mm/dd/yyyy’))
AND audited_obj_id IN (
SELECT r_object_id FROM dm_sysobject (all)
WHERE folder(‘/Accounts’, descend)
)
GROUP BY DATETOSTRING(time_stamp, ‘yyyy/mm’)
Whether we need to configure auditing for a new solution or figure out why some event was not audited, we may have to answer the question, “What is currently being audited in my repository?” Here are some handy queries to help you do just that.
List all configured audit events
This query is not very helpful other than getting the total count and the object ID’s. The queries that follow later join with various types to provide user-friendly information.
select r.registered_id, r.event,r.user_name
from dmi_registry r
where r.is_audittrail = 1
Across-the-board audit configuration
select 'ALL',r.event,r.user_name
from dmi_registry r
where r.is_audittrail = 1
and r.registered_id = '0000000000000000'
Audit configuration for types
select t.name,r.event,r.user_name
from dmi_registry r, dm_type t
where r.is_audittrail = 1
and t.r_object_id = r.registered_id
Audit configuration for Sysobjects
select t.object_name,r.event,r.user_name
from dmi_registry r, dm_sysobject t
where r.is_audittrail = 1
and t.r_object_id = r.registered_id
Audit configuration for users
select t.user_name,r.event,r.user_name
from dmi_registry r, dm_user t
where r.is_audittrail = 1
and t.r_object_id = r.registered_id