DQL To retrieve group to which user belongs
Select group_name from dm_group where any users_names in (select user_name from dm_user where user_os_name = 'karthik')
Select group_name from dm_group where any users_names in ('karthik')
DQL To get all the groups and count of all the active members in those groups
Select gr2.i_supergroups_names, count (gr1.users_names) from dm_group_r gr1, dm_group_r gr2 where gr1.r_object_id = gr2.r_object_id and gr2.i_supergroups_names is not null group by gr2.i_supergroups_names
DQL To get the name of all groups and name of all active users in those groups
select gr2.i_supergroups_names, gr1.users_names
from dm_group_r gr1, dm_group_r gr2
where gr1.r_object_id = gr2.r_object_id and
gr2.i_supergroups_names is not null and gr1.users_names in(select user_name from dm_user where user_state=0)
order by gr2.i_supergroups_names
DQL To get list of users and groups they belong to
SELECT DISTINCT u.user_name,u.user_os_name, g.group_name FROM dm_user u, dm_group g WHERE ANY g.i_all_users_names = u.user_name and r_is_group = 0
order by u.user_name
DQL To get the group names for particular user
SELECT DISTINCT u.user_name,u.user_os_name, g.group_name FROM dm_user u, dm_group g WHERE ANY g.i_all_users_names = u.user_name and r_is_group = 0 and u.user_name ='karthik' order by u.user_name
DQL To get all active users in a particular group
SELECT DISTINCT u.user_name,u.user_os_name,u.user_state, g.group_name FROM dm_user u, dm_group g WHERE ANY g.users_names = u.user_name and r_is_group = 0 and u.user_state=0 and g.group_name='admingroup' order by u.user_name
ALTER GROUP group_name ADD members
ALTER GROUP group_name ADD (select user_name from dm_user where user_name = 'karthik')
Ex 1:
ALTER GROUP enterprise ADD (select user_name from dm_user where user_name = 'karthik')
Ex 2:
To add 2 users namely Romeo and Juliet to a group
ALTER GROUP engineering ADD Romeo,Juliet
Ex 3:
To set/define an email address for a particular group
ALTER GROUP enterprise SET ADDRESS 'srksrv786@gmail.com'
DQL To remove user from group
ALTER GROUP group_name DROP members
ALTER GROUP group_name DROP (select user_name from dm_user where user_os_name = 'karthik')
Ex:
ALTER GROUP enterprise DROP (select user_name from dm_user where user_os_name = 'karthik')
Note: Before removing any user from any group make sure that which group the user belongs to by using the following select query
Select group_name from dm_group where any users_names in ('karthik')
DQL To assign a user to many groups without changing users default group
UPDATE dm_group OBJECTS APPEND users_names = 'SR, Karthik' WHERE group_name IN ('fe_general', 'enterprise');
DQL To set a default group for a user
UPDATE dm_user objects set user_group_name ='engineering' where user_name='SR, Karthik'
Note: If you modifying the groups repeatedly using this query, the most recently updated group using this query will be the default group for the user and the previously added groups also will be there in the groups that user belongs to.
For ex,
UPDATE dm_user objects set user_group_name ='enterprise' where user_name='SR, Karthik'
After the execution of this query, default group for Karthik will be enterprise and we check for the group that the user belongs, to all these groups for ex enterprise and also docu groups will be there
DQL To retrieve groups access info on the folders
SELECT r_accessor_name, r_accessor_permit FROM dm_acl
WHERE object_name IN (SELECT acl_name FROM dm_folder
WHERE ANY r_folder_path = '/SASBU/NON BLOCK SPECIFIC')
DROP GROUP group_name
Ex: Drop Group finance_orders
DQL To get subgroups
Select group_name from dm_group where any i_supergroups_names='enterprise'
Note: Using this query we can find the list of subgroups that the group enterprise (super group) has.
DQL To deactivate user
UPDATE dm_user OBJECTS SET user_state = 1 WHERE user_name = 'username'.
Ex:
UPDATE dm_user OBJECTS SET user_state = 1 WHERE user_name = 'SR, Karthik'
Grant Config Audit to User User_name
DQL to provide/assign/give groups access on the folders
SELECT r_accessor_name, r_accessor_permit FROM dm_acl
WHERE object_name IN (SELECT acl_name FROM dm_folder
WHERE ANY r_folder_path = '/FolderPath….')
Ex:
SELECT r_accessor_name, r_accessor_permit FROM dm_acl
WHERE object_name IN (SELECT acl_name FROM dm_folder
WHERE ANY r_folder_path = '/Equity/Europe')
DQL To get subgroups
select group_name from dm_group where any i_supergroups_names = 'YourGroupName'
Ex:
select group_name from dm_group where any i_supergroups_names = 'Materials_department'
Please provide your valuable suggestions if any.
Thanks & Regards,
S.R.Karthik
Awesome compilation of various DQL
ReplyDeletegud stuff
ReplyDeletehi...i m new to documentum and find ur stuff on this blog,very useful.
ReplyDeleteGUD Work...
Need query for getting all the availabel docbases?
ReplyDeleteCan any one suggest me?
thank u your queries are really helpful
ReplyDeletereally helpful! thank you!
ReplyDeleteHi Karthik,
ReplyDeleteThank you very much for the excellent posting. I am looking for a query which finds the top level group ie the supergroup of all the groups in a docbase. Would any body post that query ?
Excellent...really helpful
ReplyDeleteGreat to see this and more helpfulll
ReplyDeleteHey KarthiK,
ReplyDeleteThanks you so much..blog is useful.
referred multiple time..
Regards,
Anish
I want to get the list of all user_os_name, user_name and all group he belongs to
ReplyDeletePlease give me the dql which can display above things.
Please send me the dql to vu2major@gmail.com
Thanks
You can try with the following query to get the list of users & their groups as shown below.
Deleteselect distinct users_names, group_name from dm_group where any users_names in(select user_name from dm_user where user_state=0 and r_is_group=0) order by users_names
Please find the DQL Query to get the user_os_name, user_name and all groups that an user belongs to as follows.
DeleteSELECT DISTINCT u.user_name,u.user_os_name, g.group_name FROM dm_user u, dm_group g WHERE ANY g.i_all_users_names = u.user_name and r_is_group = 0 and u.user_name ='Karthik Sevugan Ratnam' order by u.user_name