Построение отчетов для Configuration Manager 2012 в SQL Server Reporting Services

  • Article

Довольно часто я слышу вопросы о том, как построить отчет с помощью Report Builder и Reporting Services. Тем, кто только начинает работать с ConfigMgr 2012 или переходит с 2007-й версии, поначалу тяжело и непривычно работать с новым инструментом построения отчетов. Что-ж, попробуем разобраться.

Предположим, нам нужен отчет, в котором мы сможем увидеть список компьютеров, на которых присутствует определённый файл. Также мы хотим, чтобы этот отчет позволял фильтровать по операционной системе и архитектуре компьютеры, попадающие в отчет. Предварительно, я настроил и выполнил инвентаризацию файлов на клиентах. Условие задачи слегка надумано, но в момент написания статьи, мне в голову ничего более жизненного не пришло.

Я предпочитаю начинать создание отчета с написания запроса в SQL Management Studio. Если нужен отчет с какой-либо информацией, касающейся клиентов, то с большой вероятностью для построения запроса понадобится использовать представление v_R_System. В этом представлении хранится базовая информация о компьютерах, известных ConfigMgr’у.

Вот запрос, с которого я начну:
SELECT Netbios_Name0, Operating_System_Name_and0
FROM v_R_System

 
Теперь у меня есть имя компьютера и название ОС. Далее мне нужны данные по архитектуре ОС. На TechNet есть перечень представлений и краткое описание https://technet.microsoft.com/en-us/library/dn581978.aspx   Описание довольно скудное и часто приходится самостоятельно заглядывать в представления, чтобы понять, какая информация там хранится. Это можно сделать, не создавая никаких запросов вручную. В management studio кликаем правой кнопкой на представлении и выбираем пункт меню Select Top 1000 Rows:


 
Покопавшись, я отыскал представление, хранящее данные об архитектуре и добавил его в свой запрос:
SELECT Netbios_Name0, Operating_System_Name_and0, SystemType0
FROM v_R_System AS SYS
INNER JOIN v_GS_COMPUTER_SYSTEM as COMPSYS ON SYS.Resourceid= COMPSYS.Resourceid


 
Далее нам нужно найти, где хранятся данные о найденных на клиентах файлах (Software Inventory). Довольно часто можно угадать, как примерно будет называться таблица или представление. Выполняем клик правой кнопкой по папке Views в SQL Server Management Studio и фильтруем список представлений по слову file:


 
В результатах находим представление v_GS_SoftwareFile – оно-то нам и нужно. Модифицируем запрос:

Select Netbios_Name0, Operating_System_Name_and0, SystemType0, FileName
FROM v_R_System as SYS
INNER JOIN v_GS_COMPUTER_SYSTEM AS COMPSYS ON Sys.resourceid=compsys.resourceid
INNER JOIN v_GS_SoftwareFile AS SF ON sys.ResourceID=SF.ResourceID
 

Список можно фильтровать по различным критериям, например, так:

SELECT Netbios_Name0, Operating_System_Name_and0, SystemType0, FileName
FROM v_R_System AS SYS
INNER JOIN v_GS_COMPUTER_SYSTEM AS COMPSYS ON Sys.resourceid=compsys.resourceid
INNER JOIN v_GS_SoftwareFile AS SF ON sys.ResourceID=SF.ResourceID
WHERE FileName like '%test_file.vbs%'
AND Operating_System_Name_and0 like '%Workstation%'
AND SystemType0 like 'x64%'

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

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

