Запрос к Active Directory из Excel | Windows для системных администраторов

Запрос к 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, значение которого функция должна вернуть.

Выполняем запрос к Active Directory из книги Excel

Как же все это работает? В моем примере в ячейке 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=winitpro,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 и в появившемся окне отмечаем следующие библиотеки:

Библиотеки для выполнения запросов к active directory из vba

  • 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!

Еще записи по теме: Active Directory
Понравилась статья? Скажи спасибо и расскажи друзьям!
Назад:
Вперед:

Комментариев: 31

Оставить комментарий
  1. Валерий | 26.09.2011

    Очень интересная статья, но не заработало, однако. Делал так: новая книга-новый макрос-занес скрипт с измененным доменом-отметил библиотеки-для теста в ячейке написал имя пользователя-в другой запрос =GetADInfo(«samAccountName»;A2; «distinguishedName») — ругается на GetADInfo

    Ответить
  2. itpro | 28.09.2011

    Валерий, действительно в коде была небольшая ошибка. Я обновил код vba модуля
    Проверьте, заработает ли

    Ответить
  3. KAB | 18.11.2011

    спасибо, работает, но почему-то не полностью .. «lastLogon» и аналогичные не работают(
    может подскажете, в чем может быть дело?

    Ответить
  4. KAB | 18.11.2011

    такое очучение, что не понимает большие числа — #ЗНАЧ!

    Ответить
  5. Сергей | 19.07.2012

    Спасибо, скопировал, подправил настройки.
    Работает.

    Ответить
  6. Lynx | 05.09.2012

    Круть! Все работает на ура. С помощью этого скрипта можно просто и быстро получить массово любую информацию из Active Directory
    Нюанс с которым пришлось покопаться — нужно создавать именно новый vba модуль в xls (с классом и текущей формой почему-то не заработало.)

    Ответить
  7. Marat_Best | 22.02.2013

    А как узнать названия всех полей которые можно вытащить ?
    типа Company ?

    Ответить
    • itpro | 22.02.2013

      Все праметры обьектов в AD можно узнать с помощью любого LDAP браузера или с помощью консоли adsiedit.msc

      Ответить
  8. Never | 26.06.2013

    Спасибо Вам большое за код! В своей работе, к сожалению, вынужден пользоваться поиском не по samAccountName, а по DisplayName, что приводит к выпадению полных тезок. Не подскажете как дополнить код, что бы можно было выбрать конкретного человека? Например добавить в excel поле какого по счету брать человека, а в код на каком человеке возращать значение.
    Заранее спасибо

    Ответить
  9. itpro | 08.07.2013

    Не совсем понятно, что Вы хотите, но предполагаю, то что Вы хотите реализовать можно сделать с помощью  дополнительного скрытого столбца с samAccountName и проверки значений ячеек функцией Excel IF (ЕСЛИ)

    Ответить
  10. Sklif | 25.07.2013

    Добрый день! при работе с скриптом был обнаружен следующий нюанс: не возвращает значения атрибутов ни lastLogon, ни lastLogonTimestamp при этом если не находит объект или при работе с другими числовыми или строковыми атрибутами значения выдает корректно, самостоятельно решить данный вопрос не удается :(

    Ответить
    • itpro | 29.07.2013

      К сожалению тоже не удалось победить проблему с возвратом значения lastLogonTimestam. Какая-то ерунда с обработкой timestamp в формате UTC…
      Если получиться найди решение — поделитесь :)

      Ответить
      • Sklif | 02.08.2013

        VBA ошибок не выдает, запустив скрипт отдельно (не из Excel) получил ошибку «Несоответствие типа», выяснил, что lastLogon и ему подобные в формате UTC имеют размер в 64 бита. Проверить работоспособность в 64 разрядном «офисе» с поддержкой типа данных LongLong нет возможности.

        Ответить
      • Sklif | 02.08.2013

        lastLogon и ему подобные в формате UTC имеют размер в 64 бита. Проверить работоспособность скрипта в 64 битном «офисе» с поддержкой типа данных LongLong пока нет возможности.

        Ответить
        • itpro | 05.08.2013

          Видимо проще эти переменные через vbs или dsquery вытягивать…

          Ответить
  11. Денис | 27.09.2013

    Други, помогите! Я использую макрос в Excel для получения большого количества атрибутов по каждому из пользователей для каких-то массовых изменений. Потом делаю загрузку полученной таблицы обратно. Но есть нюанс, я не смог побороть ситуацию, когда значение какого-то атрибута надо отчистить. Т.е., например, у пользователя забрали мобильный телефон и надо это поле очистить. Не получается. Я вышел из ситуации пока глупо: пишу в атрибут пробел. Но это не дело… Пустое значение, типа objUser.Mobile = «» не прокатывает.

    Ответить
    • itpro | 30.09.2013

      Что конкретно пишете в атрибут при очистке его значения? Возможно стоит записывать не просто «», а что-то типа NULL

      Ответить
  12. Денис | 30.09.2013

    itpro, нет, так не прокатит. Почитав ихние мануалы, понял, что просто так писать пустое нельзя. Есть специальная конструкция очистки значения в атрибуте. И узнал немного полезного, например, как удалить одно из значений в многозначимом (можно так писать?) атрибуте. У меня, например, есть такая строчка в VBA: objUser.PutEx ADS_PROPERTY_CLEAR, «homePhone», 0 — использование метода не просто put, а putex, далее параметр, указывающий удаление значения, далее имя атрибута и еще какой-то параметр. Читал, как обычно, не внимательно, но в данный момент для меня важен результат.

    Ответить
  13. sergei | 19.03.2014

    подскажите, как скорректировать скрипт чтобы удалить из АД информацию lastlogon

    Ответить
    • itpro | 19.03.2014

      Скрипт из статьи предназначен в первую очередь для построения различных отчетов и статистике непосредственно из Excel, т.е. для четния данных из AD В вашем случае было бы намного проще копать в сторону Powershell скрипта или WMI.
      А если не секрет какова вообще задача? Почистить следы? :)

      Ответить
  14. Алексей | 19.03.2014

    Подскажите, есть ли возможность с помощью данной функции извлекать все значения атрибута, который представлены в виде многозначных строк? Например, значение атрибута msSFU30PosixMemberOf ?

    Ответить
  15. Александр | 02.12.2014

    Добрый день!
    А у меня почему-то никак не хочет работать…
    В строке Set objRecordSet = adoCommand.Execute выдает ошибку:
    Runtime error ‘-2147217865 (80040e37)’
    Таблица не существует.
    Даже не знаю, куда копать. Очень нужно.

    Ответить
    • itpro | 04.12.2014

      Внимательно перепроверьте список подключаемых модулей и корректность указаная домена в строке strDomain=
      ЗЫ. Скрипт точно работал в 2010 Excel

      Ответить
  16. Александр | 04.12.2014

    Спасибо. Поменял в strDomain «ru» на «local», все заработало

    Ответить
  17. crownn90 | 20.01.2015

    Добрый день!
    Пытаюсь выбрать реквизиты пользователя по ФИО, немного доработав Вашу функцию, но получаю в Excel ошибку ЗНАЧ!
    Уже всю голову сломал, помогите, пожалуйста!
    Вот текст функции:

    Public Function ADGetUserByFIO(ByVal SearchFIO, ByVal ReturnField)
    Dim adoCommand, strDomain, objConnection, objRecordSet, Result, Response

    strDomain = «OU=Users,»
    Set objConnection = CreateObject(«ADODB.Connection»)
    objConnection.Open «Provider = ADsDSOObject»
    Set adoCommand = CreateObject(«ADODB.Command»)
    adoCommand.ActiveConnection = objConnection
    adoCommand.CommandText = _
    «» & _
    «;(&(ObjectClass=user)(objectCategory=Person)(cn=» & SearchFIO & «));» & ReturnField & _
    «;onelevel»
    Set objRecordSet = adoCommand.Execute
    Result = objRecordSet.Fields(ReturnField).Value
    ADGetUserByFIO = Result
    objConnection.Close
    Set objRecordSet = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing
    objConnection.Close
    End Function

    Ответить
    • itpro | 22.01.2015

      Немного не понял задачи.
      В каком атрибуте AD вы храните ФИО?

      Ответить
  18. crownn90 | 20.01.2015

    Часть почему-то не скопировалась:

    Public Function ADGetUserByFIO(ByVal SearchFIO, ByVal ReturnField)

    Dim adoCommand, strDomain, objConnection, objRecordSet, Result
    strDomain = «OU=Users, »
    Set objConnection = CreateObject(«ADODB.Connection»)
    objConnection.Open «Provider = ADsDSOObject»
    Set adoCommand = CreateObject(«ADODB.Command»)
    adoCommand.ActiveConnection = objConnection
    adoCommand.CommandTimeout = 0
    adoCommand.CommandText = _
    «» & _
    «;(&(ObjectClass=user)(objectCategory=Person)(cn=» & SearchFIO & «));» & ReturnField & _
    «;onelevel»
    Set objRecordSet = adoCommand.Execute
    Result = objRecordSet.Fields(«mail»)
    ADGetUserByFIO = Result
    objConnection.Close
    Set objRecordSet = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing
    objConnection.Close
    End Function

    Ответить
  19. Never | 13.02.2015

    Как тут было озвучено ранее, вынужден искать не по samAccountName, а по DisplayName, так же столкнулся с тезками. У ОК есть уникальные идентификаторы, которые прописываем в Description, но поиск только по нему занимает минут по 10 на каждую позицию. Решение было простым и банальным — искать по DisplayName и Description одновременно:

    Function GetADInfo(ByVal SearchField, ByVal SearchField1, ByVal SearchString, ByVal SearchString1, ByVal ReturnField)

    ‘ Указываем имя домена («dc=domain, dc=local»)
    Dim adoCommand, strDomain, objConnection
    strDomain =
    Set objConnection = CreateObject(«ADODB.Connection»)
    objConnection.Open «Provider=ADsDSOObject;»

    Set adoCommand = CreateObject(«ADODB.Command»)
    adoCommand.ActiveConnection = objConnection
    ‘ Рекурсивный поиск по AD, начиная с корня домена
    adoCommand.CommandText = _
    «;(&(objectCategory=» & «User» & «)» & _
    «(» & SearchField1 & «=» & SearchString1 & «)» & _
    «(» & SearchField & «=» & SearchString & «));» & SearchField & «,» & ReturnField & «;subtree»

    Соответственно в Excel функция выглядит:
    =GetADInfo(«DisplayName»;»Description»;A1;B1;»samAccountName»)
    В данном случае ищем по ФИО и идентификатору и возвращаю логин юзера.

    Банально, но может кому-то пригодится.

    2 crownn90, не тем путем идете, мне думается. Вышеприведенная функция ищет по чему угодно, что вы ей подсунете и возвращает что попросит. Менять надо не функцию, а объект запроса.
    DisplayName — Отображаемое имя, чаще всего это именно ФИО
    samAccountName — логин
    Mail — почта
    givenName — имя
    SN — фамилия
    title — должность
    company — компания
    department — отдел
    description — описание
    distinguishedName — локация юзера, тот же ответ возвращает, что и при запросе dsquery user -name c командной строки.
    UserAccountControl — статус учетной записи, возвращает число http://support.microsoft.com/kb/305144/ru

    P.S. Огромное спасибо за код!!! Правда когда таблица на несколько тысяч, то значительно подвисает при каждом изменении. Пришлось перейти от функции к процедуре

    Ответить
    • Жека | 09.03.2016

      как отображать все логины без введения? мне не поиск нужен а отображение в таблице все что есть по отдельным полям

      Ответить
    • жека | 09.03.2016

      как сделать чтобы не нужно было вводить логин а показывало все логи само?

      Ответить
  20. crownn90 | 16.02.2015

    itpro, задача в том, чтобы выбирать через Excel атрибуты пользователя из AD
    Never, разобрался почему не работало. У меня в конце скрипта 2 раза идет objConnection.Close, что и приводит к ошибке при закрытии коннекта второй раз. Excel в этом случае не понимает, что произошло и возвращает ошибку !ЗНАЧ

    Ответить
Полные правила комментирования на сайте winitpro.ru. Вопросы, не связанные с содержимым статьи или ее обсуждением удаляются.

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

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Я не робот( Обязательно отметьте)



MAXCACHE: 0.29MB/0.00103 sec