Sunday, April 19, 2009

User Queries in DQL

User Queries in DQL

1. DQL to get the count on user’s login to Documentum:

Select count (*) from dm_user where last_login_utc_time>DATE (NOW)-90

Note: This query retrieves the use login information by using their last successful login as reference

2. DQL to get the list of users logged into Documentum:

Select * from dm_user where last_login_utc_time>DATE (NOW)-90

3. DQL to list the users who has access to particular folder path

SELECT i_all_users_names FROM dm_groupWHERE group_name IN (SELECT r_accessor_name FROM dm_aclWHERE object_name IN (SELECT acl_name FROM dm_folderWHERE ANY r_folder_path = ‘/folderpath’))ORDER BY i_all_users_names

For Ex in Repository 1, to get the list of users who have access to the folder /SERVICE/NONTECHNICAL

SELECT i_all_users_names FROM dm_group
WHERE group_name IN (SELECT r_accessor_name FROM dm_acl
WHERE object_name IN (SELECT acl_name FROM dm_folder
ORDER BY i_all_users_names

4. DQL to find out what user signed off on what document

SELECT audited_obj_id FROM dm_audittrail WHERE
event_name = 'dm_signoff' AND
user_name = 'Raja' AND
substr ("audited_obj_id", 1, 2) = '09'AND
"time_stamp" >= DATE('01/01/09', 'dd/mm/yy') AND
"time_stamp" <= DATE(TODAY)

5. DQL to deactivate user

UPDATE dm_user OBJECTS SET user_state = 1 WHERE user_name = 'username'

6. DQL to grant extended privileges

Grant Config Audit to User User_name

7. DQL to get the list of users whose state is active and they belong to groups

select user_name, user_os_name, user_state from dm_user where user_state=0 and r_is_group=0 order by user_name

Your ideas and suggestions are most welcome.



No comments:

Post a Comment