locked
AD Users group membership report RRS feed

  • Question

  • Hi Report masters

    I need some help with creating a report. I can create the result as I want it as a query but I don't know how to make it into an report. I'm not good at SQL.
    The report should list all AD users displayname, username, description, mail, ouname, usergroup in a certain OU. 

    Here is the query in WQL:
    select SMS_R_User.displayName, SMS_R_User.UserName, SMS_R_User.description, SMS_R_User.mail, SMS_R_User.UserOUName, SMS_R_User.UserGroupName from  SMS_R_User where SMS_R_User.UserOUName like "Domain/OU"

    Hope you Report masters can help me out creating this report.

    /ALX

    Thursday, February 16, 2012 10:07 AM

Answers

All replies

  • The report should list all AD users displayname, username, description, mail, ouname, usergroup in a certain OU. 


    Why do you want to use ConfigMgr for this report? This report can be fetched from Active Directory. Also, have a look at the report shared by Garth in the below thread. However, you may need to discover additional AD attributes to achieve this.

    Anoop C Nair - @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    User Group:  ConfigMgr Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.



    Thursday, February 16, 2012 10:34 AM
  • Missed the thread http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/7f48aa48-d260-4ad1-babc-8b2496d103fb

    Anoop C Nair - @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    User Group:  ConfigMgr Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Thursday, February 16, 2012 10:39 AM
  • Thanks for the answer, I don't really understand the information in the link you provided. Can't really see group membership anywhere?

    Thursday, February 16, 2012 1:12 PM
  • Here's my butchered version of Garth's query edited to display only what you ask for.

    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',
      UG.User_Group_Name0 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
       left outer join dbo.v_RA_User_UserGroupName UG on U.ResourceID = UG.ResourceID
      
       WHERE 
      OU.User_OU_Name0 Like '%MYOU%'
    Group By 
      R.User_Name0, 
      U.Full_User_Name0, 
      U.givenName0, 
      U.sn0, 
      U.mail0,
      UG.User_Group_Name0
      order By [user name]


    John Marcum | http://myitforum.com/cs2/blogs/jmarcum/|

    Thursday, February 16, 2012 1:27 PM
  • Wow thanks alot John !

    I copied the code to a new report and changed %MYOU% to domain/OU. But it returned the error: 

    An error occurred when the report was run. The details are as follows:

    Invalid column name 'givenName0'.
    Thursday, February 16, 2012 1:35 PM
  • However, you may need to discover additional AD attributes to achieve this.



    Ok :)

    Have you read my above comments ? Have you added those attributes mentioned in the above thread to the discovery?


    Anoop C Nair - @anoopmannur

    MY BLOG:  http://anoopmannur.wordpress.com

    User Group:  ConfigMgr Professionals

    This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Thursday, February 16, 2012 1:38 PM
  • Have you enable discovery for the givenName attribute? If not this query will not work...

    http://www.enhansoft.com/

    Thursday, February 16, 2012 1:40 PM
  • Oops didn't know I had to add those, I will try again. Thanks !

    Thursday, February 16, 2012 1:48 PM
  • Once you add those you will have to wait for discovery to add them before the report will work.


    John Marcum | http://myitforum.com/cs2/blogs/jmarcum/|

    Thursday, February 16, 2012 2:11 PM
  • I removed the givenname and surname attribute and the report showed :)

    Now is it possible to make the report display the User Group Name in 1 column like it did with the WQL query instead of 1 column for each User Group Name?


    Thursday, February 16, 2012 2:46 PM
  • I found this link http://myitforum.com/myitforumwp/2011/09/16/sccm-guru-webcast-qa-2-multiple-rows-in-reports/

    that basicly explains what I am trying to do, but I have no clue how to implement it?

    ALX

    Thursday, February 16, 2012 2:58 PM
  • I removed the givenname and surname attribute and the report showed :)

    Now is it possible to make the report display the User Group Name in 1 column like it did with the WQL query instead of 1 column for each User Group Name?


    I had a feeling this was gonna be your next question. I am sure it can probably be done but I don't know how to do it. One of the other guys may be able to help. I know just enough SQL to get by.


    John Marcum | http://myitforum.com/cs2/blogs/jmarcum/|

    Thursday, February 16, 2012 7:48 PM
  • Try this. http://tinyurl.com/7d96zng

    http://www.enhansoft.com/

    • Marked as answer by ALX- Friday, February 17, 2012 3:23 PM
    Friday, February 17, 2012 1:44 PM
  • Awsome Garth, the report is beatiful now !

    Really cool to see so many PROS help out in the forums.

    Thanks everyone !

    Friday, February 17, 2012 3:23 PM
  • Can you post the completed SQL?


    John Marcum | http://myitforum.com/cs2/blogs/jmarcum/|

    Friday, February 17, 2012 5:15 PM