У коментарях до попередньої статті згадали про облік у 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 ― колиCreated;
• і в 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", "msExchCustomAttribut #"Розгорнутий елемент securityPrincipal" = Table.ExpandRecordColumn(#"Видалені стовпці", "securityPrincipal", ("sAMAccountName"), ("sAMAccountName")), #"Розгорнутий елемент top" = Table.ExpandRecordColumn(#" ", "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=uaпотрібно витягти безпосередньо назву відділу. Найпростіше це зробити з використанням роздільників на вкладці «Перетворення».

Витягуємо текст.

Як роздільники я використовую OU=і ,OU=. В принципі, достатньо і комою, але я перестраховуюсь.

Вводимо роздільники.

Тепер за допомогою фільтра можна відсікнути непотрібні OU, на кшталт заблокованих користувачів та Builtin, налаштувати сортування та завантажити дані до таблиці.

Вигляд підсумкової таблиці.

Швидкий звіт щодо складу робочих станцій, без впровадження агентів та іншої підготовки

Тепер спробуємо створити корисну таблицю, отримавши дані щодо комп'ютерів. Зробимо звіт за операційними системами, що використовуються компанією: для цього створимо запит, але в навігаторі цього разу виберемо computer.

Робимо запит щодо об'єкта computer.

Залишимо класи-колонки computerі topі розширимо їх:

• клас computerрозширимо, обравши cn, operatingSystem, operatingSystemServicePackі operatingSystemVersion;
• в класі topоберемо колиCreated.

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

За бажанням можна зробити звіт лише за серверними операційними системами. Наприклад, застосувати фільтр за атрибутом 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"

Не секрет, що, починаючи з першої версії PowerShell, Microsoft намагається зробити з нього основний інструмент. адміністрування Windows. І багато в чому це виходить! Сьогодні на простих прикладах, ми покажемо можливості PowerShell, які можна використовувати для отримання різної інформаціїпро користувачів Active Directory та їх атрибути.

Примітка. Раніше для отримання інформації про атрибути облікових записів користувачів AD доводилося використовувати різні інструменти: консоль ADUC (у тому числі), утиліту і т.п. Вибір інструменту зазвичай ґрунтувався на поставленій задачі та здібностях адміністратора у програмуванні.

PowerShell 2.0 з'явився спеціальний модуль для роботи з Active Directory - (представлений в Windows Server 2008 R2), командлети якого дозволяють виконувати різні маніпуляції з об'єктами каталогу AD. Для отримання інформації про користувачів домену Active Directory та їх властивості призначений командлет Get-ADUser. За допомогою командлета Get-ADUser можна отримати значення будь-якого атрибуту існуючого облікового запису користувача AD. Крім того, можна вказувати різні критерії вибірки та формувати списки користувачів домену та їх атрибутів.

У цьому прикладі ми покажемо, як за допомогою командлета PowerShell Get-ADUser отримати інформацію, коли останній раз змінювався пароля користувача і коли закінчується термін його дії.

Запускаємо вікно Powershll з правами адміністратора та імпортуємо модуль Active Directory командою:

Import-Module activedirectory

Порада. У Windows Server 2012 і вище цей пункт можна пропустити, оскільки модуль PowerShell Active Directory підключено за замовчуванням.

У клієнтських ОС (в тій же 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).

Повний список всіх аргументів командлета Get-ADUser можна отримати так:

Help Get-ADUser

Щоб вивести список усіх облікових записів домену, виконаємо команду:

Get-ADUser -filter *

Формат списку, що повертається, не дуже зручний для використання, виводиться лише деякі основні 10 з більш ніж 120 атрибутів і властивостей облікових записів користувачів (DN, SamAccountName, Name, UPN і т.д) крім того, ми бачимо, що інформація про час останньої зміни пароля відсутня .

Щоб вивести повну інформаціюпро всі доступні атрибути користувача tuser, виконаємо команду:

