Создание пользователей Exchange в Excel (Часть 1)

В предыдущих статьях мы обсуждали утилиты, которые создают пользователей и их почтовые ящики в Active Directory. Упоминали LDIFDE и VBScript. Но все равно самым мощным и гибким, на мой взгляд, инструментом является Excel. Excel может читать информацию в различных форматах и может служить очень удобным связующим звеном. Вы можете делать, что хотите с информацией в Excel, а затем вы можете экспортировать ее в Active Directory. Огромное количество данных экспортируется из Excel, который является самым распространенным инструментом для работы с большими таблицами. Вы также можете объединять в Excel данные, полученные из разных источников.

Если вы хотите прочесть вторую часть этой статьи, пожалуйста, воспользуйтесь ссылкой:

Создание пользователей Exchange в Excel (Часть 2)

Excel как и все продукты семейства MS Office поддерживают VBA, язык, являющий собой нечто среднее между VBScript и Visual Basic 6, который позволяет объектам офиса получать доступ к любой API функции, используя соответствующие DLL. Если вы не особо знакомы с языками программирования, то это значит что, поняв VBA, вы сможете сделать на нем все что угодно.

В отличие от VBScript, VBA – это встроенный рантайм (runtime) компилятор, который пробегает макросы (так в VBA называется скрипт), также как это делает и Visual Basic. VBA – это не совсем бесплатное средство. Он встроен в любое Office приложение, так что вам не придется инсталлировать больше никаких средств разработки, таких как Visual Studio. Эта особенность делает VBA идеальной средой разработки скриптов. Несколько раз я даже пытался перенести более сложный VBScript код на Excel или Word, только для того чтобы избавиться от неизбежных ошибок в программирование.

Подготовка

Прежде всего нужно открыть список пользователей, так как это показано ниже.

Мой список пользователей не включает описаний, но если бы они были, они тоже были бы доступны.

Рисунок 1

Современные Office приложения выходят с Макро (Macro) защитой, которая как предполагается, защитит вас от вирусов и иного вредоносного программного обеспечения. Прежде чем начать свою работу, вам необходимо убедиться, что ваши антивирусы обновлены. После это вы можете отключить Макро защиту.

Рисунок 2

Рисунок 3

Если вы очень боитесь макро-вирусов, то когда вы закончите написание своего макроса, вы сможете установить те настройки вашей среды, которые вам нравятся.

Вы должны выбрать, какую из двух сред разработки VBA вы будете использовать. В этой статье мы будем использовать Visual Basic Editor (VBE), который доступен при нажатии Alt + F11. Огромным достоинством VBA является то, что он может на основе ваших действий в Excel создать настоящий VBA макрос, который вы потом сможете использовать. Это значит, что вместо того чтобы покупать книги или лазить по сайтам, в Internet в надежде узнать, как же написать макрос, вы можете просто создать макрос. Для этого вам нужно выполнять обычные действия в Excel, нажимать кнопки и вводить значения. Все это конечно, если вы не знаете, как написать макрос кодом VBA.

Так как наш макрос будет иметь дело с Active Directory и Exchange, то нам нужно сообщить VBA, что ему потребуются API для работы c Exchange и Active Directory. Это делается таким же образом, как и в Visual Basic 6. Для этого выберите в меню VBE Tools > References.

Рисунок 4

Выберите библиотеки Exchange и ADSI, тем самым вы дадите VBA доступ к их API.

Если же вы не видите этих библиотек в окне, то проблема может быть в том, что ваш Excel запущен на рабочей станции или сервере, на котором не установлен Exchange System Manager. Вам следует установить последнюю версию ESM и Exchange сервис пак прежде чем приступать к работе с макросами.

Специально для этой статьи я создал OU и назвал ‘Test’, использующую пользователей из Active Directory и консоль Computers. А также в ней есть две группы, обозначающие разные отделы – dept1 и dept2.

Рисунок 5

Макрос

Наш макрос очень похож на те, что используются в Visual Basic.

Вот его полный скрипт. Он берет информацию, которую вводят в таблицу Excel, и преобразует ее в пользователей Exchange. Вы можете использовать его в своей собственной системе, не обладая специальными знаниями в области программирования.


Sub CreateUsers()
Dim Row As Integer
Dim oMailbox As CDOEXM.IMailboxStore
Dim oUser As IADsUser
Set rootDSE = GetObject(LDAP://RootDSE)
DomainContainer = rootDSE.Get("defaultNamingContext")
Set oOU = GetObject(LDAP://OU=Test,DC=mycompany,DC=local)
Row = 1
Do Until Cells(Row, 1) = Empty
    gname = Trim(Cells(Row, 1).Value)
    sname = Trim(Cells(Row, 2).Value)
    ID = Cells(Row, 3).Value
    mailingaddress = Cells(Row, 4).Value
    city = Cells(Row, 5).Value
    postalcode = Cells(Row, 6).Value
    homephone = Cells(Row, 7).Value
    cellular = Cells(Row, 8).Value
    dept = Trim(Cells(Row, 9).Value)
        FullName = gname & " " & sname
AliasCount = 2
    Alias = LCase(gname & Left(sname, AliasCount))
Set conn = CreateObject("ADODB.Connection")
    conn.Provider = "ADSDSOObject"
    conn.Open "ADs Provider"
ldapStr = ";(&(objectCategory=user)
 (mailNickname=" & Alias & "));adspath;subtree"
Set rs = conn.Execute(ldapStr)
While rs.RecordCount > 0
      AliasCount = AliasCount + 1
      Alias = LCase(gname & Left(sname, AliasCount))
      ldapStr = ";(&(objectCategory=user)
 (mailNickname=" & Alias & "));adspath;subtree"
      Set rs = conn.Execute(ldapStr)
