Recently, I received a request for a DQL as follows:
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.
I made couple of assumptions in coming up with the DQL, but these are easy to adjust to suit specific needs. When looking for documents in a folder, I assumed the containment to be recursive. If you don’t need to look in the subfolders, just remove “
, descend” in the
folder() predicate below.
The other assumption I made was that the period of check was 1 calendar year.
For example, create a report for 2009 by each month. Again, if the need is different, the just modify the date range condition.
Alter the following DQL, by replacing
SELECT DATETOSTRING(time_stamp, 'yyyy/mm') AS period, COUNT(distinct user_name) AS viewers
WHERE object_type = '<OBJECT_TYPE>'
AND event_name = 'dm_getfile'
AND DATEFLOOR(year, "time_stamp") = DATEFLOOR(year, DATE('01/01/<FOUR_DIGIT_YEAR>', 'mm/dd/yyyy'))
AND audited_obj_id IN (
SELECT r_object_id FROM dm_sysobject (all)
WHERE folder('<FOLDER_PATH>', descend)
GROUP BY DATETOSTRING(time_stamp, 'yyyy/mm')
A sample result from this query is shown below: