CUCM 9.1.2 SQL query

[bholashrestha]

Hi Everyone,

I need to get the list of application users with their assigned persmision group using SQL query.

For example I would like to see something as below:

AppUser                                                            Permission Group

*********                                                             *********************

user1                                                                 Standard CCM Admin Users

user2                                                                 Standard CCM Read Only

I would really appriciate if anyone can help me to do this. Thanks

Regrds

BholaS

[Nadeem Ahmed]

you can use this query filter later based on your requirement but here you will Appuser along with device and permissiongroup.

run sql select au.name as ApplicationUser, d.name as Device, d.description as DeviceDescription, dg.name as PermissionGroup from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard ='f' inner join ApplicationUserDirGroupMap as audgm on audgm.fkapplicationuser=au.pkid inner join DirGroup as dg on audgm.fkdirgroup=dg.pkid inner join device as d on d.pkid=audm.fkdevice order by au.name

Br,

Nadeem

PS;Please rate all useful post.

[Nadeem Ahmed]

I just modified the query now you will get the required result which you are looking for. Test in my lab too. Cheers

applicationuser permissiongroup
=============== =========================================
cuejtapi        Standard CTI Enabled
cuejtapi        Standard CTI Allow Control of All Devices
cuejtapi        Standard CTI Allow Control of All Devices
cuejtapi        Standard CTI Enabled

run sql select au.name as ApplicationUser, dg.name as PermissionGroup from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard ='f' inner join ApplicationUserDirGroupMap as audgm on audgm.fkapplicationuser=au.pkid inner join DirGroup as dg on audgm.fkdirgroup=dg.pkid inner join device as d on d.pkid=audm.fkdevice order by au.name

Br,

Nadeem Ahmed

PS: Please rate all useful post.

[bholashrestha]

Thanks Nadeem,

Above statement is only displaying the application users with device/or CTI Route point associated with it. What I would like is to display all the application users regardless if has got device/ CTI Route Point or not. Would you be able to assist?

Thanks in advance.

BholaS

[ricardomorcelo]

Or, just the sql query the list of members of any one, or all, of the permission groups.