Do you want the data for a project, or for the entire system? There is a USERS table in the Site Admin database that contains usernames and user full names. In ALM QC v11 in Site Admin on the Site Users tab there is a toolbar button to export the user information.
Within a Project there is also a USERS table, but it has only the username, not the user full name. There is a field for full name in that table, but in my system there is no data in that field. From within a project you could construct an Excel report in the Dashboard module to extract the data from the Project USERS table.
(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
The below query can be helpful to you.Using this query you get the userid and the roles which he have in that project.
SELECT m.Project Project, m.usname US_USERNAME, ISNULL(m.Admin,0) Admin, ISNULL(m.Bus_Owner,0) Bus_Owner, ISNULL (m.Bus_Reviewer,0) Bus_Reviewer, ISNULL(m.Defect_Resolver,0) Defect_Resolver, ISNULL(m.Inactive ,0) Inactive , ISNULL(m.Lead_Test_Mgr,0) Lead_Test_Mgr, ISNULL(m.Quality,0) Quality, ISNULL(m.Req_Author,0) Req_Author, ISNULL(m.Tech_Reviewer,0) Tech_Reviewer, ISNULL(m.Test_Analyst,0) Test_Analyst, ISNULL(m.Test_Mgr,0) Test_Mgr, ISNULL(m.Tester,0) Tester, ISNULL(m.View_Only,0) View_Only FROM ( SELECT DB_NAME() AS Project, u.US_USERNAME usname, MAX(TDAdmin) Admin, MAX(Business_Owner) as Bus_Owner, MAX(Business_Reviewer) as Bus_Reviewer, MAX(Defect_Resolver) as Defect_Resolver, MAX(Inactive) Inactive , MAX(Lead_Test_Manager) as Lead_Test_Mgr, MAX(Quality) Quality, MAX(Requirement_Author) as Req_Author, MAX(Technical_Reviewer) as Tech_Reviewer, MAX(Test_Analyst) as Test_Analyst, MAX(Test_Manager) as Test_Mgr, MAX(Tester) Tester, MAX(View_Only) as View_Only FROM ( SELECT DISTINCT us_username, CASE gr_Group_Name WHEN 'TDAdmin' THEN 1 END TDAdmin, CASE gr_Group_Name WHEN 'QATester' THEN 1 END QATester, CASE gr_Group_Name WHEN 'Project Manager' THEN 1 END Project_Manager, CASE gr_Group_Name WHEN 'Developer' THEN 1 END Developer, CASE gr_Group_Name WHEN 'Viewer' THEN 1 END Viewer, CASE gr_Group_Name WHEN 'Quality' THEN 1 END Quality, CASE gr_Group_Name WHEN 'Test Analyst' THEN 1 END Test_Analyst, CASE gr_Group_Name WHEN 'Test Manager' THEN 1 END Test_Manager, CASE gr_Group_Name WHEN 'Tester' THEN 1 END Tester, CASE gr_Group_Name WHEN 'Defect Resolver' THEN 1 END Defect_Resolver, CASE gr_Group_Name WHEN 'Inactive' THEN 1 END Inactive, CASE gr_Group_Name WHEN 'Business Owner' THEN 1 END Business_Owner, CASE gr_Group_Name WHEN 'Business Reviewer' THEN 1 END Business_Reviewer, CASE gr_Group_Name WHEN 'eApprove Administrator' THEN 1 END eApprove_Administrator, CASE gr_Group_Name WHEN 'Requirement Author' THEN 1 END Requirement_Author, CASE gr_Group_Name WHEN 'Lead Test Manager' THEN 1 END Lead_Test_Manager, CASE gr_Group_Name WHEN 'View Only' THEN 1 END View_Only, CASE gr_Group_Name WHEN 'Technical Reviewer' THEN 1 END Technical_Reviewer FROM td.USERS,td.GROUPS WHERE SUBSTRING (US_GROUP, GR_GROUP_ID+1, 1) = '1' ) u GROUP BY u.US_USERNAME )m
Get the user details from the LDAP and create it as a lookup file.
Compare the userid of every record which came as a output of the above query with that of the userid in the lookup file ( you can use VLOOKUP function ) to get the username.
You can use DB_NAME() to get the project database name.
This query will help you to join the tables USERS AND GROUPS
Additional interesting query to return list of Resp to User assignement
--User to resp
FROM groups gr,
Hope this help