UPDATE! The community will be go into read-only on April 19, 8am Pacific in preparation for migration on April 21. Read more.
UPDATE! The community will be go into read-only on April 19, 8am Pacific in preparation for migration on April 21.Read more.
Absent Member.. Absent Member..
Absent Member..
2507 views

User id , users full name report

hi i need a report with following details . please help me out 

 

User ID in HPQC                     User Name                     Team

0 Likes
5 Replies
Absent Member.. Absent Member..
Absent Member..

Where is the "Team" information stored in QC? I'm not familiar with a field of this name.

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.
[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(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.)
0 Likes
Knowledge Partner Knowledge Partner
Knowledge Partner

Hi MACHMAN,

 

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.

 

Regards,

Srihari

404-579-5368

 

 

0 Likes
Absent Member.. Absent Member..
Absent Member..

This query will help you to join the tables USERS AND GROUPS

SELECT USERS.US_USERNAME,
GROUPS.GR_GROUP_ID,
GROUPS.GR_GROUP_NAME
FROM USERS,
GROUPS
WHERE(INSTR(US_GROUP,1,1,1)-1) =GROUPS.GR_GROUP_ID

 

http://www.qualitycenterstepbystep.com/2012/join-tablas-users-y-groups/

0 Likes
Absent Member.
Absent Member.

Hello,

 

Additional interesting query to return list of Resp to User assignement

 

--User to resp

SELECT us.us_username,

gr.gr_group_name,

SUBSTR(us.us_group,gr.gr_group_id+1,1) respassgn

FROM groups gr,

users us

WHERE 1=1

    AND SUBSTR(us.us_group,gr.gr_group_id+1,1)=1

 

Hope this help

Regards

Erwan

0 Likes
Vice Admiral Vice Admiral
Vice Admiral

I am getting error message " SUBSTR is not a built in recognized function name" 

 

We are on slq server 2008 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.