AD discovery and add the following attributes (givenname, sn, mail & ou) report

For full details see. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/7f48aa48-d260-4ad1-babc-8b2496d103fb

I fix the query to make it work, change OS to a readable name, formatting etc..

SELECT 
  R.Name0 as 'Host Name', 
  CS.Model0 as 'model', 
  BIOS.SerialNumber0 as 'SN', 
  OS.Caption0 as 'OS', 
  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'
 FROM 
  dbo.v_R_System R
  JOIN dbo.v_FullCollectionMembership FCM ON R.ResourceID = FCM.ResourceID
  JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID
  JOIN dbo.v_GS_PC_BIOS BIOS ON R.ResourceID = BIOS.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 dbo.v_GS_OPERATING_SYSTEM os on CS.ResourceID = OS.ResourceID
 WHERE 
  FCM.CollectionID = @CollectionID
Group By 
  R.Name0,
  CS.Model0, 
  BIOS.SerialNumber0, 
  OS.Caption0,
  R.User_Name0, 
  U.Full_User_Name0, 
  U.givenName0, 
  U.sn0, 
  U.mail0  

Leave a Reply

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