cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements
200
Views
0
Helpful
4
Replies
Ingo de Jager
Beginner

CUCM SQL Query for User Group access.

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Maren Mahoney
Advocate

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.

Query.jpg

 

 

 

 

 

 

 

 

HTH

Maren

View solution in original post

Nithin Eluvathingal
VIP Mentor

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

 

 



Response Signature


View solution in original post

4 REPLIES 4
Maren Mahoney
Advocate

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.

Query.jpg

 

 

 

 

 

 

 

 

HTH

Maren

View solution in original post

Excellent, I'll give that a try.

 

Regards,

Ingo

Nithin Eluvathingal
VIP Mentor

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

 

 



Response Signature


View solution in original post

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.

Create
Recognize Your Peers
Content for Community-Ad