Выгрузка пользователей из ad в excel
В комментариях к предыдущей статье вспомнили про учет в Excel вместо 1С. Что ж, проверим, насколько вы знаете Excel. Сегодня я покажу, как получать данные из Active Directory и работать с ними без макросов и PowerShell — только штатными механизмами Office. Например, можно запросто получить аналитику по использованию операционных систем в организации, если у вас еще нет чего-либо вроде Microsoft SCOM. Ну, или просто размяться и отвлечься от скриптов.
Конечно, получить данные как в примерах ниже можно буквально одной строчкой на PowerShell. Но, во-первых, PowerShell — это слишком скучно, а во-вторых, Excel умеет динамически обновлять данные ― получившиеся документы можно опубликовать в сети и забыть про их актуализацию.
Для работы с данными я буду использовать механизм Power Query. Для офиса 2010 и 2013 придется устанавливать плагин, в Microsoft Office 2016 этот модуль уже встроен. К сожалению, стандартной редакции нам не хватит, понадобится Professional.
Сам механизм предназначен для получения и обработки данных из самых разных источников ― от старого ODBC и текстовых файлов, до Exchange, Oracle и Facebook. Подробнее о механизме и встроенном скриптовом языке «M» уже писали на Хабре, я же разберу пару примеров использования Power Query для получения данных из Active Directory.
Сам запрос к базе домена создается на вкладке «Данные ― Новый запрос ― Из других источников ― Из Active Directory».
Указываем источник данных.
Понадобится выбрать название домена, указать необходимые данные для подключения. Далее выберем тип объектов, в этом примере ― user. Справа в окне предпросмотра запрос уже выполняется, показывая предварительный вид данных.
Подготавливаем запрос, любуемся предпросмотром.
Предварительно запрос стоит подготовить, нажав кнопку «изменить» и выбрав нужные колонки. По сути эти колонки ― это классы Каждый из них содержит набор определенных атрибутов объекта Active Directory, кроме основной колонки displayName, которая сама является атрибутом. Я остановлюсь на классах user, person, top и securityPrincipal. Теперь необходимо выбрать нужные атрибуты из каждого класса с помощью «расширения» ― значок с двумя стрелочками у заголовка колонки:
Расширяем запрос.
Теперь настроим фильтр: в частности, чтобы не получить заблокированные аккаунты, нужно чтобы атрибут userAccountControl имел значение 512 или 66048. Фильтр может быть другой в вашем окружении. Подробнее про атрибут можно прочитать в документации Microsoft.
Применяем фильтр.
Иногда Excel неверно определяет формат данных, особенно значения атрибута lastLogonTimestamp. Если вдруг постигла такая беда, на вкладке «Преобразовать» можно выставить верный формат.
Теперь столбец userAccountControl стоит удалить ― в отображении он не нужен совершенно. И нажимаем «Загрузить и закрыть».
Получилась табличка, которую осталось совсем немного довести до ума. Например, переименовать столбцы в что-то удобочитаемое. И настроить автоматическое обновление данных.
Автоматическое обновление при открытии таблицы или по таймауту настраивается во вкладке «Данные» в «Свойствах».
Настройка обновления данных.
После того, как настройка обновления будет завершена, можно смело отдавать таблицу сотрудникам отдела персонала или службе безопасности ― пусть знают, кто и когда входил в систему.
Другой вариант использования Excel в связке с Active Directory ― это формирование адресной книги, исходя из данных AD. Понятно, что адресная книга получится актуальной, только если в домене порядок.
Создадим запрос по объекту user, развернем класс user в mail, а класс person в telephoneNumber. Удалим все столбцы, кроме distinguishedName ― структура домена повторяет структуру предприятия, поэтому названия Organizational Units соответствуют названиям подразделений. Аналогично в качестве основы названий подразделений можно использовать и группы безопасности.
Теперь из строки CN=Имя Пользователя, OU=Отдел Бухгалтерии, OU=Подразделения, DC=domain, DC=ru нужно извлечь непосредственно название отдела. Проще всего это сделать с использованием разделителей на вкладке «Преобразование».
Извлекаем текст.
В качестве разделителей я использую OU= и ,OU=. В принципе, достаточно и запятой, но я перестраховываюсь.
Вводим разделители.
Теперь с помощью фильтра можно отсечь ненужные OU, вроде заблокированных пользователей и Builtin, настроить сортировку и загрузить данные в таблицу.
Вид итоговой таблицы.
Теперь попробуем создать полезную таблицу, получив данные по компьютерам. Сделаем отчет по используемым компанией операционным системам: для этого создадим запрос, но в навигаторе на этот раз выберем computer.
Делаем запрос по объекту computer.
Оставим классы-колонки computer и top и расширим их:
Расширенный запрос.
При желании можно сделать отчет только по серверным операционным системам. Например, применить фильтр по атрибуту operatingSystem или operatingSystemVersion. Я не буду этого делать, но поправлю отображение времени создания ― мне интересен только год. Для этого на вкладке «Преобразование» выберем нужную нам колонку и в меню «Дата» выберем «Год».
Извлекаем год из времени ввода компьютера в домен.
Теперь останется удалить столбец displayname за ненадобностью и загрузить результат. Данные готовы. Теперь можно работать с ними, как с обычной таблицей. Для начала сделаем сводную таблицу на вкладке «Вставка» ― «Сводная таблица». Согласимся с выбором источника данных и настроим ее поля.
Настройки полей сводной таблицы.
Теперь остается настроить по вкусу дизайн и любоваться итогом:
Сводная таблица по компьютерам в AD.
При желании можно добавить сводный график, также на вкладке «Вставка». В «Категории» (или в «Ряды», по вкусу) добавим operatingSystem, в данные ― cn. На вкладке «Конструктор» можно выбрать тип диаграммы по душе, я предпочел круговую.
Круговая диаграмма.
Теперь наглядно видно, что, несмотря на идущее обновление, общее количество рабочих станций с Windows XP и серверов с Windows 2003 довольно велико. И есть к чему стремиться.
Надо отметить, что Excel умеет составлять не только любимые бухгалтерией таблички. При умелом подходе ему по плечу и аналитика многомерных данных (OLAP-кубы), и решение системы уравнений с помощью матриц. А для тех, у кого на стенке пылится сертификат от Microsoft – есть вариант заморочиться даже с 3D-играми. Не Doom конечно, но вечер точно займет.
А что вы думаете про Excel как инструмент администратора? Доводилось использовать что-то из описанного?
Get-ADUser команда, которая возвращает список пользователей AD в Powershell. На самом деле командлет мало чем отличается от тех, которые мы рассматривали раннее с приставкой "Get-AD".
Для работы с командой нам понадобится Powershell Active Directory Module.
Так мы вернем всех пользователей AD:
Если нам нужно найти по имени, сделайте так:
Где вместо Adminis имя вашей учетной записи. Звездочки позволяют искать вхождение где справа и слева могут быть еще символы.
Для получения сведений об одной учетной записи AD есть ключ Identity:
Кроме этого у нас есть дополнительный ключ Properties, который выводит дополнительные свойства у объекта пользователя:
Если мы хотим выполнить Get-ADUser в OU, то для этого есть дополнительный ключ, который может искать в организационных единицах и контейнерах. В моем случае я ищу в OU Moscow и домене domain.local:
Если вам нужно искать в контейнере – используйте CN. Примеры с множеством OU смотрите ниже.
Навигация по посту:
Поиск и фильтрация списка пользователей AD в Powershell Get-ADUser -Filter
Мы можем фильтровать по одному или множеству свойств. Для того что бы увидеть все свойства нужно сделать:
В области 1 выделены все имена, по которым мы можем фильтровать. В области 2 тип данных с которыми мы можем сравнивать. Т.е. если тип данных у области 2 string (строка), то это плохая идея сравнивать с числом (int). Нужно сразу отметить, что те свойства, которые мы получаем благодаря ключу Properties мы не можем применять в Filter.
Примеры с Get-Member мы уже рассматривали раньше.
Для примера так мы можем получить всех пользователей, у которых указан город Moscow:
А теперь найдем всех пользователей, которые неверно вводили пароль более одного раза и в они включены:
Т.к. BadLogonCount – это свойство от ключа Properties оно должно проверятся через конвейер в Where.
Еще один пример, но теперь нужно найти пользователя имя которого содержит 0001 и он активен:
Получение списка пользователей Active Directory в Powershell Get-User из нескольких OU
В команде нет встроенных средств поиска в нескольких объектах сразу. Мы можем объявить переменную, содержащую этот список и затем передать через цикл. На моем пример используется контейнер Users и OU Moscow:
Если бы у меня была OU Ekaterinburg в которой находилось OU Marketing написать стоило бы так:
Выгрузка пользователей из AD в Excel с Powershell
Экспорт и выгрузка может быть во множество форматов, но для выгрузки в Excel есть специальный формат CSV. Для того что бы сделать выгрузку достаточно выполнить следующее:
Если мы хотим выгрузить только определенные свойства, то в поле SELECT -Property нужно их перечислить:
Получение Email пользователя AD в Powershell
Нужное свойство мы можем получить через соответствующий ключ Properties. Так я получу почту у конкретного пользователя Administrator:
Выгрузим в CSV имена только тех пользователей у которых нет почты:
Получение списка пользователей чей срок действия пароля истек в Powershell
Для того что бы увидеть все свойства относящиеся к паролям выполните:
И затем, по этому свойству, отфильтруем пользователей:
Одной из стандартных процедур проведения аудита ITGC для каталога Active Directory является получение выгрузки всех пользователей домена. На основании полученных данных далее формируются процедуры тестирования, к примеру изучение списка администраторов или выявление пользователей с истекшим паролем. Наиболее эффективным для формирования такой выгрузки будет использование стандартного интерфейса PowerShell , примеры которого мы и рассмотрим в данной статье
Ниже представлен скрипт PowerShell, как один из наиболее простых и быстрых способов получить список всех пользователей домена AD в формате CSV, который без проблем открывается тем же Excel’ем.
Чтобы вывести полную информации обо всех доступных атрибутах пользователя tuser, выполним команду
Get-ADUser -identity tuser -properties *
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | where | sort-object PasswordLastSet | select-object Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires | Export-csv -path c:tempuser-password-expires-2015.csv
Get-ADUser это один из основных командлетов PowerShell, который можно использовать для получения различной информации о пользователях Active Directory и их атрибутах. С помощью командлета Get-ADUser можно получить значение любого атрибута учетной записи пользователя AD, вывести список пользователей в домене с нужными атрибутами и экспортировать их в CSV, и использовать различные критерии для выборки доменных пользователей.
Комадлет Get-ADUser доступен начиная с PowerShell 2.0 и входит в специальный модуль для работы с Active Directory — Active Directory Module for Windows PowerShell (представлен в Windows Server 2008 R2). Командлеты модуля RSAT-AD-PowerShell позволяют выполнять различные операции с объектами каталога AD.
В этом примере мы покажем, как с помощью командлета PowerShell Get-ADUser получить информацию о времени последней смены пароля пользователя, дате истекает срока действия пароля и другие данные пользователей.
Как найти пользователя в AD и вывести его свойства с помощью Get-ADUser?
Для использования модуля RSAT-AD-PowerShell нужно запустить консоль PowerShell с правами администратора и импортировать модуль командой:
В Windows Server 2012 и выше модуль RSAT-AD-PowerShell устанавливается по-умолчанию при развертывании на сервере роли Active Directory Domain Services (AD DS). Для установки модуля на рядовом сервере домена нужно выполнить команду:
Install-WindowsFeature -Name "RSAT-AD-PowerShell" –IncludeAllSubFeature
В десктопных версия Windows (например, в Windows 10) для работы коммандера Get-AdUser нужно установить соответствующую версию RSAT и включить в Панели Управления компонент Active Directory Module for Windows PowerShell (Remote Server Administration Tools -> Role Administration Tools -> AD DS and AD LDS Tools -> AD DS Tools).
Вы можете установить модуль AD из PowerShell:
Add-WindowsCapability –online –Name “Rsat.ActiveDirectory.DS-LDS.Tools
Import-Module "C:PSADPoShMicrosoft.ActiveDirectory.Management.dll"
Import-Module "C:PSADPoShMicrosoft.ActiveDirectory.Management.resources.dll"
Полный список всех аргументов командлета Get-ADUser можно получить так:
Чтобы вывести список всех учетных записей домена, выполните команду:
По-умолчанию командлет Get-ADUser возвращает только 10 основных атрибутов (из более чем 120 свойств учетных записей пользователей): DistinguishedName, SamAccountName, Name, SID, UserPrincipalName, ObjectClass, статус аккаунта (Enabled: True/False согласно атрибуту UserAccountControl), и т.д.
В выводе командлета отсутствует информация о времени последней смены пароля пользователя.
Get-ADUSer –Server DC01.winitpro.loc –Identity tstuser
Чтобы вывести полную информации обо всех доступных атрибутах пользователя tuser, выполните команду:
Get-ADUser -identity tuser -Properties *
Командлет Get-ADUser с параметром Properties * вывел список всех атрибутов пользователя AD и их значения.
Попробуем изменить параметры команды Get-ADUser, чтобы вывести только нужные нам атрибуты пользователя. Можно вывести сразу несколько атрибутов пользователя:
- PasswordExpired
- PasswordLastSet
- PasswordNeverExpires
- lastlogontimestamp
Get-ADUser tuser -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires, lastlogontimestamp
Теперь в информации о пользователе есть данные о статусе аккаунта (Expired:True/False), дате смены пароля и времени последнего входа в домен (lastlogontimestamp). Представим информацию в более удобном табличном виде и уберем все лишние атрибуты с помощью Select-Object –Property или Format-Table:
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires
Получение пользователей из нескольких OU с помощью Get-ADUser
Чтобы вывести пользователей только из определенного контейнера домена (OU), воспользуйтесь параметром SearchBase:
Get-ADUser -SearchBase ‘OU=Moscow,DC=winitpro,DC=loc’ -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires
Если вам нужно выбрать пользователей сразу из нескольких OU, используйте следующую конструкцию:
$OUs = "OU=Moscow,DC=winitpro,DC=local","OU=SPB,DC=winitpro,DC=loc"
$OUs | foreach
Получить Email адреса пользователей из AD
Email пользователя это один из атрибутов в Active Directory. Чтобы вывести список email адресов пользователей, вы должны добавить поле EmailAddress в выбираемые поля командлета Get-ADUser.
Get-ADUser -filter * -properties EmailAddress -SearchBase ‘OU=MSK,DC=winitpro,DC=loc’| select-object Name, EmailAddress
Вывести список активных учёток с почтовыми адресами:
Get-ADUser -Filter <(mail -ne "null") -and (Enabled -eq "true")>-Properties Surname,GivenName,mail | Select-Object Name,Surname,GivenName,mail | Format-Table
Список пользователей, у которые нет email адреса:
Get-ADUser -Filter * -Properties EmailAddress | where -Property EmailAddress -eq $null
Следующий пример позволяет выгрузить адресную книгу email адресов компании в виде csv файла, который в дальнейшем можно импортировать в Outlook или Mozilla Thunderbird:
Get-ADUser -Filter <(mail -ne "null") -and (Enabled -eq "true")>-Properties Surname,GivenName,mail | Select-Object Name,Surname,GivenName,mail | Export-Csv -NoTypeInformation -Encoding utf8 -delimiter "," $env:tempmail_list.csv
Get-ADUser: экспорт пользователей домена в текстовый или CSV/Excel файл
Полученный список пользователей домена с атрибутами можно выгрузить в текстовый файл:
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires > C: empusers.txt
Или вы можете выгрузить пользователей AD в файл CSV, который в дальнейшем будет удобно экспортировать в Excel.
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | select Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires | Export-csv -path c: empuser-password-expires-2019.csv -Append -Encoding UTF8
Get-ADUser –Filter: Сортировка и фильтрация списка пользователей AD
С помощью параметра –Filter вы можете фильтровать список пользователей по одному или нескольким атрибутам. В качестве аргументов этого параметра можно указать значения определённых атрибутов пользователей Active Directory. При использовании параметра –Filter командлет Get-ADUser выведет только пользователей, которые соответствуют критериям фильтра.
Например, выведем список активных (Enabled) учётных записей пользователей, чье имя содержит «Dmitry» (в примере ниже используется множественный фильтр, вы можете комбинировать условия с помощью стандартных логических операторов сравнения PowerShell):
Get-AdUser -Filter "(Name -like ‘*Dmitry*’) -and (Enabled -eq ‘True’)" -Properties * |select name,enabled
Дополнительно с помощью sort-object вы можете отсортировать полученный список пользователей по определенному атрибуту. Кроме того, для выборки пользователей можно использовать командлет where. Здесь также можно использовать сразу несколько критериев фильтрации.
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires -SearchBase ‘OU=Moscow,DC=winitpro,DC=loc’| where | sort-object PasswordLastSet | select-object Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires
Таким образом, можно построить таблицу с любыми необходимыми атрибутами пользователей Active Directory.
Get-ADUser: разные примеры использования
Далее приведем еще несколько полезных вариантов запросов о пользователях Active Directory с помощью различных фильтров. Вы можете их комбинировать для получения необходимого списка пользователей домена:
Вывод пользователей AD, имя которых начинается с Roman:
Чтобы подсчитать общее количество всех аккаунтов в Active Directory:
Список всех активных (не заблокированных) учетных записей в AD:
Get-ADUser -Filter | Select-Object SamAccountName,Name,Surname,GivenName | Format-Table
get-aduser -Filter * -Properties Name, WhenCreated | Select name, whenCreated
Вывести всех пользователей, которые были созданы за последние 24 часа (пример отсюда):
$lastday = ((Get-Date).AddDays(-1))
Get-ADUser -filter
Список учетных записей с истекшим сроком действия пароля (срок действия пароля настраивается в доменной политике):
Get-ADUser -filter -properties name,passwordExpired | where |select name,passwordexpired
Задача: для списка учетных записей, которые хранятся в текстовом файле (по одной учетной записи в строке), нужно получить телефон пользователя из AD и выгрузить информацию в текстовый csv файл (для дальнейшего формирования отчета в Exсel).
Пользователи, которые не меняли свой пароль в течении последних 90 дней:
$90_Days = (Get-Date).adddays(-90)
Get-ADUser -filter
$user = Get-ADUser winadmin -Properties thumbnailPhoto
$user.thumbnailPhoto | Set-Content winadmin.jpg -Encoding byte
Список групп, в которых состоит учетная запись пользователя
Get-AdUser winadmin -Properties memberof | Select memberof -expandproperty memberof
Вывести список пользователей из OU, которые состоят в определенной группе безопасности:
Get-ADUser -SearchBase ‘OU=Moscow,DC=winitpro,DC=loc’ -Filter * -properties memberof | Where-Object
Вывести список компьютеров домена, на которые разрешено входить пользователю (ограничение через атрибут LogonWorkstations):
Get-ADUser AIvanov -Properties LogonWorkstations | Format-List Name, LogonWorkstations
28.09.2011
itpro
Active Directory
комментариев 59
Достаточно часто системному администратору 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 (наименование организации):
Код макроса на VBA для получения данных из AD следующий:
Чтобы данная функция заработала, необходимо подключить ряд библиотек в 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!
Предыдущая статья Следующая статья
Создание WMI фильтров для групповых политик (GPO) в домене AD
Установка программ с помощью групповых политик в домене Active Directory
Управление репликацией Active Directory
Очень интересная статья, но не заработало, однако. Делал так: новая книга-новый макрос-занес скрипт с измененным доменом-отметил библиотеки-для теста в ячейке написал имя пользователя-в другой запрос =GetADInfo(«samAccountName»;A2; «distinguishedName») — ругается на GetADInfo
Валерий, действительно в коде была небольшая ошибка. Я обновил код vba модуля
Проверьте, заработает ли
спасибо, работает, но почему-то не полностью .. «lastLogon» и аналогичные не работают(
может подскажете, в чем может быть дело?
Спасибо, скопировал, подправил настройки.
Работает.
Круть! Все работает на ура. С помощью этого скрипта можно просто и быстро получить массово любую информацию из Active Directory
Нюанс с которым пришлось покопаться — нужно создавать именно новый vba модуль в xls (с классом и текущей формой почему-то не заработало.)
А как узнать названия всех полей которые можно вытащить ?
типа Company ?
Все праметры обьектов в AD можно узнать с помощью любого LDAP браузера или с помощью консоли adsiedit.msc
Спасибо Вам большое за код! В своей работе, к сожалению, вынужден пользоваться поиском не по samAccountName, а по DisplayName, что приводит к выпадению полных тезок. Не подскажете как дополнить код, что бы можно было выбрать конкретного человека? Например добавить в excel поле какого по счету брать человека, а в код на каком человеке возращать значение.
Заранее спасибо
Не совсем понятно, что Вы хотите, но предполагаю, то что Вы хотите реализовать можно сделать с помощью дополнительного скрытого столбца с samAccountName и проверки значений ячеек функцией Excel IF (ЕСЛИ)
Добрый день! при работе с скриптом был обнаружен следующий нюанс: не возвращает значения атрибутов ни lastLogon, ни lastLogonTimestamp при этом если не находит объект или при работе с другими числовыми или строковыми атрибутами значения выдает корректно, самостоятельно решить данный вопрос не удается 🙁
К сожалению тоже не удалось победить проблему с возвратом значения lastLogonTimestam. Какая-то ерунда с обработкой timestamp в формате UTC…
Если получиться найди решение — поделитесь 🙂
VBA ошибок не выдает, запустив скрипт отдельно (не из Excel) получил ошибку «Несоответствие типа», выяснил, что lastLogon и ему подобные в формате UTC имеют размер в 64 бита. Проверить работоспособность в 64 разрядном «офисе» с поддержкой типа данных LongLong нет возможности.
lastLogon и ему подобные в формате UTC имеют размер в 64 бита. Проверить работоспособность скрипта в 64 битном «офисе» с поддержкой типа данных LongLong пока нет возможности.
Видимо проще эти переменные через vbs или dsquery вытягивать…
Други, помогите! Я использую макрос в Excel для получения большого количества атрибутов по каждому из пользователей для каких-то массовых изменений. Потом делаю загрузку полученной таблицы обратно. Но есть нюанс, я не смог побороть ситуацию, когда значение какого-то атрибута надо отчистить. Т.е., например, у пользователя забрали мобильный телефон и надо это поле очистить. Не получается. Я вышел из ситуации пока глупо: пишу в атрибут пробел. Но это не дело… Пустое значение, типа objUser.Mobile = «» не прокатывает.
Что конкретно пишете в атрибут при очистке его значения? Возможно стоит записывать не просто «», а что-то типа NULL
itpro, нет, так не прокатит. Почитав ихние мануалы, понял, что просто так писать пустое нельзя. Есть специальная конструкция очистки значения в атрибуте. И узнал немного полезного, например, как удалить одно из значений в многозначимом (можно так писать?) атрибуте. У меня, например, есть такая строчка в VBA: objUser.PutEx ADS_PROPERTY_CLEAR, «homePhone», 0 — использование метода не просто put, а putex, далее параметр, указывающий удаление значения, далее имя атрибута и еще какой-то параметр. Читал, как обычно, не внимательно, но в данный момент для меня важен результат.
подскажите, как скорректировать скрипт чтобы удалить из АД информацию lastlogon
Скрипт из статьи предназначен в первую очередь для построения различных отчетов и статистике непосредственно из Excel, т.е. для четния данных из AD В вашем случае было бы намного проще копать в сторону Powershell скрипта или WMI.
А если не секрет какова вообще задача? Почистить следы? 🙂
Подскажите, есть ли возможность с помощью данной функции извлекать все значения атрибута, который представлены в виде многозначных строк? Например, значение атрибута msSFU30PosixMemberOf ?
Добрый день!
А у меня почему-то никак не хочет работать…
В строке Set objRecordSet = adoCommand.Execute выдает ошибку:
Runtime error ‘-2147217865 (80040e37)’
Таблица не существует.
Даже не знаю, куда копать. Очень нужно.
Внимательно перепроверьте список подключаемых модулей и корректность указаная домена в строке strDomain=
ЗЫ. Скрипт точно работал в 2010 Excel
Спасибо. Поменял в strDomain «ru» на «local», все заработало
Добрый день!
Пытаюсь выбрать реквизиты пользователя по ФИО, немного доработав Вашу функцию, но получаю в Excel ошибку ЗНАЧ!
Уже всю голову сломал, помогите, пожалуйста!
Вот текст функции:
Public Function ADGetUserByFIO(ByVal SearchFIO, ByVal ReturnField)
Dim adoCommand, strDomain, objConnection, objRecordSet, Result, Response
Немного не понял задачи.
В каком атрибуте AD вы храните ФИО?
Часть почему-то не скопировалась:
Public Function ADGetUserByFIO(ByVal SearchFIO, ByVal ReturnField)
Как тут было озвучено ранее, вынужден искать не по 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;»
Соответственно в Excel функция выглядит:
=GetADInfo(«DisplayName»;»Description»;A1;B1;»samAccountName»)
В данном случае ищем по ФИО и идентификатору и возвращаю логин юзера.
Банально, но может кому-то пригодится.
P.S. Огромное спасибо за код. Правда когда таблица на несколько тысяч, то значительно подвисает при каждом изменении. Пришлось перейти от функции к процедуре
как отображать все логины без введения? мне не поиск нужен а отображение в таблице все что есть по отдельным полям
как сделать чтобы не нужно было вводить логин а показывало все логи само?
itpro, задача в том, чтобы выбирать через Excel атрибуты пользователя из AD
Never, разобрался почему не работало. У меня в конце скрипта 2 раза идет objConnection.Close, что и приводит к ошибке при закрытии коннекта второй раз. Excel в этом случае не понимает, что произошло и возвращает ошибку !ЗНАЧ
Всё супер, указал домен и всё заработало.
Кто-нибудь знает как ускорить поиск, чтобы искал не по всему AD, а сразу в определенном юните зная путь или его distinguishedName?
OU=ИмяОргЮнита,DC=Domain name,DC=local
Например, в домене firma.local есть папка с сотрудниками «staff», то можно изменить переменную strDomain = «OU=staff,dc=firma,dc=local»
Скрипт точно не для админа.
Для админа скрипт должен быть wsf. Чтоб при его запуске сам открывался Excel с новой книгой, в которую вставлялись результаты работы скрипта.
Вопрос возник. Допустим структура в домене такая:
domain2.domain1.local
ou00
groups
pc
users
ou01
groups
pc
users
ou02
groups
pc
users
и т.д.
И кстати, если прописываю конкретную OU, например strDomain = «OU=ou01,DC=domain2,DC=domain1,DC=local» — то тоже вытаскивает данные в пределах OU=ou00
Конструкция strDomain = ‘DC=domain2,DC=domain1,DC=local’ должна искать по всему домену. Вас случайно в правах не ограничили по остальным OU?
Нет, в правах не ограничен. Эксель запускал от локального администратора и от администратора домена.
Вариант с экселем для меня более удобный. Ещё раз. Структура:
dd1.domain.local
—ALL
—-OU00
———OU01
————Groups
————PCs
————Users
———OU02
————Groups
————PCs
————Users
———OU03
————Groups
————PCs
————Users
и т.д.
Пробовал разные варианты:
‘strDomain = «DC=dd1,DC=domain,DC=local»
‘strDomain = «OU=OU00,OU=ALL,DC=dd1,DC=domain,DC=local»
‘strDomain = «OU=OU01,OU=OU00,OU=ALL,DC=dd1,DC=domain,DC=local»
‘strDomain = «OU=OU02,OU=OU00,OU=ALL,DC=dd1,DC=domain,DC=local»
По пользователям, которые находятся в других OU выдает «not found».
Инфу получается по факту достаёт только из «OU=Users,OU=OU01,OU=OU00,OU=ALL,DC=dd1,DC=domain,DC=local»
Что ещё может быть?
Вообще говоря параметр subtree указывает, что выполняется рекурсивный поиск, начиная с указанного контенера или корня домена.
Поэтому достаточно указать:
strDomain = ‘DC=dd1,DC=domain,DC=local’
Попробуйте в скрите исследовать набор объектов, который возвращается из objRecordSet при поиске от корня.
Отличный скрипт! Рабочий. Вот только у меня не получается его доработать, чтобы он выдавал список групп в которых состоит пользователь. То есть юзер вводит логин произвольного пользователя и в итоге получает список групп домена, в которых состоит введённый пользователь. Подскажите, пжлст, как допилить скрипт под мои нужды?
Как я понимаю можно реализовать ещё такой способ: перебирать все группы домена и если в этой группе состоит введённый пользователь, то выводить в список эту группу. Как реализовать только все это- вот проблема…
что-то так и не получается у меня видоизменить скрипт. может сможете помочь?
Спасибо большое за подсказку. немного видоизменил под себя, что-то добавил и убрал, но все заработало!
еще раз спасибо!
Подскажите, как можно этот vba код внедрить в word, к примеру в служебную записку чтоб сразу дергались данные из AD
Скорре всего да. Ищите данные по запросу «word vba get active directory user info»
Подскажите, как можно вывести сразу несколько значений в одну ячейку через точку с запятой или каждое требуемое значение в соседние ячейки от той в которую вводим =GetADInfo….?
GetADInfo это функция — она возвращает только одно значение.
Вам наверно проще сделать еще одну функцию, например GetADInfo2 и в строке справа использовать ее:
=GetADInfo2("samAccountName";A2; "description")
я так и делаю, НО, когда пяток пользователей , а вывести нужно 9 значений (enabled, SAMAccountName, Display, Name, sn, GivenName, employeeID, Title, mail, manager) то это уже 5*9=40 запросов к AD,
а когда файл с сотней пользователей, то соответственно и запросов соответственно под 1000… даже, если excel и делает эти запросы в 6 потоков, это зависун тот ещё. поэтому и думаю можно ли уменьшить кол-во запросов к AD. пробовал играться с настройками и выводом =GetADInfo(«samAccountName»;A2; «ReturnField «;»ReturnField2″…)
и в запросе к ад добавлять необходимые выводы…
adoCommand.CommandText = _
";(&(objectCategory=" & "User" & ")" & _
"(objectClass=" & "User" & ")" & _
"(" & SearchField & "=" & SearchString & "));" & SearchField & "," & ReturnField & "," & ReturnField2 &. ";subtree"
и даже если добавить
GetADInfo = objRecordSet.Fields(ReturnField) + "; " + objRecordSet.Fields(ReturnField)
он выведет несколько значений
но вот как его заставить вывести второе значение из полученного из AD по запросу выше…
напрмер так уже не работает
GetADInfo = objRecordSet.Fields(ReturnField) + "; " + objRecordSet.Fields(ReturnField2)
у меня знаний не хватает.
провожу ресертификацию пользователей приложений. когда вывожу данные по приложениям из MSSQL баз, там сразу добавляю данные из AD через openquery запрос.
а из приложений, где невозможно сразу обратиться к ad при выводе данных, то получается приходится работать с таким вот ёкслелем.
выход конечно выгружать данные из AD в отдельный ёксель и по нему ВПРить, но это постоянно обновлять данные нужно…
Вот и спросил у вас как у гуру по данномуц вопросу
потому что 100 запросов и 900 — разница огромная)
Понятна ваша проблема. Сейчас не готов вспоминать курс молодого бойца в vbs. Это уже устаревшая техника.
На самом деле я думаю, что будет проще формировать данные в esxel через PowerShell и командлеты рабаботы с AD. Как раз на след неделе буду писать про это статью, покажу как брать данные из AD и помещать их в документ Excel. Я думаю вам это будет проще.
Спасибо! Буду очень ждать, добавте пожалуйста ссылку на эту статью здесь, если вам не сложно будет.
Добрый день. А можно ли получить с помощью этого скрипта список всех групп из определённого оргюнита/домена?
Добрый день. А можно ли получить с помощью этого скрипта список всех групп из определённого оргюнита/домена?
Не подскажу. Именно такой задачи не стояло когда-то. А сейчас перешел на PowerShell. VBA уже не так интересен.
Цель у меня получить именно силами vba список всех групп из конкретного оргюнита (в нем будут вложены и другие оргюниты/каталоги). Повершеллом легче и быстрее, но нужно именно vba…
Доброго дня. Компилятор почему-то ругается на строчку %MINIFYHTML…
Ругается на самый первый символ: invalid character. Не совсем понятно откуда взялась эта последовательность шестнадцатеричная.
17.08.2020
itpro
Active Directory, PowerShell
комментариев 8
Сохраненные запросы (Saved Queries) в mmc консоли Active Directory Users and Computers (ADUC) позволяют создавать различные LDAP фильтры для выборки объектов Active Directory.Такие запросы можно сохранять, редактировать и переносить между компьютерами. С помощью сохраненных запросов можно быстро и эффективно решать задачи поиска и выборки объектов в AD по различным критериям. Saved Queries помогают администратору быстро выполнять типовые задачи администрирования объектов AD: вывести список всех отключенных учетных записей в домене, выбрать всех пользователей определенной организации с ящиками на заданном сервере Exchange и т.п.
При использовании сохраненных LDAP запросов администратор может выполнять групповые операции с объектами из разных OU (контейнеров) Active Directory. Например, можно выполнить массовую блокировку/разблокировку, перемещение, удаление учетных записей, переименование и т.п. Такие запросы в консоли ADUC позволяют обойти иерархическую структуру OU в Active Directory и собрать все нужные объекты в плоском табличном виде.
Большинство операций по выборке объектов в AD можно выполнить с помощью командлетов из модуля PowerShell для Active Directory (например, Get-ADUser, Get-ADComputer, Get-ADObject, Get-ADGroup, Search-ADAccount и т.д.), утилиты dsquery, скриптов vbs и т.д. Но для не-администраторов гораздо проще использовать графическую консоль ADUC для получения информации из AD.
Active Directory Saved Queries впервые появились в Windows Server 2003 и продолжают поддерживаться во всех последующих версиях Windows Server. Для использования сохранных запросов на вашем компьютере должна быть установлена консоль ADUC (входит в состав средств администрирования RSAT).
Как создать сохраненный запрос в консоли Active Directory?
Рассмотрим несколько примеров использования сохраненных запросов в консоли Active Directory Users and Computers для поиска объектов. Предположим, вам нужно получить список активных (незаблокированных) учетных записей пользователей, их email адресов и названий отделов.
Вы можете создать и сохранить в вашей консоли ADUC множество различных сохраненных запросов, которые можно организовать в древовидную структуру. Таким образом вы может создать удобную подборку запросов, позволяющих быстро выполнять типовые задачи администрирования AD.
Совет. В некоторых случаях удобнее обращаться к объектам Active Directory напрямую из Exсel. Как это реализовать описано в статье Выполнение запросов Active Directory из Excel.
В оснастке поддерживается несколько режимов формирования сохраненных запросов AD. Не обязательно указывать текст LDAP фильтра вручную. Выможете сформировать сфой запрос с помощью простого графического мастера. Просто выбираете различные атрибуты объектов AD и используйте для поиска по нужным критериям. Например, чтобы вывести список компьютеров с Windows Server в домене, выберите:
В качестве символа подстановки используется * (можно указать так *Server* ). Можно добавить несколько критериев поиска.
Сохраните запрос и обновите его в консоли ADUC. В списке будут показаны все Windows Server в вашем домене.
Сохраненные запросы хранятся локально на компьютере, на котором они были созданы. Xml-файл с настройками находится здесь C:\Users\%USERNAME%\AppData\Roaming\Microsoft\MMC\DSA). Для переноса сохранённых запросов между компьютерами, в консоли dsa.msc есть функция экспорта (Export Query Definition) и импорта (Import Query Definition) запросов через XML файлы.
Полезные сохраненные LDAP запросы для MMC консоли Active Directory
В следующей табличке собраны примеры часто используемых LDAP запросов для выборки объектов Active Directory. Вы можете сохранить их в своей консоли ADUC для ежедневного использования.
Использование LDAP фильтров в PowerShell
Вы можете использовать рассмотренные LDAP фильтры для поиска объектов в консоли PowerShell. В большинстве командлетов из модуля PowerShell для Active Directory есть специальный параметре LdapFilter. В этом параметре нужно указывать код LDAP запроса.
Get-ADUser -LdapFilter "(&(objectCategory=person)(objectClass=user)(department=*Отдел продаж*))"| ft -a DisplayName,department
Командлеты Get-ADUser , Get-ADComputer , Get-ADGroup являются специализированными и используются для поиска объектов определенного типа – пользователей, компьютеров или групп. Если вы не знаете тип объекта AD, который вам нужен, или вам нужна информация о всех типах объектов, используйте более универсальный командлет Get-ADObject . Например, для поиска объекта по SID:
Get-ADObject -LdapFilter "(objectSID=S-1-5-21-71234583-15204917-8654222215-1231)" -Properties * -SearchBase “OU=MSK,DC=winitpro,DC=ru| ft -a DisplayName,Title
Get-ADUser команда, которая возвращает список пользователей AD в Powershell. На самом деле командлет мало чем отличается от тех, которые мы рассматривали раннее с приставкой "Get-AD".
Для работы с командой нам понадобится Powershell Active Directory Module.
Так мы вернем всех пользователей AD:
Если нам нужно найти по имени, сделайте так:
Где вместо Adminis имя вашей учетной записи. Звездочки позволяют искать вхождение где справа и слева могут быть еще символы.
Для получения сведений об одной учетной записи AD есть ключ Identity:
Кроме этого у нас есть дополнительный ключ Properties, который выводит дополнительные свойства у объекта пользователя:
Если мы хотим выполнить Get-ADUser в OU, то для этого есть дополнительный ключ, который может искать в организационных единицах и контейнерах. В моем случае я ищу в OU Moscow и домене domain.local:
Если вам нужно искать в контейнере - используйте CN. Примеры с множеством OU смотрите ниже.
Навигация по посту
Поиск и фильтрация списка пользователей AD в Powershell Get-ADUser -Filter
Мы можем фильтровать по одному или множеству свойств. Для того что бы увидеть все свойства нужно сделать:
В области 1 выделены все имена, по которым мы можем фильтровать. В области 2 тип данных с которыми мы можем сравнивать. Т.е. если тип данных у области 2 string (строка), то это плохая идея сравнивать с числом (int). Нужно сразу отметить, что те свойства, которые мы получаем благодаря ключу Properties мы не можем применять в Filter.
Примеры с Get-Member мы уже рассматривали раньше.
Для примера так мы можем получить всех пользователей, у которых указан город Moscow:
А теперь найдем всех пользователей, которые неверно вводили пароль более одного раза и в они включены:
Т.к. BadLogonCount - это свойство от ключа Properties оно должно проверятся через конвейер в Where.
Еще один пример, но теперь нужно найти пользователя имя которого содержит 0001 и он активен:
Получение списка пользователей Active Directory в Powershell Get-User из нескольких OU
В команде нет встроенных средств поиска в нескольких объектах сразу. Мы можем объявить переменную, содержащую этот список и затем передать через цикл. На моем пример используется контейнер Users и OU Moscow:
Если бы у меня была OU Ekaterinburg в которой находилось OU Marketing написать стоило бы так:
Выгрузка пользователей из AD в Excel с Powershell
Экспорт и выгрузка может быть во множество форматов, но для выгрузки в Excel есть специальный формат CSV. Для того что бы сделать выгрузку достаточно выполнить следующее:
Если мы хотим выгрузить только определенные свойства, то в поле SELECT -Property нужно их перечислить:
Получение Email пользователя AD в Powershell
Нужное свойство мы можем получить через соответствующий ключ Properties. Так я получу почту у конкретного пользователя Administrator:
Выгрузим в CSV имена только тех пользователей у которых нет почты:
Читайте также: