Add All Users From OU to Group Excel

[LBMCreation]

Hi All, I have been tasked at work to build a script which can do the following.

1. Add all users from a OU to a group i.e. ou=user1 to group1 etc
2. Delete users from group1 if no longer in ou=user1
3. This has to be done on a input file basis for example:

Excel: HeaderA1(Users OU) | HeaderB1(Group CN)
OU=Users,DC=blah,DC=com CN=Group1,DC=blah,DC=com

I do currently have a script which adds all users from a OU to a group, but the problem is their are going to be loads of groups which need populating by different OU's. It would be would better to be able to just add a line to the excel doc to specify a new users to group instruction.

=================================================================

My script is:

OPTION EXPLICIT

dim strFilter, strRoot, strScope, strGroupName
dim strNETBIOSDomain, strGroupDN
dim cmd, rs,cn, objGroup

strFilter = "(&(objectCategory=person)(objectClass=user))"
strScope = "subtree"

strRoot = "OU=Users,DC=blah,DC=com"

strNETBIOSDomain = "LBMSLN"

strGroupName = "Group1"

strGroupDN = GetDN(strNETBIOSDomain,strGroupName)

set objGroup = getobject("LDAP://" & strGroupDN)
set cmd = createobject("ADODB.Command")
set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")

cn.open "Provider=ADsDSOObject;"
cmd.activeconnection = cn

cmd.commandtext = "<LDAP://" & strRoot & ">;" & strFilter & ";ADsPath,sAMAccountName;" & strScope
cmd.properties("page size")=1000

set rs = cmd.execute
while rs.eof <> true and rs.bof <> true

on error resume next

objGroup.Add rs("ADsPath")

if err.number = -2147019886 then

elseif err.number <> 0 then

else

end if
err.clear

on error goto 0

rs.movenext
wend

cn.close

Function GetDN(byval strDomain,strObject)
DIM objTrans

Set objTrans = CreateObject("NameTranslate")
objTrans.Init 1, strDomain
objTrans.Set 3, strDomain & "\" & strObject
GetDN = objTrans.Get(1)

end function

================================================================

I also have a delete script which basically is the same but reversed

This script is going to be run at midnight each night unattended.

Hope you all can help with this.

Many Thanks

 

[LBMCreation]

Just had a thought but dont know how i would do it

Get the ((( strRoot = "OU=Users,DC=blah,DC=com" ))) Value from Cell "A1" in excel

and

Get the ((( strGroupName = "Group1" ))) Value from Cell "B1"

after that has processed the script would go back to the start but use

A2 & B2 etc until it reaches "" as the vaule and then stops.

Also think this might work for the delete script.


Can anybody show me how I would do this to my script

Cheers

 

[markdmac]

Give this a try:

In an open spreadsheet have the OU in column A and the group name in column B. Column D&E will get status info

On Error Resume Next
Const ADS_GROUP_TYPE_GLOBAL_GROUP = &h2
Const ADS_GROUP_TYPE_SECURITY_ENABLED = &h80000000
Const ADS_PROPERTY_APPEND = 3

Set objExcel = GetObject(,"Excel.Application")
r = 2
objExcel.Cells(1,4).value = "Creation Status"
objExcel.Cells(1,5).value = "Join User Status"

do until len(objExcel.cells(r, 1).value) = 0
	
	Set objOU = GetObject("LDAP://" & objExcel.Cells(r,1).value)
	Set objGroup = objOU.Create("Group", "CN=" & objExcel.Cells(r,2).value)
	objGroup.Put "sAMAccountName", objExcel.Cells(r,2).value
	objGroup.Put "groupType", ADS_GROUP_TYPE_GLOBAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
	objGroup.SetInfo

	If Err.Number <> 0 Then
		If Err.number = -2147019886 Then
			objExcel.cells(r, 4).value = "Error Creating Group: Group Already Exists "
			Err.Clear
		Else
			objExcel.cells(r, 4).value = "Error Creating Group " & Err.Number & " " & Err.Description
			Err.Clear
		End If
	Else
		objExcel.cells(r, 4).value = "Group Created Successfully"
	End If
	Set objGroup = Nothing
	WScript.Sleep 5000
	'On Error GoTo 0
' Now join the users to the group
	'Enumerate the users in the OU
	strOU = objExcel.cells(r,1).value

	' Create connection to AD
	
	Set objConnection = CreateObject("ADODB.Connection")
	objConnection.Open "Provider=ADsDSOObject;"
	
	' Create command
	'
	Set objCommand = CreateObject("ADODB.Command")
	objCommand.ActiveConnection = objConnection
	objCommand.Properties("Page Size") = 1000
	
	' Execute command to get all users in OU
	objCommand.CommandText = _
	  "<LDAP://" & strOU & ">;" & _
	  "(&(objectclass=user)(objectcategory=person));" & _
	  "adspath,distinguishedname,sAMAccountName;"
	Set objRecordSet = objCommand.Execute
   		Set objGroup = GetObject _
		    ("LDAP://" & "CN=" & Trim(objExcel.Cells(r,2).value) & "," & Trim(objExcel.Cells(r,1).value))

	Do Until objRecordSet.EOF
		Err.Clear
  		UserLDAP = objRecordSet.Fields("distinguishedname").Value
			objGroup.PutEx ADS_PROPERTY_APPEND, _
		    "member", Array(UserLDAP)

		'Check for success or failure and add to our report
		If Err.Number <> 0 Then 
			Report = objExcel.Cells(r,5).Value & "Error joining " & UserLDAP & "  "
			Err.Clear
		Else
			Report = objExcel.Cells(r,5).Value & "Success joining " & UserLDAP & "  "
			Err.Clear
		End If
		
		
	
		objExcel.Cells(r,5).Value = Report
		Report = ""

		objrecordset.MoveNext
	Loop
	r = r + 1
	objGroup.SetInfo
	Set objGroup = Nothing
	Set objOU = Nothing
Loop

I hope that helps.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.