• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 760
  • Last Modified:

Get the list of groups and subgroups hierarchy of certain OU

Exporting to one excel tab and simillar to the following table

OU      Group1      GROUP2                        User
IT      BI      ReportGroup      User 1; user 2;Users3
IT      BI      ETLGroup                     User 1; user 5;Users4
IT      HelpDesk            
0
Hadush
Asked:
Hadush
  • 9
  • 6
1 Solution
 
RobSampsonCommented:
Hi, I'm not sure exactly how you want this report....

You have Group1, Group2, User, but what if there's more than one subgroup, or do you not care about this?

Or, does Group1 never have any user objects, so in the User column you only want to list the members of Group2?

Regards,

Rob.
0
 
HadushAuthor Commented:
Thanks for your response Rob!
I put that as an example but it does go deep and I need to return all the subgroups in thier order.
Group1 -->Group2--->Groups3---->   etc and finally the users list
 
0
 
HadushAuthor Commented:
Group1 does have a user object too .
The sample out put could be
Group1                        Group2                           Group3                                      Users
IT                                                                                                                      
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
RobSampsonCommented:
How would you know that from the users list whether a user was from Group2 or Group3?  Or does that not matter?  Do you just want to list all of the users who are somewhere in the heirarchy from Group1?

Rob.
0
 
HadushAuthor Commented:

Group1 does have a user object too .
The sample out put could be
Group1                        Group2                           Group3                                      Users
IT                                                                                                                               user1, user2
IT                                  BI                                  Reporting                                    user2, users3
IT                                BI                                    ETL Group                                   user1, users4
..                            
0
 
HadushAuthor Commented:
I really don't care. What I care is if the users is ether of the group hierarchy it needs to be listed on the users column.
0
 
RobSampsonCommented:
OK, I've played around with it a bit....a little confusing, but it looks OK.  The users for each group are listed in bold text.

Just change
strOU = "OU=Users,OU=Croydon,OU=Sites,"

to the OU path that you want to enumerate, in reverse order.  For example, the above refers to the OU
domain.com/Sites/Croydon/Users

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2

strOU = "OU=Users,OU=Croydon,OU=Sites,"
If strOU <> "" Then
	If Right(strOU, 1) <> "," Then strOU = strOU & ","
End If

Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.get("defaultNamingContext")

Set objOU = GetObject("LDAP://" & strOU & strDomain)
objOU.Filter = Array("Group")

Set objExcel = CreateObject("Excel.Application")
objExcel.visible = True
Set objWB = objExcel.Workbooks.Add
Set objSheet = objWB.Sheets(1)

intRow = 1
intCol = 2
For Each objMember In objOU
	GetGroups objMember, intRow, intCol
	intRow = intRow + 1
Next

Sub GetGroups(objGroup, intGroupRow, intGroupCol)
	objExcel.Cells(intGroupRow, 1).Value = Mid(Split(strOU, ",")(0), 4)
	objExcel.Cells(intGroupRow, intGroupCol).Value = Mid(objGroup.Name, 4)
	GetMembers objGroup, intGroupRow, intGroupCol
	intNumOfGroups = 0
	For Each objObject In objGroup.Members
		If LCase(objObject.Class) = "group" Then
			intNumOfGroups = intNumOfGroups + 1
			'intGroupCol = intGroupCol + 1
			intGroupRow = intGroupRow + 1
			objExcel.Cells(intGroupRow, 1).Value = Mid(Split(strOU, ",")(0), 4)
			objExcel.Cells(intGroupRow, intGroupCol).Value = Mid(objGroup.Name, 4)
			GetGroups objObject, intGroupRow, intGroupCol + 1
		End If
	Next
End Sub

Sub GetMembers(objGroupUsers, intUserRow, intUserCol)
	For Each objObject In objGroupUsers.Members
		If LCase(objObject.Class) = "user" Then
			objExcel.Cells(intUserRow, intUserCol + 1).Font.Bold = True
			If objExcel.Cells(intUserRow, intUserCol + 1).Value = "" Then
				objExcel.Cells(intUserRow, intUserCol + 1).Value = objObject.CN
			Else
				objExcel.Cells(intUserRow, intUserCol + 1).Value = objExcel.Cells(intUserRow, intUserCol + 1).Value & ";" & objObject.CN
			End If
		End If
	Next
End Sub

Open in new window

0
 
HadushAuthor Commented:
Thanks Rob!
I am doing something stupid :)
I have one OU and I want to enumrate over that lets say my  OU= finance, and my domain is  dc=test, dc=fabricam, dc=com
I am getting an error on line 9 " The directory property can't found in the catch " when I pass these parameters
 
 
0
 
RobSampsonCommented:
Don't worry about the domain bit (unless you are running it on a test domain).

If it's your normal domain, just use

strOU = "OU=Finance,"

but if it's a test domain, change
strDomain = objRootDSE.get("defaultNamingContext")

to this
strDomain = "DC=Test,DC=fabricam,DC=com"

Regards,

Rob.
0
 
HadushAuthor Commented:
Thanks again Rob. It is working now except the users are not on the same column. It goes upto six level deep and the 7th column would be my users list.
 
 
0
 
HadushAuthor Commented:
Great solution! I am sure others will use it too.
0
 
RobSampsonCommented:
>> except the users are not on the same column

Hi, my reason for this was because I couldn't be sure how many nested groups you would have, so that's why I made the users bold, to make it more readable.  The only way to put all the users in the same column would be to go through the list twice in some fashion.  It could be done though, if you require that.

Thanks for the grade.  Merry Christmas.

Regards,

Rob.
0
 
HadushAuthor Commented:
Same to you! I wonder how that could be done. That would be additional chirstmas gift.
0
 
HadushAuthor Commented:
Hi Rob,
This could be another tread, what if I want to have a function in excel that passes list of group names from column1 and get the memebers of that group in another column separated by semicolon.
0
 
RobSampsonCommented:
Hi, I've reposted the code from this question into your new question, which is changed to do the same thing, but put the users all in the last column instead.

Regards,

Rob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now