Display the User Group Name in 1 column

To see full forum post see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/986960ba-8a50-4e69-9e5a-64a40c9d8df5/

SELECT 
  R.User_Name0 as 'user name', 
  U.Full_User_Name0 as 'full name', 
  U.givenName0 as 'First Name', 
  U.sn0 as 'Surname', 
  U.mail0 as 'Email',  
  max(OU.User_OU_Name0) as 'OU Path',
  Groups.Groups as 'User Group'
 FROM  
  dbo.v_R_System R
  JOIN dbo.v_FullCollectionMembership FCM ON R.ResourceID = FCM.ResourceID
  JOIN dbo.v_R_User U ON R.User_Domain0+ '\'+R.User_Name0 = U.Unique_User_Name0
  left outer join dbo.v_RA_User_UserOUName OU on U.ResourceID = OU.ResourceID
  join (SELECT U.ResourceID, STUFF((  SELECT 
                      ',' + UG.User_Group_Name0 
                    FROM 
                      dbo.v_RA_User_UserGroupName UG 
                    WHERE 
                      U.ResourceID = UG.ResourceID 
                    FOR XML PATH('')),1,1,'') AS Groups 
                    FROM 
                      dbo.v_R_User U) as Groups on U.ResourceID = Groups.ResourceID
WHERE 
  OU.User_OU_Name0 Like '%SQL%'
Group By 
  R.User_Name0, 
  U.Full_User_Name0, 
  U.givenName0, 
  U.sn0, 
  U.mail0,
  Groups.Groups
order By 
  R.User_Name0

Leave a Reply

Your email address will not be published. Required fields are marked *