Wend
    ' Update User Record
    Set oUser = oOU.Create("user", "cn=" & FullName)
    oUser.Put "cn", FullName
    oUser.Put "SamAccountName", Alias
    oUser.Put "userPrincipalName", Alias & "@mycompany.local"
    oUser.Put "givenName", gname
    oUser.Put "sn", sname
    oUser.Put "description", ID
    oUser.SetInfo
    oUser.GetInfo
' Enable Account
    oUser.AccountDisabled = False
    ' Set Pwd to be same as 123456
    oUser.SetPassword ("123456")
    'Account is not disabled
    oUser.AccountDisabled = False
    ' User must change password at next Logon
    oUser.Put "pwdLastSet", CLng(0)
oUser.SetInfo
Set oMailbox = oUser
    MDBName = "Mailbox Store (EXCHANGE)"
    StorageGroup = "First Storage Group"
    Server = "Exchange"
    AdminGroup = "MyCompany"
    Organization = "MyCompany School of Arts"
    DomainDN = "DC=mycompany,DC=local"
oMailbox.CreateMailbox "LDAP://CN=" & MDBName & _
                                   ",CN=" & StorageGroup & _
                                   ",CN=InformationStore" & _
                                   ",CN=" & Server & _
                                   ",CN=Servers" & _
                                   ",CN=" & AdminGroup & _
                                   ",CN=Administrative Groups" & _
                                   ",CN=" & Organization & _
                                   ",CN=Microsoft Exchange,CN=Services" & _
                                   ",CN=Configuration," & DomainDN
oUser.SetInfo
StrobjGroup1 = "LDAP://CN=" & dept & ",OU=Test,DC=mycompany,DC=local"
    Set objGroup1 = GetObject(StrobjGroup1)
    objGroup1.Add (oUser.ADsPath)
Set oUser = Nothing
    Row = Row + 1
Loop
End Sub

Как работает скрипт

Прежде всего, вам нужно определить объекты. Объекты VBA определяются также как в VB6. Это значит, что вы можете также использовать и неопределенные объекты, но это не очень хорошо, когда вы работаете со сложными объектами.

В этом макросе мы определяем объект почтового ящика (oMailbox) и объект пользователя (IADsUser).


Dim Row As Integer
Dim oMailbox As CDOEXM.IMailboxStore
Dim oUser As IADsUser

Как я упоминал в моих предыдущих статьях, существует множество способов создания пользователей и их почтовых ящиков. Самый распространенный – создание пользователя с использованием ADSI и потом заменить его почтовым ящиком Exchange. А также можно создать почтовый ящик, используя CDOEXM. Большим плюсом объявления объектов является то, что вы всегда знаете, что каждый объект из себя представляет. Вы, конечно, можете предоставить компилятору самому решать это за вас, но это иногда может приводить к неприятным неожиданностям.

Следующая часть кода достает из Active Directory контейнер доменов, а затем и OU, в который мы хотим поместить пользователя.


