Handy DQL: Audit Report by Month

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 <OBJECT_TYPE>, <FOLDER_PATH>, <FOUR_DIGIT_YEAR> below.

SELECT DATETOSTRING(time_stamp, 'yyyy/mm') AS period, COUNT(distinct user_name) AS viewers
FROM dm_audittrail
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:

period viewers
2009/01 3
2009/02 1
2009/04 6

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s