mass AD global groups creation from Excel sheet

[primoz88]

Hello Everyone,

I would be really grateful, if you could help me out with the belo
issue.

I need to create mass (200) AD global security groups. I was wonderin
if there is any way to create those groups using script importing dat
from excel sheet.

I would like to provide in Excel sheet some defined values e.g. grou
name, description, managed by and notes.


Many thanks in advance for your assistance.
Any hints will be appreciated

--
primoz8
-----------------------------------------------------------------------
primoz88's Profile: http://forums.techarena.in/members/143937.ht
View this thread: http://forums.techarena.in/server-scripting/1258036.ht

http://forums.techarena.i

[Greg Stigers]

There are lots and lots of ways to do this. CSVDE will let you create
groups. See "How to use Csvde to import contacts and user objects into
Active Directory" at <http://support.microsoft.com/kb/327620>.

Another such way comes from an unexpected source: "Performing a Single Phase
Migration" at
<http://technet.microsoft.com/en-us/library/aa997747(EXCHG.65).aspx>. Search
for LDFfromCSV under "Updating Recipient Information Using. .csv Files,
which explains:
The following Excel macro demonstrates how to generate an .ldf file from a
.csv file that contains the proxyAddresses attribute. In this code, it is
assumed that the distinguished name is in the first column of the file and
loops through all remaining header cells to determine the proxyAddresses
column. The result is an entry for each recipient object from the .csv file
to modify the proxyAddresses attribute. This approach can be applied with
minimal modification to other directory attributes, as well.

I will add that the macro can be made to work with multiple columns. And
that you paste said macro code into Excel (how depends on your version of
Excel), with your CSV file open. You might want to save this as its own
spreadsheet, containing this macro code.
______
Greg Stigers, MCSE
remember to vote for the answers you like

[Richard Mueller [MVP]]

Post by primoz88
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