SELECT Netbios_Name0, Operating_System_Name_and0, Distinguished_Name0
FROM v_R_System AS SYS
INNER JOIN v_GS_COMPUTER_SYSTEM AS COMPSYS ON Sys.resourceid=compsys.resourceid
INNER JOIN v_GS_SoftwareFile AS SF ON sys.ResourceID=SF.ResourceID
WHERE FileName like '%test_file.vbs%' AND Operating_System_Name_and0 like '%workstation%' AND SystemType0 like 'x64%'
GROUP BY Netbios_Name0, Operating_System_Name_and0, Distinguished_Name0

 
Сам запрос почти готов, осталось только сделать отчет на его основе. Для этого открываем консоль ConfigMgr, переходим в раздел Monitoring и выделяем папку Reporting. Справа появится ссылка на Report Manager.


 
Переходим по ссылке. Заходим в папку ConfigMgr_T01, где T01 это ваш сайт-код. Тут, пожалуй, можно создать отдельную папку для нашего отчета: My Custom Reports. В папке запускаем Report Builder:


 
Ждем пока Report Builder загрузится:


 
Здесь нужно заметить, что для подключения к Reporting Services при разработке отчетов иногда приходится запускать Report Builder, Internet Explorer и даже SQL Management Tools с повышенными привилегиями. Если что-то не работает, как это ожидается или вы не видите какие-либо элементы интерфейса, объекты, которые ожидаете увидеть – пробуйте Run as Administrator. В данном случае, у меня консоль ConfigMgr запущена с повышенными привилегиями, а, следовательно, и окно IE у меня также открывается с повышенными привилегиями.

Создаем новый отчет, выбирая Table or Matrix Wizard:


 
Создаем новый Dataset, по-нашему это набор данных. А по сути это запрос, результат которого мы будем отображать в таблице отчета.


 
На следующей странице жмем Browse, заходим в папку ConfigMgr_<код сайта> и в самом конце списка выбираем уже существующий DataSource – источник данных, т.е. база данных, к которой будем подключаться.


 
Нажимаем Next и подключаемся, используя учетные данные текущего пользователя.


 
Дальше нам нужно сконструировать запрос. Это можно сделать двумя путями: с помощью дизайнера запроса или просто напечатать код. У нас есть готовый код, так что срузу жмем кнопку Edit as Text и вставляем текст запроса в поле. Тут же можно проверить запрос, нажав кнопку Run.


 
 
На следующей странице перетаскиваем все доступные поля в область Values:

 
На остальных страницах мастера оставляем все по умолчанию. После закрытия мастера имеем такую картину:


 
Сохраняем отчет на сервере. Впринципе отчет уже можно запускать, но я сначала предпочел дать колонкам удобоваримые имена, настроить их ширину и обозначить заголовок. Также неплохо было бы сделать сортировку по столбцам, остановлюсь на этом подробнее. Для этого кликаем правой кнопкой на заголовке столбца с именем компьютера, выбираем Text Box Properties:


 
Далее переходим в раздел Interactive Sorting и включаем сортировку:


 

Аналогично настраиваем сортировку для всех остальных столбцов.

Теперь разберемся с параметрами отчета. Сейчас мы фильтруем результат по жестко заданным критериям в запросе. Я хочу, чтобы пользователь имел возможность изменять эти критерии при запуске отчета. Для этого мне нужно создать параметры и наборы данных, из которых будут подгружаться списки возможных значений для параметров. Добавим параметр OSNAME, предварительно создав еще один набор данных, из которого будет подгружаться список его возможных значений:


 

SELECT distinct Operating_System_Name_and0 from v_R_System
WHERE Operating_System_Name_and0 is not null


 
 
Параметр готов, теперь его нужно задействовать в нашем основном запросе. Кликаем правой кнопкой по Dataset1 и редактируем запрос

 

 

Сейчас можно в очередной раз сохранить отчет и проверить как работает параметр. Он работает, но мне этого мало. В данном варианте мы можем выбрать только одну из операционных систем, а я бы хотел иметь возможность также посмотреть отчет по всем операционным системам сразу. Для этого нам понадобиться создать вспомогательный параметр и изменить запросы в OSNAME_Dataset и Dataset1 (основной). При использовании параметров нужно помнить, что их имена чувствительны к регистру.
 
 

Меняем запрос в OSNAME_Dataset:


 

Изменяем запрос в Dataset1


 
SELECT Netbios_Name0, Operating_System_Name_and0, Distinguished_Name0
FROM v_R_System AS SYS
INNER JOIN v_GS_COMPUTER_SYSTEM AS COMPSYS ON Sys.resourceid=compsys.resourceid
INNER JOIN v_GS_SoftwareFile AS SF ON sys.ResourceID=SF.ResourceID
WHERE FileName like '%test_file.vbs%'
AND (@OSNAME = @AllValueParameter OR  SYS.Operating_System_Name_and0=@OSNAME)
AND SystemType0 like '%x64%'
GROUP BY Netbios_Name0, Operating_System_Name_and0, Distinguished_Name0

