В комментариях к предыдущей статье вспомнили про учет в 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 стоит удалить ― в отображении он не нужен совершенно. И нажимаем «Загрузить и закрыть».
Получилась табличка, которую осталось совсем немного довести до ума. Например, переименовать столбцы в что-то удобочитаемое. И настроить автоматическое обновление данных.
Автоматическое обновление при открытии таблицы или по таймауту настраивается во вкладке «Данные» в «Свойствах».
Настройка обновления данных.После того, как настройка обновления будет завершена, можно смело отдавать таблицу сотрудникам отдела персонала или службе безопасности ― пусть знают, кто и когда входил в систему.
Код запроса на языке «М» под спойлером.
( под спойлером )Создаем адресную книгу, или что делать, когда корпоративный портал с 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 довольно велико. И есть к чему стремиться.
Код запроса под спойлером.
( Под спойлером 2 )Но и это еще не всеНадо отметить, что Excel умеет составлять не только любимые бухгалтерией таблички. При умелом подходе ему по плечу и аналитика многомерных данных (OLAP-кубы), и
решение системы уравнений с помощью матриц. А для тех, у кого на стенке пылится сертификат от Microsoft – есть вариант заморочиться даже с
3D-играми. Не Doom конечно, но вечер точно займет.
Источник:
https://m.habrahabr.ru/company/pc-administrator/blog/350582/![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Билл Стюарт, scripting guru, в своей статье на WindowsITPro описывает те проблемы, с которыми ему пришлось столкнуться при написании Powershell-скрипта, который бы выводил членство пользователей в группах Active Directory.
Источник 1: Официальный сайт ESET: 
Антон Тюрин,
