Re: mass AD global groups creation from Excel sheet

"primoz88" <> wrote in message
news...
>
> Hello Everyone,
>
> I would be really grateful, if you could help me out with the below
> issue.
>
> I need to create mass (200) AD global security groups. I was wondering
> if there is any way to create those groups using script importing data
> from excel sheet.
>
> I would like to provide in Excel sheet some defined values e.g. group
> name, description, managed by and notes.
>
>
> Many thanks in advance for your assistance.
> Any hints will be appreciated.
>

It's fairly easy to create groups in a VBScript program. To create one group
the steps would be:
==========
Option Explicit
Dim strName, strParentDN, objParent, objGroup

Const ADS_GROUP_TYPE_GLOBAL_GROUP = &H02
Const ADS_GROUP_TYPE_LOCAL_GROUP = &H04
Const ADS_GROUP_TYPE_UNIVERSAL_GROUP = &H08
Const ADS_GROUP_TYPE_SECURITY_ENABLED = &H80000000

' Specify the name of the group.
strName = "Test Group"

' Specify the DN if the parent OU/container.
strParentDN = "ou=West,dc=MyDomain,dc=com"

' Bind to the parent OU/container.
Set objParent = GetObject("LDAP://" & strParentDN)

' Create the group.
Set objGroup = objParent.Create("group", "cn=" & strName)
' Assign NetBIOS name of group (NT or pre-Windows 2000 name).
objGroup.Put "sAMAccountName", strName
' Assign the group type.
objGroup.Put "groupType", ADS_GROUP_TYPE_GLOBAL_GROUP Or _
ADS_GROUP_TYPE_SECURITY_ENABLED
' Save changes.
objGroup.SetInfo
=========
A script can also be written to read information from an excel spreadsheet.
For each group (row) the spreadsheet will need to specify:

1. The DN of the parent OU/Container (unless the same for all, in which case
the DN can be hard coded).
2. Name of the group (assume Common Name and NetBIOS names are the same).
3. Group type (unless all are the same), either "global", "local", or
"universal".
4. If the group is a "security" group or "distribution" group.

Assuming the first row of the spreadsheet is column headers, the first
column is the DN of the parent OU/Container, the second column is the group
name (Common and NetBIOS names), the third column is the group type, and the
fourth column indicates if this is a security group, the code could be
similar to below:
=============
Option Explicit
Dim strExcelPath, objExcel, objSheet, strPrevious
Dim strName, strParentDN, objParent, objGroup
Dim strGroupType, strSecurity, lngType

Const ADS_GROUP_TYPE_GLOBAL_GROUP = &H02
Const ADS_GROUP_TYPE_LOCAL_GROUP = &H04
Const ADS_GROUP_TYPE_UNIVERSAL_GROUP = &H08
Const ADS_GROUP_TYPE_SECURITY_ENABLED = &H80000000

' Specify spreadsheet file.
strExcelPath = "c:\scripts\Groups.xls"

' Open the spreadsheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Skip the first row. Iterate through the remaining rows of the spreadsheet,
' until the first blank entry in the first column.
intRow = 2
' Keep track of previous OU/Container.
' We only need to bind if the OU/Container is different.
strPrevious = ""
Do While objSheet.Cells(intRow, 1).Value <> ""
' Retrieve values from spreadsheet.
strParentDN = objSheet.Cells(intRow, 1).Value
strName = objSheet.Cells(intRow, 2).Value
strGroupType = objSheet.Cells(intRow, 3).Value
strSecurity = objSheet.Cells(intRow, 4).Value
Select Case LCase(strGroupType)
Case "global"
lngType = ADS_GROUP_TYPE_GLOBAL_GROUP
Case "local"
lngType = ADS_GROUP_TYPE_LOCAL_GROUP
Case "universal"
lngType = ADS_GROUP_TYPE_UNIVERSAL_GROUP
End Select
If (LCase(strSecurity) = "security") Then
lngType = lngType Or ADS_GROUP_TYPE_SECURITY_ENABLED
End If
If (strParentDN <> strPrevious) Then
' Bind to a different parent OU/Container.
Set objParent = GetObject("LDAP://" & strParentDN)
End If
' Create the group.
Set objGroup = objParent.Create("group", "cn=" & strName)
' Assign NetBIOS name of group.
objGroup.Put "sAMAccountName", strName
' Assign the group type.
objGroup.Put "groupType", lngType
' Save changes.
objGroup.SetInfo
strPrevious = strParentDN
intRow = intRow + 1
Loop

' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--