Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке»

в 9:00, , рубрики: Excel, Блог компании Сервер Молл, запросы к ad, отчеты, Серверное администрирование, системное администрирование, Терминология IT

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 1

В комментариях к предыдущей статье вспомнили про учет в 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».

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 2
Указываем источник данных.

Понадобится выбрать название домена, указать необходимые данные для подключения. Далее выберем тип объектов, в этом примере ― user. Справа в окне предпросмотра запрос уже выполняется, показывая предварительный вид данных.

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 3
Подготавливаем запрос, любуемся предпросмотром.

Предварительно запрос стоит подготовить, нажав кнопку «изменить» и выбрав нужные колонки. По сути эти колонки ― это классы Каждый из них содержит набор определенных атрибутов объекта Active Directory, кроме основной колонки displayName, которая сама является атрибутом. Я остановлюсь на классах user, person, top и securityPrincipal. Теперь необходимо выбрать нужные атрибуты из каждого класса с помощью «расширения» ― значок с двумя стрелочками у заголовка колонки:

  • класс user расширим, выбрав lastLogonTimestamp и userAccountControl;
  • в person выберем telephoneNumber;
  • в topwhenCreated;
  • и в securityPrincipalSamAccountName.

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 4
Расширяем запрос.

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 5
Применяем фильтр.

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

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

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

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 6

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

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

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

let
 Источник = ActiveDirectory.Domains("domain.ru"),
 domain.ru = Источник{[Domain="domain.ru"]}[#"Object Categories"],
 user1 = domain.ru{}[Objects],
 #"Удаленные столбцы" = 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 ([userAccountControl] = 512 or [userAccountControl] = 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 нужно извлечь непосредственно название отдела. Проще всего это сделать с использованием разделителей на вкладке «Преобразование».

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 7
Извлекаем текст.

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 8
Вводим разделители.

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 9
Вид итоговой таблицы.

Быстрый отчет по составу рабочих станций, без внедрения агентов и прочей подготовки

Теперь попробуем создать полезную таблицу, получив данные по компьютерам. Сделаем отчет по используемым компанией операционным системам: для этого создадим запрос, но в навигаторе на этот раз выберем computer.

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 10
Делаем запрос по объекту computer.

Оставим классы-колонки computer и top и расширим их:

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 11
Расширенный запрос.

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 12
Извлекаем год из времени ввода компьютера в домен.

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 13
Настройки полей сводной таблицы.

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 14
Сводная таблица по компьютерам в AD.

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

Excel вместо PowerShell: запросы к AD и системные отчеты «на коленке» - 15
Круговая диаграмма.

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

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

let
 Источник = ActiveDirectory.Domains("domain.ru"),
 domain.ru = Источник{[Domain="domain.ru"]}[#"Object Categories"],
computer1 = domain.ru{}[Objects],
 #"Удаленные столбцы" = 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"

Но и это еще не все

Надо отметить, что Excel умеет составлять не только любимые бухгалтерией таблички. При умелом подходе ему по плечу и аналитика многомерных данных (OLAP-кубы), и решение системы уравнений с помощью матриц. А для тех, у кого на стенке пылится сертификат от Microsoft – есть вариант заморочиться даже с 3D-играми. Не Doom конечно, но вечер точно займет.

А что вы думаете про Excel как инструмент администратора? Доводилось использовать что-то из описанного?

Автор: Tri-Edge

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js