none
Output members of AD group and nested groups with path inside script task RRS feed

  • Question

  • Hi,

    I was able to get members of group and nested groups ,i would also like to output the group within which the user falls.

    Example, Nancy belongs to the Security group Oracle that I am looking into,but she is part of this group indirectly,meaning belonging to another group called Analytics.This group Analytics has been added to Oracle group.Hence,she is part of Oracle through Analytics.

    Basically,i want output the nested group name (if applicable to a user besides their names: if a user is directly added to Oracle,they appear as just Oracle,else nestedgroup\Oracle) .Any other format besides one I mention below for ADGroup that conveys what is the group the user is coming from will suffice.

    I tried the below to get the list of all users within AD group ,but not sure of how to get nested groups names alongwith.

      public void Main()
           
             {

                 PrincipalContext ctx = new PrincipalContext(ContextType.Domain, "abc.com");
                 GroupPrincipal grp = GroupPrincipal.FindByIdentity(ctx, IdentityType.Name, "Oracle");

                 if (grp != null)
                 {
                 foreach (Principal p in grp.GetMembers(true))
                  {
             
                      System.IO.File.AppendAllText(@"C:\Users\JDoe\Documents\Test.txt", "Date :" + DateTime.Now.ToString() + "\t" + "Message :" + p.Name +Environment.NewLine);
                  } 

                 grp.Dispose();
                     ctx.Dispose();

                 }
                 else
                 {
                     System.IO.File.AppendAllText(@"C:\Users\Jdoe\Documents\Test.txt", "Date :" + DateTime.Now.ToString() + "\t" + "Message :We did not find that group:" + grp + " in that domain, perhaps the group resides in a different domain" + Environment.NewLine);
                 }

             }

    

    Friday, November 16, 2018 8:03 PM

Answers

  • I think it is going to boil down to what you have available and what you want. Here's an implementation that takes a group name and enumerates all the members (directly or indirectly). For indirect members it appends the group that got there. Note however that this wouldn't be a valid "path" that you could use to get the group. I verified it works for 2 levels but not beyond that. Also note that dups are possible.

    It relies on iterators so you'll need to be using a version of C# that supports it. Since you're using SSIS this may or may not work inside SSIS. If it doesn't then you'll have to resort to using a List<string> instead of yield. Also note that this code doesn't do any error handling.

    static void Main ( string[] args )
    {
        using (var context = new PrincipalContext(ContextType.Domain, "mydomain"))
        {
            var group = "Oracle";
    
            var members = GetGroupMembers(context, group);
            foreach (var member in members)
                Console.WriteLine(member);
        };
    
        Console.ReadLine();
    }
    
    static IEnumerable<string> GetGroupMembers ( PrincipalContext context, string groupName )
    {
        var group = GroupPrincipal.FindByIdentity(context, groupName);
        if (group != null)
        {
            var members = group.GetMembers();
    
            //Dump the users first
            foreach (var member in members.OfType<UserPrincipal>())
            {
                yield return member.SamAccountName;
            };
    
            //Enumerate the groups
            foreach (var member in members.OfType<GroupPrincipal>())
            {
                var children = GetGroupMembers(member, member.Name);
                foreach (var child in children)
                    yield return child;
            };
        };
    }
    
    static IEnumerable<string> GetGroupMembers ( GroupPrincipal root, string parentPath )
    {
        var members = root.GetMembers();
    
        //Dump the users first
        foreach (var member in members.OfType<UserPrincipal>())
        {
            yield return $"{parentPath}/{member.SamAccountName}";
        };
    
        //Enumerate the groups
        foreach (var member in members.OfType<GroupPrincipal>())
        {
            var children = GetGroupMembers(member, $"{parentPath}/{member.Name}");
            foreach (var child in children)
                yield return child;
        };        
    }



    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by msdnpublic1234 Monday, November 19, 2018 9:51 PM
    Monday, November 19, 2018 5:10 PM
    Moderator
  • static void Main ( string[] args )
    {
        using (var context = new PrincipalContext(ContextType.Domain, "mydomain"))
        {
            var group = "Oracle";
    
            var members = GetGroupMembers(context, group);
            foreach (var member in members.Rows.OfType<DataRow>())
                Console.WriteLine("{0} : {1}", member["Name"], member["Group"]);
        };
    
        Console.ReadLine();
    }
    
    static DataTable GetGroupMembers ( PrincipalContext context, string groupName )
    {
        var dt = new DataTable();
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Group", typeof(string));
    
        var group = GroupPrincipal.FindByIdentity(context, groupName);
        if (group != null)
            GetGroupMembersCore(dt, group, group.Name);
    
        return dt;
    }
    
    static void GetGroupMembersCore ( DataTable dt, GroupPrincipal root, string parentPath )
    {
        var members = root.GetMembers();
    
        //Dump the users first
        foreach (var member in members.OfType<UserPrincipal>())
        {
            dt.Rows.Add(member.SamAccountName, parentPath);
        };
    
        //Enumerate the groups
        foreach (var member in members.OfType<GroupPrincipal>())
        {
            GetGroupMembersCore(dt, member, $"{parentPath}/{member.Name}");
        };
    }



    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by msdnpublic1234 Tuesday, November 20, 2018 3:14 PM
    Tuesday, November 20, 2018 2:48 PM
    Moderator