Set rootDSE = GetObject(LDAP://RootDSE)
DomainContainer = rootDSE.Get("defaultNamingContext")
Set oOU = GetObject(LDAP://OU=Test,DC=mycompany,DC=local)

Теперь взглянем на следующую строчку кода:


Row = 1
Do Until Cells(Row, 1) = Empty
    gname = Trim(Cells(Row, 1).Value)
    sname = Trim(Cells(Row, 2).Value)
    ID = Cells(Row, 3).Value
    mailingaddress = Cells(Row, 4).Value
    city = Cells(Row, 5).Value
    postalcode = Cells(Row, 6).Value
    homephone = Cells(Row, 7).Value
    cellular = Cells(Row, 8).Value
    dept = Trim(Cells(Row, 9).Value)
    FullName = gname & " " & sname
    AliasCount = 2
    Alias = LCase(gname & Left(sname, AliasCount))

Мы используем цикл “Do” для того, чтобы пробегать по таблице Excel до тех пор, пока не обнаружим пустую строку. Функция Cell в VBA используется для извлечения информации из ячейки таблицы Excel.

Например, в "gname" мы сохраняем данное пользователю имя (given name), которое берем из первой ячейки каждой строчки. Я использую trim потому что иногда информация вводится в ручную, и в ячейках попадаются пустые промежутки. Позже, когда мы будем создавать имя пользователя (username) и псевдоним (alias), это может вызвать проблемы.

Псевдоним пользователя мы получаем из данного пользователю имени и первых двух букв его фамилии. Их мы получаем с помощью функции Left. Это достаточно распространенный метод создания псевдонимов и многие организации его используют для создания псевдонимов и имен пользователя, которые должны быть короткими, но в тоже время уникальными. Но если вдруг у вас есть 2 пользователя с одинаковым данным именем и у них первые буквы в фамилии совпадают, то вы конечно должны добавить еще буквы к их имени. Следующая строчка кода показывает, как это можно сделать.

   Set conn = CreateObject("ADODB.Connection")
    conn.Provider = "ADSDSOObject"
    conn.Open "ADs Provider"
    ldapStr = ";(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
    Set rs = conn.Execute(ldapStr)
    While rs.RecordCount > 0
      AliasCount = AliasCount + 1
      Alias = LCase(gname & Left(sname, AliasCount))
      ldapStr = ";(&(objectCategory=user)(mailNickname=" & Alias & "));adspath;subtree"
     Set rs = conn.Execute(ldapStr)
    Wend

Наш алгоритм запрашивает Active Directory на наличие пользователей с одинаковыми псевдонимами. И если вдруг такие пользователи есть, то переменная AliasCount увеличивается на одну или несколько букв.

Конечно же, этот код не совершенен. Например, он не будет работать, если у вас есть 2 пользователя с одинаковыми именами и фамилиями. Если хотите, вы можете улучшить макрос, добавив к псевдониму цифру вместо букв.

После того, как создали поле с псевдонимом, можно создать пользователя в Active Directory. Заметьте, что поля Псевдоним и имя пользователя (SamAccountName) имеют одинаковое значение. Поле с описанием используется для того, чтобы ввести цифровой ID для каждого пользователя, например, туда можно вводить номер полиса социального страхования или какой-нибудь другой номер.


Set oUser = oOU.Create("user", "cn=" & FullName)
oUser.Put "cn", FullName
oUser.Put "SamAccountName", Alias
oUser.Put "userPrincipalName", Alias & "@mycompany.local"
oUser.Put "givenName", gname
oUser.Put "sn", sname
oUser.Put "description", ID

Пользователь будет создан с общим паролем “123456”, который он потом должен будет поменять, после того как зайдет в систему. В организациях, где вопросу безопасности уделяется больше внимания, можно прибегнуть к генерации различных паролей.


oUser.AccountDisabled = False
oUser.SetPassword ("123456")
oUser.AccountDisabled = False
oUser.Put "pwdLastSet", CLng(0)
oUser.SetInfo

Теперь, будучи уже экспертами в написание скрипта для Exchange, мы выполним переход от AD объекта к почтовому ящику Exchange (Exchange Mailbox) и создадим почтовый ящик.


Set oMailbox = oUser
MDBName = "Mailbox Store (EXCHANGE)"
StorageGroup = "First Storage Group"
Server = "Exchange"
AdminGroup = "MyCompany"
Organization = "MyCompanyOrg
DomainDN = "DC=mycompany,DC=local"
oMailbox.CreateMailbox "LDAP://CN=" & MDBName & _
                                   ",CN=" & StorageGroup & _
                                   ",CN=InformationStore" & _
                                   ",CN=" & Server & _
                                   ",CN=Servers" & _
                                   ",CN=" & AdminGroup & _
                                   ",CN=Administrative Groups" & _
                                   ",CN=" & Organization & _
                                   ",CN=Microsoft Exchange,CN=Services" & _
                                   ",CN=Configuration," & DomainDN
oUser.SetInfo

Для того чтобы использовать скрипт, вам потребуется доработать некоторые поля, чтобы он подходил к вашему серверу Exchange. Также обратите внимание, что некоторые поля доступны только после того, как почтовый ящик в Exchange уже создан. А это может занять некоторое время, так как RUS должен обработать его. Например, если вы хотите использовать одну из дополнительных возможностей Exchange для сохранения ID пользователя (User’s ID) вместо описания, то вам надо написать скрипт для активации RUS или написать другой скрипт и запустить его через несколько минут после того, как был запущен первый.

Оставшийся код добавляет пользователя в группу, которая была указана в таблице Excel, а затем переводит счетчик Row на следующий ряд. После этого цикл повторяется снова.


    StrobjGroup1 = "LDAP://CN=" & dept & ",OU=Test,DC=mycompany,DC=local"
    Set objGroup1 = GetObject(StrobjGroup1)
    objGroup1.Add (oUser.ADsPath)
    Set oUser = Nothing
    Row = Row + 1
Loop
End Sub

Заключение

Всего несколько строк кода в VBA Excel могут сохранить вам несколько часов работы, которые вы потратите на ввод пользователей вручную. Главная особенность скрипта – его гибкость. Любое поле может быть назначено исходя из логики, которую вы выберите. Конечно, макрос, показанный в этой статье очень простой, но его можно доработать, и он будет подходить для решения очень сложных задач. Вы можете синхронизировать две системы, расставить разрешения и много что еще, что вам возможно потребуется.