В комментариях к предыдущей статье вспомнили про учет в 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 . Теперь необходимо выбрать нужные атрибуты из каждого класса с помощью «расширения» ― значок с двумя стрелочками у заголовка колонки:

• класс user расширим, выбрав lastLogonTimestamp и userAccountControl ;
• в person выберем telephoneNumber ;
• в top ― whenCreated ;
• и в securityPrincipal ― SamAccountName .

Расширяем запрос.

Теперь настроим фильтр: в частности, чтобы не получить заблокированные аккаунты, нужно чтобы атрибут userAccountControl имел значение 512 или 66048. Фильтр может быть другой в вашем окружении. Подробнее про атрибут можно прочитать в документации Microsoft .

Применяем фильтр .

Иногда Excel неверно определяет формат данных, особенно значения атрибута lastLogonTimestamp. Если вдруг постигла такая беда, на вкладке «Преобразовать» можно выставить верный формат.

Теперь столбец userAccountControl стоит удалить ― в отображении он не нужен совершенно. И нажимаем «Загрузить и закрыть».

Получилась табличка, которую осталось совсем немного довести до ума. Например, переименовать столбцы в что-то удобочитаемое. И настроить автоматическое обновление данных.

Автоматическое обновление при открытии таблицы или по таймауту настраивается во вкладке «Данные» в «Свойствах».

Настройка обновления данных.

После того, как настройка обновления будет завершена, можно смело отдавать таблицу сотрудникам отдела персонала или службе безопасности ― пусть знают, кто и когда входил в систему.

Код запроса на языке «М» под спойлером.