All replies

  • Hi msdnpublic1234,

    The following thread share two about workaround which using GetAuthorizationGroups & System.DirectoryServices for your reference.

    https://stackoverflow.com/questions/5312744/how-to-determine-all-the-groups-a-user-belongs-to-including-nested-groups-in-a

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 19, 2018 3:27 AM
    Moderator
  • Thanks Zhangloug.I am able to get the list of all users that are part of the group and nested group.Also,the groups in the context of which the user is a member.I want to just output the groups that are nested within the group i m searching and exclude all the other groups that user is a member of.

    Mainly,I want to have the nested group name if applicable for all those users who are indirectly part of the group i m searching.

    This is what i have so far:

      public void Main()

            {

                PrincipalContext ctx = new PrincipalContext(ContextType.Domain, "abc.com");
                GroupPrincipal grp = GroupPrincipal.FindByIdentity(ctx, IdentityType.Name, "Oracle");

                if (grp != null)
                {
                    foreach (Principal p in grp.GetMembers(true))
                    {
                     
                        System.IO.File.AppendAllText(@"C:\Users\JDoe\Documents\UsersList", "Date :" + DateTime.Now.ToString() + "\t" + "Message :" + p.Name + Environment.NewLine);
                        foreach (Principal userGroup in p.GetGroups(ctx))
                       
                            {
                                          System.IO.File.AppendAllText(@"C:\Users\JDoe\Documents\UsersList.txt", "Date :" + DateTime.Now.ToString() + "\t" + "Message :Member of Group-" + userGroup.Name + Environment.NewLine);
                            }
                        }
                    }
                    grp.Dispose();
                    ctx.Dispose();

                }
                else
                {
     );
                    System.IO.File.AppendAllText(@"C:\Users\JDoe\Documents\UsersList.txt", "Date :" + DateTime.Now.ToString() + "\t" + "Message :We did not find that group:" + grp + " in that domain, perhaps the group resides in a different domain" + Environment.NewLine);
                }

            }

    Monday, November 19, 2018 2:09 PM
  • I think it is important to note that there is no such thing as a nested group in Windows. If you use an AD browser you'll see that the groups defined in an OU are all at the same level. So you don't really have a hierarchy. A hierarchy would imply that if you deleted group A then its child group's B and C would also be removed but that concept doesn't exist in Windows AFAIK.

    What you do have is group memberships. So group B can be a member of group A and a user who is in group B is also a member of group A. In that regard there is a relationship. But consequently a group can be in several relationships so you can have dups. For example if group A has a group B and group C also has a group B and user is in group B then they are in group A and group C as well but from either path.

    To get the nested information you're going to have to recursively search the group memberships. This is going to be expensive which is one reason why Windows provides an alternative approach to getting all the group members flattened. But since you want the nesting information I don't know any other way than to enumerate the groups. Off the top of my head I'm thinking if you have the root group then you can enumerate its members. For each child group you then repeat the process. If you're looking for a particular member then you'll end up checking each group for that member. Whether it would be faster to enumerate the group members or use IsInRole I don't know. For small groups I'm thinking enumerating the members is good but for a large group like IT IsInRole may be faster.  


    Michael Taylor http://www.michaeltaylorp3.net


    • Edited by CoolDadTxModerator Monday, November 19, 2018 3:49 PM Fixed typo in group letters
    Monday, November 19, 2018 3:25 PM
    Moderator
  • Thanks Michael.Could you provide a reference as I have been looking at a lot of them and dont know which one to follow for getting nesting info.I am seeing this:

    https://www.codeproject.com/Articles/27281/Retrieve-Names-from-Nested-AD-Groups

    Will be great if you can help me start.
    Monday, November 19, 2018 3:34 PM
  • I think it is going to boil down to what you have available and what you want. Here's an implementation that takes a group name and enumerates all the members (directly or indirectly). For indirect members it appends the group that got there. Note however that this wouldn't be a valid "path" that you could use to get the group. I verified it works for 2 levels but not beyond that. Also note that dups are possible.

    It relies on iterators so you'll need to be using a version of C# that supports it. Since you're using SSIS this may or may not work inside SSIS. If it doesn't then you'll have to resort to using a List<string> instead of yield. Also note that this code doesn't do any error handling.

    static void Main ( string[] args )
    {
        using (var context = new PrincipalContext(ContextType.Domain, "mydomain"))
        {
            var group = "Oracle";
    
            var members = GetGroupMembers(context, group);
            foreach (var member in members)
                Console.WriteLine(member);
        };
    
        Console.ReadLine();
    }
    
    static IEnumerable<string> GetGroupMembers ( PrincipalContext context, string groupName )
    {
        var group = GroupPrincipal.FindByIdentity(context, groupName);
        if (group != null)
        {
            var members = group.GetMembers();
    
            //Dump the users first
            foreach (var member in members.OfType<UserPrincipal>())
            {
                yield return member.SamAccountName;
            };
    
            //Enumerate the groups
            foreach (var member in members.OfType<GroupPrincipal>())
            {
                var children = GetGroupMembers(member, member.Name);
                foreach (var child in children)
                    yield return child;
            };
        };
    }
    
    static IEnumerable<string> GetGroupMembers ( GroupPrincipal root, string parentPath )
    {
        var members = root.GetMembers();
    
        //Dump the users first
        foreach (var member in members.OfType<UserPrincipal>())
        {
            yield return $"{parentPath}/{member.SamAccountName}";
        };
    
        //Enumerate the groups
        foreach (var member in members.OfType<GroupPrincipal>())
        {
            var children = GetGroupMembers(member, $"{parentPath}/{member.Name}");
            foreach (var child in children)
                yield return child;
        };        
    }



    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by msdnpublic1234 Monday, November 19, 2018 9:51 PM
    Monday, November 19, 2018 5:10 PM
    Moderator
  • Thanks a lot Michael,I am trying your solution and will get back if further help needed.
    Monday, November 19, 2018 5:53 PM
  • Michael,

    This works like charm.However,there is more to it that i need to do.I want to store this member properties like displayname,email,Groupname (example-Oracle),parentpath(if applicable) as rows into datatable.I would then use this to join with another datatable containing rows from sharepoint list.At the end,I want to have something like attached

    inside of datatable so I can query it for joining with other datatable

    Monday, November 19, 2018 9:51 PM
  • I believe you have everything you need with the enumeration code. Instead of returning string you'd need to return the data though. You could probably create a DataTable with the columns you need before enumeration and then enumerate through using the posted code. Each time create a DataRow with the appropriate data instead of a string. As rows are returned then add them to your DataTable. After that you'd just have to join with your other table(s) you're creating.

    Michael Taylor http://www.michaeltaylorp3.net

    Monday, November 19, 2018 10:55 PM
    Moderator
  • I have been trying in that direction.I am stuck here with error on the GetGroupMembers.Also,I am not sure how to add rows from nested group into datatable.Something is not right in the way i m implementing.Also,within the using {},I have a var member referring to GetGroupMembers,which is why it is not returning anything from table.Pls guide,as i dont know how to fix.

       public void Main()

            {
             
                using (var context = new PrincipalContext(ContextType.Domain, "abc"))
                {
                    var group = "Oracle";

                    var members = GetGroupMembers(context, group);
                    foreach (var member in members)
                        System.IO.File.AppendAllText(@"C:\Users\Jdoe\Documents\Users.txt", "Date :" +
                        DateTime.Now.ToString() +"\t"+ member + Environment.NewLine);

                }

            }                                                       

    private static IEnumerable<DataTable> GetGroupMembers(PrincipalContext context, string groupName)
            {
                var group = GroupPrincipal.FindByIdentity(context, groupName);
                if (group != null)
                {
                    var members = group.GetMembers();

                        var table = new DataTable();
                        table.Columns.Add("Name", typeof(string));
                        table.Columns.Add("Group", typeof(string));
                    //Dump the users first
                    foreach (var member in members.OfType<UserPrincipal>())
                    {
                        var row = table.NewRow();

                            row["Name"] = member.DisplayName;
                            row["Group"] = group.DisplayName;
                            table.Rows.Add(row);

                        //yield return $"{group.DisplayName}/{member.DisplayName}";

                    }

                    //Enumerate the groups
                    foreach (var member in members.OfType<GroupPrincipal>())
                    {
                        var children = GetGroupMembers(member, member.Name);
                        foreach (var child in children)
                            //yield return child;
                    }

    yield return table;                                                                                                                        }
            }

            static IEnumerable<string> GetGroupMembers(GroupPrincipal root, string parentPath)
            {
                var members = root.GetMembers();

                //Dump the users first
                foreach (var member in members.OfType<UserPrincipal>())
                {
                    yield return $"{parentPath}/{member.DisplayName}";
                    /* yield return $"{member.DisplayName}";
                    yield return $"{parentPath}";  */
                }

    Tuesday, November 20, 2018 2:16 AM
  • static void Main ( string[] args )
    {
        using (var context = new PrincipalContext(ContextType.Domain, "mydomain"))
        {
            var group = "Oracle";
    
            var members = GetGroupMembers(context, group);
            foreach (var member in members.Rows.OfType<DataRow>())
                Console.WriteLine("{0} : {1}", member["Name"], member["Group"]);
        };
    
        Console.ReadLine();
    }
    
    static DataTable GetGroupMembers ( PrincipalContext context, string groupName )
    {
        var dt = new DataTable();
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Group", typeof(string));
    
        var group = GroupPrincipal.FindByIdentity(context, groupName);
        if (group != null)
            GetGroupMembersCore(dt, group, group.Name);
    
        return dt;
    }
    
    static void GetGroupMembersCore ( DataTable dt, GroupPrincipal root, string parentPath )
    {
        var members = root.GetMembers();
    
        //Dump the users first
        foreach (var member in members.OfType<UserPrincipal>())
        {
            dt.Rows.Add(member.SamAccountName, parentPath);
        };
    
        //Enumerate the groups
        foreach (var member in members.OfType<GroupPrincipal>())
        {
            GetGroupMembersCore(dt, member, $"{parentPath}/{member.Name}");
        };
    }



    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by msdnpublic1234 Tuesday, November 20, 2018 3:14 PM
    Tuesday, November 20, 2018 2:48 PM
    Moderator
  • Michae,

    You rock and thanks!!

    Tuesday, November 20, 2018 3:14 PM
  • Michael,

    I see some duplicate rows added to the table. Pls suggest how to remove the duplicate rows from datatable. I tried some methods but seems like i have to convert the DT type to IEnumerable.Not sure if i should or not .Pls suggest a best way.Also,dont know why there are duplicate rows ,like almost double the number of rows.I have temporarily hard coded the dt but later make it dynamic by reading that data from sharepoint list.Following is what i have currently:

    public void main()

    {

     var dt = new DataTable();
                    dt.Columns.Add("ADGroupName", typeof(string));
                    dt.Columns.Add("CubeName", typeof(string));
                    dt.Rows.Add("Oracle", "Oracle");
                    dt.Rows.Add("Peoplesoft", "Peoplesoft");
                  
                //AD querying
                using (var context = new PrincipalContext(ContextType.Domain, "abc123"))
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            var str1 = dr[0].ToString();

                           
                            var members = GetGroupMembers(context, str1, dr[1].ToString());
                           

                            string connectionString = GetConnectionString();
                           
                            using (SqlConnection connection =
                                       new SqlConnection(connectionString))
                            {
                                connection.Open();
                               
                                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                                {
                                    bulkCopy.DestinationTableName = "MembersFromDB";
                                    
                                        bulkCopy.ColumnMappings.Add(0, "UserName");
                                        bulkCopy.ColumnMappings.Add(1, "DisplayName");
                                        bulkCopy.ColumnMappings.Add(2, "Email");
                                        bulkCopy.ColumnMappings.Add(3, "ADGroup");
                                        bulkCopy.ColumnMappings.Add(4, "ADPath");
                                        bulkCopy.ColumnMappings.Add(5, "CubeName");
                                    try
                                    {
                                        bulkCopy.WriteToServer(members);

                                    }


                                    catch (Exception e)
                                    {
                                        System.IO.File.AppendAllText(@"C:\Users\JDoe\Documents\Users.txt", "Date :" +
                         DateTime.Now.ToString() + "\t" + "Exception reported as: " + e + Environment.NewLine);
                                    }
                                }
                                foreach (var member in members.Rows.OfType<DataRow>())
                                    System.IO.File.AppendAllText(@"C:\Users\JDOe\Documents\Users.txt", "Date :" +
                         DateTime.Now.ToString() + "\t" + member["DisplayName"] + "\t" + member["ADGroup"] + "\t" + member["ADPath"] + Environment.NewLine);
                            }
                        }
                    }
                }
            }
                private static string GetConnectionString()
              
                {
                    return "Data Source=abc1234; " +
                        " Integrated Security=true;" +
                        "Initial Catalog=Purchase;";
                }
                static DataTable GetGroupMembers(PrincipalContext context, string groupName,string cube)
                {
                    var table = new DataTable();
                    table.Columns.Add("UserName", typeof(string));
                    table.Columns.Add("DisplayName", typeof(string));
                    table.Columns.Add("Email", typeof(string));
                    table.Columns.Add("ADGroup", typeof(string));
                    table.Columns.Add("ADPath", typeof(string));
                table.Columns.Add("CubeName", typeof(string));
                var group = GroupPrincipal.FindByIdentity(context, groupName);
                    if (group != null)

                        GetGroupMembersCore(table, group, group.Name, groupName,cube);

                    return table;

                }

                static void GetGroupMembersCore(DataTable dt, GroupPrincipal root, string parentPath, string groupname,string cube)
                {
                    var members = root.GetMembers();

                    //Dump the users first
                    foreach (var member in members.OfType<UserPrincipal>())
                    {
                        dt.Rows.Add(member.SamAccountName, member.DisplayName, member.EmailAddress, groupname, parentPath,cube);
                    }

                    //Enumerate the groups
                    foreach (var member in members.OfType<GroupPrincipal>())
                    {
                        GetGroupMembersCore(dt, member, $"{parentPath}/{member.Name}", groupname,cube);

                    }
                }


    Thursday, November 22, 2018 2:17 AM
  • Hi msdnpublic,

    Since the issue is a new issue, I would suggest that you could post a new thread, it will be beneficial to other communities who have the similar issue.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 22, 2018 2:20 AM
    Moderator
  • Yes, I mentioned that dups are possible. A single user can be in multiple groups. You need to decide what makes a membership "unique". Then filter out the dups. You could do that after you get the DataTable back via LINQ and the Distinct method. Alternatively check for the existing user before you add them to the DataTable to begin with. 

    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, November 22, 2018 3:53 AM
    Moderator
  • I see that every row repeats and count jumps to exactly double.I am aware that a member will be part of multiple groups and will repeat,but here I see that the entire row repeats itself.I dont know why  every row would repeat.
    Thursday, November 22, 2018 4:12 AM