Extracting Access Control Group and Role/User Relationships from CUCM

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.

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.