A buddy from TAC asked for some help with pulling a report on user and Access Control Group or Role assignments in CUCM.  These were the two queries that I crafted to assist him.  His requirement was to include application users and end users, so I used a UNION for this.

The below is useful for filtering user roles:

SELECT username, group_name, user_type FROM (
SELECT au.name AS username, dg.name AS group_name, fr.name AS role_name, 'applicationuser' AS user_type
FROM applicationuser au
INNER JOIN applicationuserdirgroupmap audgmap ON au.pkid=audgmap.fkapplicationuser
INNER JOIN dirgroup dg ON dg.pkid=audgmap.fkdirgroup
INNER JOIN functionroledirgroupmap frgmap ON dg.pkid=frgmap.fkdirgroup
INNER JOIN functionrole fr ON fr.pkid = frgmap.fkfunctionrole
UNION
SELECT eu.userid AS username, dg.name AS group_name, fr.name AS role_name, 'enduser' AS user_type
FROM enduser eu
INNER JOIN enduserdirgroupmap eudgmap ON eu.pkid=eudgmap.fkenduser
INNER JOIN dirgroup dg ON dg.pkid=eudgmap.fkdirgroup
INNER JOIN functionroledirgroupmap frgmap ON dg.pkid=frgmap.fkdirgroup
INNER JOIN functionrole fr ON fr.pkid = frgmap.fkfunctionrole
)
WHERE role_name IN ("Standard CCM Admin Users", "Standard CCMADMIN Read Only")
GROUP BY user_type, group_name, username
ORDER BY user_type, username

And filtering on Access Control Groups:

SELECT DISTINCT * FROM (
SELECT au.name AS username, dg.name AS group_name, 'applicationuser' AS user_type
FROM applicationuser au
INNER JOIN applicationuserdirgroupmap audgmap ON au.pkid=audgmap.fkapplicationuser
INNER JOIN dirgroup dg ON dg.pkid=audgmap.fkdirgroup
UNION
SELECT DISTINCT eu.userid AS username, dg.name AS group_name, 'enduser' AS user_type
FROM enduser eu
INNER JOIN enduserdirgroupmap eudgmap ON eu.pkid=eudgmap.fkenduser
INNER JOIN dirgroup dg ON dg.pkid=eudgmap.fkdirgroup
)
WHERE group_name IN ("Standard CCM Super Users", "Standard CCM Admin Users")
GROUP BY user_type, group_name, username
ORDER BY user_type, username

 

Hope this is helpful.