How to list users and their prospective groups?

Question ID: 104205
5
0

Trying to create an report in QCv10 Excel Reports in the Dashboard Module.

I am trying to findout how to run a query joining the users and groups tables.

The description for the US_GROUP field in the USERS table looks like a bit mask that specifies the groups to which the user belongs. If bit 1 is set, the user belongs to the group identified with GROUPS.GR_GROUP_ID = 1. etc, etc.

Is there a way to decifer in SQL to get the user and their groups in the report?

Any help you all can provide will be greatly appricated.

Marked as spam
Posted by (Questions: 5, Answers: 1)
Asked on August 26, 2010 8:08 pm
8 views
Answers (1)
7
Private answer

You are interpreting the process corectly. If bit X is set, the user belongs to the group identified with GROUPS.GR_GROUP_ID = X.

So use this query for the Excel report.

For SQL Server:
SELECT US_USERNAME AS User_Name,
GR_GROUP_NAME AS Group_Name
FROM
td.USERS,
td.GROUPS
WHERE SUBSTRING (US_GROUP, GR_GROUP_ID+1, 1) = '1'
order by US_USERNAME

For Oracle use:
SUBSTR(US_GROUP, GR_GROUP_ID+1, 1) = '1'

This will produce a report like this:

User_Name / Group_Name
alex_qc / TDAdmin
pete_qc / QATester
paul_qc / Developer

Marked as spam
Posted by (Questions: 0, Answers: 613)
Answered on August 26, 2010 8:24 pm
Perfect! Thank You so much!!
( at August 26, 2010 8:25 pm)
My pleasure as always.
( at August 26, 2010 8:33 pm)
What About Oracle?
( at August 26, 2010 8:51 pm)
DerlWeldon, I added the information for Oracle. Check with your DBA to be sure on the syntax.
( at August 26, 2010 8:59 pm)
Great Answer, This will help me.
( at August 26, 2010 10:40 pm)