Порядок параметров также имеет значение. В данном случае AllValueParameter должен быть первым.


 

Подправлю параметр OSNAME, чтобы он принимал  значение «ALL» в качестве значения по умолчанию. Можно явно указать ALL в Default Values:


 
Либо подставлять значение другого параметра:


 
В данном случае работать будут оба варианта, т.к. значение по умолчанию для параметра AllValueParameter также равно «ALL».
У нас есть отчет с одним параметром. Аналогично добавляем параметр SYSTEMTYPE (архитектура) с набором данных SYSTEMTYPE_Dataset, в котором будет список возможных значений для параметра.


 
SELECT @AllValueParameter AS SystemType0
UNION ALL
SELECT DISTINCT SystemType0
FROM v_GS_COMPUTER_SYSTEM
 
 


 
Добавляем параметр SYSTEMTYPE в наш основной запрос.


 
SELECT Netbios_Name0, Operating_System_Name_and0, Distinguished_Name0
FROM v_R_System AS SYS
INNER JOIN v_GS_COMPUTER_SYSTEM AS COMPSYS ON Sys.resourceid=compsys.resourceid
INNER JOIN v_GS_SoftwareFile AS SF ON sys.ResourceID=SF.ResourceID
WHERE FileName like '%test_file.vbs%'
AND (@OSNAME = @AllValueParameter OR  SYS.Operating_System_Name_and0=@OSNAME)
AND (@SYSTEMTYPE = @AllValueParameter OR  SystemType0 =@SYSTEMTYPE)
GROUP BY Netbios_Name0, Operating_System_Name_and0, Distinguished_Name0

На выходе получаем отчет такого вида:


 
Все работает, но мне этого все еще недостаточно. На этот раз я хочу, чтобы список доступных для выбора ОС менялся в зависимости от выбранной архитектуры. Этого можно добиться с помощью каскадирования параметров. Каскадирование позволяет динамически получать список возможных значений для одного параметра в зависимости от значения другого. В моем отчете я сначала хочу выбрать архитектуру ОС, а затем уже определиться с именем ОС. Список имен ОС должен формироваться динамически, в зависимости от выбранной архитектуры. Для этого требуется расположить параметры в следующем порядке:


 
И изменить запрос набора данных OSNAME_Dataset таким образом, чтобы фильтровать результат запроса по типу архитектуры. Для этого нам понадобится использовать данные из представления v_GS_COMPUTER_SYSTEM


 

SELECT @AllValueParameter AS Operating_System_Name_and0
UNION ALL
SELECT distinct Operating_System_Name_and0 FROM v_R_System AS SYS
INNER JOIN v_GS_Computer_System AS COMPSYS
ON SYS.ResourceID=COMPSYS.ResourceID
WHERE Operating_System_Name_and0 is not null
AND SystemType0=@SYSTEMTYPE

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

Точно так же, как для первого отчета, запускаем Report Builder делаем отчет на основе запроса:
SELECT FileName, FilePath
FROM v_R_System as SYS
INNER JOIN v_GS_SoftwareFile AS SF ON sys.ResourceID=SF.ResourceID
WHERE Netbios_Name0=@COMPUTERNAME AND FileName like '%test_file.vbs%'

Параметр COMPUTERNAME создался автоматически. В заголовке отчета подставим имя компьютера. Для этого кликаем правой кнопкой мыши на поле, где написано Click to add title, переходим в TextBox properties.


 

В поле Value вписываем [@COMPUTERNEME]


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


 
Имя компьютера в главном отчете будет кликабельным, так что я оформлю его в стиле гиперссылки:


 
Поздравляю, мы закончили и вот что получилось в итоге:

Хинты:

  • Чтобы не придумывать дизайн отчета каждый раз, можно создать пустой отчет, соответствующий вашим корпоративным стандартам и использовать его в качестве отправной точки при создании новых отчетов.
  • Создавать отчеты можно не только в Report Builder, есть еще такой инструмент как Business Intelligence Development Studio