User query for user group in Quality Center / ALM?
Question ID: 105414

Does anyone have a user query that they use to find all the users/user info for a project including gathering the user group string that shows what user groups are turned on for a particular user?

Marked as spam
Posted by (Questions: 100, Answers: 5)
Asked on May 14, 2014 4:55 pm
Answers (1)
Private answer

Try the query below, you may need to tweak it for your environment, I have used this one in QC 11 before so it should work, you will need to edit the code in two places, you will need to change 'project1' in the where statement to the name of the project and also change the [default_project1_db] to your project database in the third join statement. Also if the name of your qcsiteadmin_db is different you will need to change that as well. I created this code for use in the excel report in the QC dashboard so that is where I would try and run the queries from. You will still need to decipher the User Group column to figure out what groups the user is a part of.

SELECT admin.[USER_ID] as 'User ID'
,admin.[USER_NAME] as 'User Name'
,admin.[ACC_IS_ACTIVE] as 'ACC is Active'
,admin.[FULL_NAME] as 'Full Name'
,admin.[EMAIL] as 'Email'
,admin.[USER_PASSWORD] as 'User Password'
,admin.[DESCRIPTION] as 'Description'
,admin.[PHONE_NUMBER] as 'Phone Number'
,admin.[LAST_UPDATE] as 'Last Update'
,admin.[US_DOM_AUTH] as 'Dom Auth'
,admin.[US_REPORT_ROLE] as 'Report Role'
,admin.[US_IS_SYSTEM] as 'Is system user?'
,admin.[US_IS_ACTIVE] as 'Is Active'
,admin.[US_EXPIRATION_DATE] as 'Exp Date'
,userprojects.[PROJECT_ID] as 'Project ID'
,projects.[PROJECT_NAME] as 'Project Name'
,projects.[DOMAIN_NAME] as 'Domain Name'
,'UserGroups-' + CAST(projectusers.US_GROUP As VARCHAR(255)) as 'User Group'
FROM [qcsiteadmin_db].[td].[USERS] admin
INNER JOIN [qcsiteadmin_db].[td].[USERS_PROJECTS] userprojects
ON admin.[USER_ID]=userprojects.[USER_ID]
Inner JOIN [qcsiteadmin_db].[td].[PROJECTS] projects
ON userprojects.[PROJECT_ID]=projects.[PROJECT_ID]
Inner Join [default_project1_db].[td].[USERS] projectusers
ON admin.[USER_NAME]=projectusers.[US_USERNAME]
Where projects.[PROJECT_NAME]= 'project1'
Order By admin.[USER_ID]

I had to add the ''UserGroups-'' to the User groups column so that Excel does not change the format and drop the preceding zeros from the group string.

Marked as spam
Posted by (Questions: 0, Answers: 364)
Answered on May 14, 2014 4:59 pm

Welcome back to "EyeOnTesting" brought to you by Orasi Software, Inc.

Scroll to Top