CUCM SQL Query for User Group access.

[Ingo de Jager]

Hi Guys,

Off the bat I am not a SQL person so I need a bit of help trying to combine a few SQL queries into one I can eventually run via AXL or the command line.

What I am looking for is a query to list Enduser ID's IF the enablecups setting is true. Once found I need the following:

First Name, Last Name, UserID, Primary Extension and two of the Access Control Groups EG. Standard CCM Admin Users and Standard CCM Super Users listed, perhaps even the Standard CCM End Users also.

Below is out put from another script that gives me the enduser group name membership. If I can only get the other fields in there from different tables it would be a great help.

username         group_name               user_type 
================ ======================== ========= 
ccmadministrator Standard CCM Admin Users enduser   
ccmadministrator Standard CCM Super Users enduser 

In short, this is what I want (for end users with enablecups == true):

first_name, last_name, userid, primary_extension, group_name

Ingo , de Jager, user1, 1234567890, Standard CCM Admin Users

Ingo , de Jager, user1, 1234567890, Standard CCM Super Users

User , Two, user2, 2234567890, Standard CCM End Users

User , Three, user3, 3234567890, Standard CCM End Users

Any suggestions on a SQL query that can do this?

Thanks,

Ingo

[Maren Mahoney · Accepted solution]

I'm still learning SQL queries myself, so I don't have a query that will give you what you are looking for. But another way to obtain the information would be to run a "Generate User Reports" out of BAT. You can list all of the information you mentioned. Once you have the CSV file, pull it into Excel and sort on the "Enable User for Unified CM IM and Presence" column.

I'm adding a screen cap of the Generate User Reports. The field "Access Control Group" is added below "Primary Extension", although you can't see it.

HTH

Maren

[Ingo de Jager]

Excellent, I'll give that a try.

Regards,

Ingo

[Nithin Eluvathingal · Accepted solution]

What I am looking for is a query to list Enduser ID's IF the enablecups setting is true. run sql select userid from enduser where enablecups = 't'

First Name, Last Name, UserID, Primary Extension and two of the Access Control Groups EG. Standard CCM Admin Users
run sql select firstname , lastname, userid , dirgroup.name from enduser JOIN enduserdirgroupmap on enduser.pkid = enduserdirgroupmap.fkenduser JOIN dirgroup on enduserdirgroupmap.fkdirgroup = dirgroup.pkid

[Ingo de Jager]

Thanks, that did the trick even though I have to merge the two results. The BAT User Report also worked if you don't want to go the SQL route.