?
Solved

Get the list of groups and subgroups hierarchy of certain OU

Posted on 2009-12-22
15
Medium Priority
?
745 Views
Last Modified: 2012-05-08
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
Comment
Question by:Hadush
  • 9
  • 6
15 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 26115834
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
 
LVL 8

Author Comment

by:Hadush
ID: 26115924
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
 
LVL 8

Author Comment

by:Hadush
ID: 26115945
Group1 does have a user object too .
The sample out put could be
Group1                        Group2                           Group3                                      Users
IT                                                                                                                      
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:RobSampson
ID: 26115953
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
 
LVL 8

Author Comment

by:Hadush
ID: 26115962

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
 
LVL 8

Author Comment

by:Hadush
ID: 26116114
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 26116477
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
 
LVL 8

Author Comment

by:Hadush
ID: 26116661
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
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 26116681
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
 
LVL 8

Author Comment

by:Hadush
ID: 26116812
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
 
LVL 8

Author Closing Comment

by:Hadush
ID: 31669122
Great solution! I am sure others will use it too.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 26117314
>> 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
 
LVL 8

Author Comment

by:Hadush
ID: 26117651
Same to you! I wonder how that could be done. That would be additional chirstmas gift.
0
 
LVL 8

Author Comment

by:Hadush
ID: 26120078
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 26134642
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question