Get-ADUser -identity tuser -properties *

Отже, ми бачимо повний списокатрибутів користувача AD та їх значень, асоційованих з обліковим записом користувача. Далі ми перейдемо до форматування виведення командлету Get-ADUser, щоб відображалися потрібні нам поля. Нас цікавлять атрибути:

• PasswordExpired
• PasswordLastSet
• PasswordNeverExpires

Виконаємо команду:

Get-ADUser tuser -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires

Тепер дані користувача мають інформацію про дату зміни пароля і часу, коли він прострочиться. Представимо інформацію у більш зручному табличному вигляді:

Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires

Щоб вивести дані користувачів із певної OU, скористаємося параметром SearchBase:

Get-ADUser -SearchBase 'OU=Moscow,DC=winitpro,DC=loc' -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires

Результат виконання команди можна вивантажити до текстового файлу:

Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires > C:\temp\users.txt

Або CSV, який надалі буде зручно експортувати в Excel (додатково за допомогою sort-objectвідсортуємо таблицю по стовпцю PasswordLastSet, а також додамо умову where– ім'я користувача має містити рядок «Dmitry»):

Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | where ($_.name -like "*Dmitry*") | sort-object PasswordLastSet | select-object Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires | Export-csv -path c:\temp\user-password-expires-2015.csv

Таким чином, можна побудувати таблицю з будь-якими необхідними атрибутами користувачів Active Directory.

Для отримання списку облікових записів користувачів AD за певною ознакою використовується параметр Filter. Як аргументи цього параметра можна вказати значення певних атрибутів користувачів Active Directory, в результаті командлет Get-ADUser буде застосований для користувачів, які підпадають під критерії фільтра.

Виведення користувачів AD, ім'я яких починається з Roman:

Get-ADUser -filter (name -like "Roman*")

Get-ADUser-Filter (SamAccountName -like "*") | Measure-Object

Список усіх активних (не заблокованих) облікових записів AD:

Get-ADUser-Filter (Enabled -eq "True") | Select-Object SamAccountName,Name,Surname,GivenName | Format-Table

Список облікових записів зі строком дії пароля, що минув.

Get-ADUser -filter (Enabled -eq $True) -properties passwordExpired | where ($_.PasswordExpired)

Список активних облікових записів з поштовими адресами:

Get-ADUser -Filter ((mail -ne "null") -and (Enabled -eq "true")) -Properties Surname,GivenName,mail | Select-Object Name,Surname,GivenName,mail | Format-Table

Завдання: для списку облікових записів, які зберігаються в текстовому файлі(по одному обліку в рядку) потрібно отримати телефон користувача AD і вивантажити інформацію в текстовий csv файл (можна легко імпортувати в Esxel).

Import-Csv c:\ps\usernsme_list.csv | ForEach ( Get-ADUser -identity $_.user -Properties Name, telephoneNumber | Select Name, telephoneNumber | Export-CSV c:\ps\export_ad_list.csv -Append -Encoding UTF8 )

Наступний приклад дозволяє вивантажити адресну книгу підприємства у вигляді 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:temp\mail_list.csv

Користувачі, які не змінювали свій пароль протягом останніх 90 днів:

$90_Days = (Get-Date).adddays(-90) Get-ADUser -filter ((passwordlastset -le $90_days))

$user = Get-ADUser winadmin -Properties thumbnailPhoto $user.thumbnailPhoto | Set-Content winadmin.jpg -Encoding byte

Список груп, у яких складається обліковий запискористувача

Get-AdUser winadmin -Properties memberof | Select memberof -expandproperty memberof

Сьогодні ми спробуємо вивантажити список усіх користувачів у окремий файлз 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 для 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ми побачимо знаки питаннязамість російських букв.

Під час перегляду файлу дані експортуються в один рядок і таким чином не підходять для читання. Щоб це змінити, нам необхідно виконати такі дії: