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.