let Источник = ActiveDirectory.Domains("domain.ru"), domain.ru = Источник{}[#"Object Categories"], user1 = domain.ru{}, #"Удаленные столбцы" = Table.RemoveColumns(user1,{"organizationalPerson", "shadowAccount", "posixAccount", "msExchOmaUser", "msExchBaseClass", "msExchIMRecipient", "msExchCertificateInformation", "msExchMultiMediaUser", "msExchMailStorage", "msExchCustomAttributes", "mailRecipient", "distinguishedName"}), #"Развернутый элемент securityPrincipal" = Table.ExpandRecordColumn(#"Удаленные столбцы", "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}), #"Развернутый элемент top" = Table.ExpandRecordColumn(#"Развернутый элемент securityPrincipal", "top", {"whenCreated"}, {"whenCreated"}), #"Развернутый элемент person" = Table.ExpandRecordColumn(#"Развернутый элемент top", "person", {"telephoneNumber"}, {"telephoneNumber"}), #"Развернутый элемент user" = Table.ExpandRecordColumn(#"Развернутый элемент person", "user", {"lastLogonTimestamp", "userAccountControl"}, {"lastLogonTimestamp", "userAccountControl"}), #"Строки с применным фильтром" = Table.SelectRows(#"Развернутый элемент user", each ( = 512 or = 66048)), #"Измененный тип" = Table.TransformColumnTypes(#"Строки с примененным фильтром",{{"lastLogonTimestamp", type datetime}}), #"Удаленные столбцы1" = Table.RemoveColumns(#"Измененный тип",{"userAccountControl"}) in #"Удаленные столбцы1"

Создаем адресную книгу, или что делать, когда корпоративный портал с AD не дружит

Другой вариант использования 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 и расширим их:

• класс computer расширим, выбрав cn , operatingSystem , operatingSystemServicePack и operatingSystemVersion ;
• в классе top выберем whenCreated .

Расширенный запрос.

При желании можно сделать отчет только по серверным операционным системам. Например, применить фильтр по атрибуту operatingSystem или operatingSystemVersion. Я не буду этого делать, но поправлю отображение времени создания ― мне интересен только год. Для этого на вкладке «Преобразование» выберем нужную нам колонку и в меню «Дата» выберем «Год».

Извлекаем год из времени ввода компьютера в домен.

Теперь останется удалить столбец displayname за ненадобностью и загрузить результат. Данные готовы. Теперь можно работать с ними, как с обычной таблицей. Для начала сделаем сводную таблицу на вкладке «Вставка» ― «Сводная таблица». Согласимся с выбором источника данных и настроим ее поля.

Настройки полей сводной таблицы.

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

Сводная таблица по компьютерам в AD.

При желании можно добавить сводный график, также на вкладке «Вставка». В «Категории» (или в «Ряды», по вкусу) добавим operatingSystem , в данные ― cn . На вкладке «Конструктор» можно выбрать тип диаграммы по душе, я предпочел круговую.

Круговая диаграмма.

Теперь наглядно видно, что, несмотря на идущее обновление, общее количество рабочих станций с Windows XP и серверов с Windows 2003 довольно велико. И есть к чему стремиться.

Код запроса под спойлером.

let Источник = ActiveDirectory.Domains("domain.ru"), domain.ru = Источник{}[#"Object Categories"], computer1 = domain.ru{}, #"Удаленные столбцы" = Table.RemoveColumns(computer1,{"user", "organizationalPerson", "person"}), #"Другие удаленные столбцы" = Table.SelectColumns(#"Удаленные столбцы",{"displayName", "computer", "top"}), #"Развернутый элемент computer" = Table.ExpandRecordColumn(#"Другие удаленные столбцы", "computer", {"cn", "operatingSystem", "operatingSystemServicePack", "operatingSystemVersion"}, {"cn", "operatingSystem", "operatingSystemServicePack", "operatingSystemVersion"}), #"Развернутый элемент top" = Table.ExpandRecordColumn(#"Развернутый элемент computer", "top", {"whenCreated"}, {"whenCreated"}), #"Извлеченный год" = Table.TransformColumns(#"Развернутый элемент top",{{"whenCreated", Date.Year}}), #"Удаленные столбцы1" = Table.RemoveColumns(#"Извлеченный год",{"displayName"}) in #"Удаленные столбцы1"

0

У меня есть следующий рабочий скрипт, который проверяет, является ли большой список пользователей в CSV-файле членом группы AD и записывает результаты в results.csv.

Не знаете, как преобразовать сценарий, чтобы я мог изменить $group = "InfraLite" на $group = DC .\List_Of_AD_Groups.CSV .

Таким образом, сценарий не просто возвращает совпадения для одной группы AD, но так что он возвращает совпадения для групп 80 AD, содержащихся в List_of_AD_groups.csv. Написание YES/NO для каждой группы AD в новом столбце CSV (или, если это невозможно, создание отдельного CSV-файла для каждой группы с результатами будет также.

Я мог бы сделать это вручную, изменив значение из $group и имя файла экспорта и повторного запуска скрипта в 80 раз, но должен быть быстрым был с PS, чтобы сделать это

например results.csv ?:

NAME AD_GROUP1 AD_GROUP2 AD_GROUP80 etc etc. user1 yes no yes user2 no no yes user3 no yes no echo "UserName`InfraLite" >> results.csv $users = GC .\user_list.csv $group = "InfraLite" $members = Get-ADGroupMember -Identity $group -Recursive | Select -ExpandProperty SAMAccountName foreach ($user in $users) { if ($members -contains $user) { echo "$user $group`tYes" >> results.csv } else { echo "$user`tNo" >> results.csv } }

• 2 ответа

Сортировка:

Активность

0

тривиальное решение вашей проблемы было бы обернуть ваш существующий код в другом цикле и создать выходной файл для каждой группы:

$groups = Get-Content "C:\groups.txt" foreach ($group in $groups) { $members = Get-ADGroupMember ... ... }

Более изящный подход был бы создать шаблон отображения группы, клонировать его для каждого пользователя, и заполнить копию с членством в группах пользователя. Нечто подобное должно работать:

$template = @{} Get-Content "C:\groups.txt" | ForEach-Object { $template[$_] = $false } $groups = @{} Get-ADGroup -Filter * | ForEach-Object { $groups[$_.DistinguishedName] = $_.Name } Get-ADUser -Filter * -Properties MemberOf | ForEach-Object { $groupmap = $template.Clone() $_.MemberOf | ForEach-Object { $groups[$_] } | Where-Object { $groupmap.ContainsKey($_) } | ForEach-Object { $groupmap[$_] = $true } New-Object -Type PSObject -Property $groupmap } | Export-Csv "C:\user_group_mapping.csv" -NoType

0

я играл с этим на некоторое время, и я думаю, что я нашел способ, чтобы получить Вас именно то, что вы были после.

Я думаю, что Ansgar был на правильном пути, но я не мог заставить его делать то, что было после. Он упомянул, что на момент написания статьи он не имел доступа к среде AD.

Вот что я придумал:

$UserArray = Get-Content "C:\Temp\Users.txt" $GroupArray = Get-Content "C:\Temp\Groups.txt" $OutputFile = "C:\Temp\Something.csv" # Setting up a hashtable for later use $UserHash = New-Object -TypeName System.Collections.Hashtable # Outer loop to add users and membership to UserHash $UserArray | ForEach-Object{ $UserInfo = Get-ADUser $_ -Properties MemberOf # Strips the LPAP syntax to just the SAMAccountName of the group $Memberships = $UserInfo.MemberOf | ForEach-Object{ ($_.Split(",")).replace("CN=","") } #Adding the User=Membership pair to the Hash $UserHash.Add($_,$Memberships) } # Outer loop to create an object per user $Results = $UserArray | ForEach-Object{ # First create a simple object $User = New-Object -TypeName PSCustomObject -Property @{ Name = $_ } # Dynamically add members to the object, based on the $GroupArray $GroupArray | ForEach-Object { #Checking $UserHash to see if group shows up in user"s membership list $UserIsMember = $UserHash.($User.Name) -contains $_ #Adding property to object, and value $User | Add-Member -MemberType NoteProperty -Name $_ -Value $UserIsMember } #Returning the object to the variable Return $User } #Convert the objects to a CSV, then output them $Results | ConvertTo-CSV -NoTypeInformation | Out-File $OutputFile

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

Сегодня мы попробуем выгрузить список все пользователей в отдельный файл из Active Directory. Главным помощником в этом деле у нас будет PowerShell. Всё дело в том, что Microsoft изначально планировала командную консоль PowerShell как основной инструмент для управления серверными компонентами Windows. И на сегодняшний день, когда мы имеем уже версию 2.0, по большому счету, это так и есть.

Ещё в недалеком прошлом, чтобы хоть как-то взаимодействовать с AD, администраторам необходимо было иметь в своем распоряжении либо утилиту dsquery, либо разного рода скрипты или утилиты. Сегодня же начиная с версии Windows Server 2008 R2, мы можем работать с AD через PowerShell. С приходом PowerShell 2.0 для взаимодействия с Active Directory используется специальный модуль Active Directory Module for Windows PowerShell , который содержит в себе необходимый список командлетов. Для наших задач мы будем использовать команду Get-ADUser .

Итак, в зависимости под управлением какой операционной системы мы будем запускать консоль PowerShell, нам необходимо будет выполнить “подготовительные действия”.

1) Если мы работаем из-под Windows Server до версии 2012 , то нам необходимо выполнить команду:

• Import-Module activedirectory – команда для импортирования модуля в AD

Для версий операционной системы от 2012 и выше, данный модуль уже включен по умолчанию.

2) Если мы работаем из под любой клиентской Windows, то на ней должен быть установлен пакет удаленного администрирования RSAT, с проинсталлированным компонентом Active Directory Module for Windows PowerShell.

Стоит отметить, что командлет Get-ADUser рекомендуется выполнять при количестве выгружаемых данных до 1000 пользователей.

Экспортируем пользователей AD при помощи PowerShell в отдельный файл

Для начала вызовем справку для команды Get-ADUser. В результате Вы получите все необходимые команды для дальнейшего администрирования.

• help Get-ADUser – команда для вызова справки

Чтобы получить в окне PowerShell список всех пользователей со всеми свойствами, необходимо выполнить следующую команду:

• Get-ADUser -filter * – экспорт списка пользователей AD

Данная выгрузка не совсем информативна и не умещает в окне всю необходимую информацию. Поэтому попробуем сузить поиск и выведем свойства конкретного пользователя с именем user1:

• Get-ADUser -identity user1 -properties * – экспорт свойств определенного пользователя

А теперь попробуем экспортировать список всех пользователей с их свойствами во внешний txt или csv файл:

• Get-ADUser -filter * -properties * | Export-csv -path c:\users.csv -encoding Unicode – экспорт пользователей в отдельный файл

Хотелось бы обратить отдельное внимание на ключ -encoding Unicode . Он служит для того, чтобы русская кириллица, после экспорта из AD, могла корректно отображаться в выгруженном файле. Например, через Microsoft Excel мы увидим вопросительные знаки вместо русских букв.

При просмотре файла данные экспортируются в одну строку и тем самым не пригодны для чтения. Чтобы это изменить, нам необходимо выполнить следующие действия:

В этой статье мы рассмотрим возможности PowerShell по управлению группами домена Active Directory. Мы рассмотрим, как создать новую группу в AD, добавить в нее пользователей (или удалить), вывести список пользователей группы и несколько других полезных действия с доменными группами, которые чрезвычайно полезны при повседневном администрировании. Для управления группами AD в модуле PowerShell для Active Directory имеются следующие основные командлеты:

Для использования данных командлетов в вашей сессии PowerShell должен быть загружен специальный модуль взаимодействия с AD — Active Directory Module for Windows PowerShell . Данный модуль впервые был представлен в Windows Server 208 R2. В Windows Server 2012 и выше этот модуль включен по умолчанию. На клиентских компьютерах его можно установить и включить в качестве одного из компонентов RSAT. Проверить, загружен ли модуль можно так:

Get-Module -Listavailable

Как вы видите, модуль ActiveDirectory загружен. Если нет – импортируйте его командой:

Import-Module activedirectory

Полный список команд модуля можно получить так:

Get-Command -Module ActiveDirectory

В модуле всего доступно 147 командлетов, из которых с группами могут работать 11.

Get-Command -Module ActiveDirectory -Name "*Group*"

Вот их список:

• Add-ADPrincipalGroupMembership
• Get-ADAccountAuthorizationGroup
• Get-ADGroup
• Get-ADGroupMember
• Get-ADPrincipalGroupMembership
• New-ADGroup
• Remove-ADGroup
• Remove-ADPrincipalGroupMembership
• Set-ADGroup

Создадим новую группу в указанном контейнере (OU) Active Directory с помощью команды New-ADGroup :

New-ADGroup "TestADGroup" -path "OU=Groups,OU=Moscow,DC=corp,dc=winitpro,DC=ru" -GroupScope Global -PassThru –Verbose

С помощью атрибута Description можно задать описание группы, а с помощью DisplayName изменить отображаемое имя.

Параметром GroupScope можно задать один из следующих типов групп:

• 0 = DomainLocal
• 1 = Global
• 2 = Universal

Создать группу распространения можно так:

New-ADGroup "TestADGroup-Distr" -path "OU=Groups,OU=Moscow,DC=corp,dc=winitpro,DC=ru" -GroupCategory Distribution -GroupScope Global -PassThru –Verbose

Add-AdGroupMember – добавить пользователей в группу AD

Добавить пользователей в группу Active Directory можно с помощью командлета Add-AdGroupMember . Добавим в новую группу двух пользователей:

Add-AdGroupMember -Identity TestADGroup -Members user1, user2

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

Формат CSV файла такой (список пользователей по одному в строке, имя столбца – users)

Import-CSV .\users.csv -Header users | ForEach-Object {Add-AdGroupMember -Identity ‘TestADGroup’ -members $_.users}

Чтобы получить всех членов одной группы (groupA) и добавить их в другую группу (groupB), воспользуйтесь такой командой:

Get-ADGroupMember “GroupA” | Get-ADUser | ForEach-Object {Add-ADGroupMember -Identity “Group-B” -Members $_}

В том случае, если нужно скопировать в новую группу и членов всех вложенных групп (рекурсивно), нужно воспользоваться такой командой:

Get-ADGroupMember -Identity “GroupA” -Recursive | Get-ADUser | ForEach-Object {Add-ADGroupMember -Identity “GroupB” -Members $_}

Remove-ADGroupMember – удалить пользователей из группы

Для удаления пользователей из группы AD нужно использовать командует Remove-ADGroupMember. Удалим из группы двух пользователей:

Remove-ADGroupMember -Identity TestADGroup -Members user1, user2

Подтвердите удаление пользователей из группы:

Если нужно удалить из группы пользователей по списку из CSV файла, воспользуйтесь такой командой:

Import-CSV .\users.csv -Header users | ForEach-Object {Remove-ADGroupMember -Identity ‘TestADGroup’ -members $_.users}

Get-ADGroup – получить информацию о группе AD

Получить информацию о группе поможет командлет Get-ADGroup :

Get-ADGroup "TestADGroup"

Даная команда выводит информацию об основных атрибутах группы (DN, тип группы, имя, SID). Чтобы вывести значение всех атрибутов группы домена AD, выполните такую команду:

Get-ADGroup "TestADGroup" -properties *

Как вы видите, теперь стали отображаться такие атрибуты, как время создания и модификации группы, описание и т.д.

С помощью командлета Get-ADGroup можно найти все интересующие вас группы по определенному шаблону. Например, нужно найти все группы AD, в имени которых содержится фраза admins :

Get-ADGroup -LDAPFilter “(name=*admins*)” | Format-Table

Get-ADGroupMember – вывести список пользователей группы AD

Вывести на экран список пользователей группы:

Get-ADGroupMember "TestADGroup"

Чтобы оставить в результатах только имена пользователей, выполните:

Get-ADGroupMember "TestADGroup"| ft name

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

Get-ADGroupMember ‘server-admins" -recursive| ft name

Чтобы выгрузить список учетных записей, состоящих в определённой группе в CSV файл (для дальнейшего использования в Excel), выполните такую команду:

Get-ADGroupMember ‘server-admins" -recursive| ft samaccountname| Out-File c:\ps\admins.csv

Чтобы добавить в текстовый файл данные учетных записей пользователей в AD, воспользуемся командлетом . Например, помимо учетной записи нужно вывести должность и телефон пользователя группы:

Get-ADGroupMember -Identity ’server-admins’ -recursive| foreach { Get-ADUser $_ -properties title, OfficePhone|Select-Object title, OfficePhone }

(Get-ADGroupMember -Identity "domain admins").Count

Оказалось, что в группе «domain admins» у нас состоит 7 учетных записей администраторов.

Чтобы найти список пустых групп в определенном OU, воспользуйтесь такой командой:

Get-ADGroup -Filter * -Properties Members -searchbase “OU=Moscow,DC=corp,dc=winitpro,DC=ru” | where {-not $_.members} | select Name