Запрос к Active Directory из Excel

Достаточно часто системному администратору Windows приходится делать различные выгрузки по информации о пользователях домена Active Directory. Представим, что у нас есть список учетных записей (имена пользователя в формате samAccountName), и нам, например, необходимо получить информацию о том, в какой организации эти пользователи работают и их Canonical Name (CN). Ранее для получения такой информации мне приходилось писать небольшой скрипт на vbs, который последовательно перебирает все записи в Excel и возвращает обратно требуемую информацию из Active Directory. Такой механизм полностью работоспособен, но не очень удобен, поэтому я решил воспользоваться мощью языка Visual Basic from Application и выполнять запросы к Active Directory прямо из Excel (из макроса), т.к. такая методика была бы достаточно универсальной и в принципе все эти скрипты можно со спокойной совестью передавать менее подкованным бухгалтерам и экономистам!

Я создал новый макрос в книге Excel, и создал функцию с именем GetADInfo, которая на входе получает имя поля, по которому осуществляется поиск (в моем случае это имя пользователя, которое хранится в атрибуте Active Directory – samAccountName), значение этого поля (значение ячейки с именем пользователя) и имя артибута AD, значение которого функция должна вернуть.

Как же все это работает? В моем примере в ячейке A2 содержится имя пользователя домена, и я хочу для этого пользователя узнать компанию, в которой он числится (поле AD “Company”) и его каноническое имя (поле AD «distinguishedName»), в этом случае формулы для ячеек соответственно будут выглядеть следующим образом:

Ячейка B2 (наименование организации):

=GetADInfo("samAccountName";A2; "Company")

Ячейка C2 (CN):

=GetADInfo("samAccountName";A2; "distinguishedName")

Код макроса на VBA для получения данных из AD следующий:

Function GetADInfo(ByVal SearchField, ByVal SearchString, ByVal ReturnField) ' Указываем имя домена ("dc=domain, dc=local") Dim adoCommand, strDomain, objConnection strDomain = "dc=remontka,dc=ru" Set objConnection = CreateObject("ADODB.Connection") objConnection.Open "Provider=ADsDSOObject;" ' Подключаемся Set adoCommand = CreateObject("ADODB.Command") adoCommand.ActiveConnection = objConnection ' Рекурсивный поиск по AD, начиная с корня домена adoCommand.CommandText = _ "<LDAP://" & strDomain & ">;(&(objectCategory=" & "User" & ")" & _ "(" & SearchField & "=" & SearchString & "));" & SearchField & "," & ReturnField & ";subtree" ' создаем набор записей RecordSet Dim objRecordSet Set objRecordSet = adoCommand.Execute If objRecordSet.RecordCount = 0 Then GetADInfo = "not found" ' ничего не найдено Else GetADInfo = objRecordSet.Fields(ReturnField) ' возвращаемое значение End If ' Закрываем подключение objConnection.Close ' Очищаем переменные Set objRecordSet = Nothing Set objCommand = Nothing Set objConnection = Nothing End Function

Чтобы данная функция заработала, необходимо подключить ряд библиотек в VBA. В редакторе VBA выбираем меню Tools->References и в появившемся окне отмечаем следующие библиотеки:

• Visual Basic For Application
• Microsoft Excel 14.0 Object Library
• OLE Automation
• Microsoft Office 14.0 Object Library
• Microsoft ActiveX Data Objects 2.8 Library (или около того)
• Microsoft Scripting Runtime
• Microsoft VBScript Regular Expressions 5.5

После того, как вы активируете следующие компоненты, сохраните макрос VBAи книгу Excel, и в результате в соответствующих полях Excel появится информация из Active Directory. Прелесть данного скрипта состоит в том, что он достаточно универсальный и с небольшими модификациями он поможет динамически получать практически любую информацию из Active Directory прямо в книге прямо в книге Excel!