Справочник по инструкции SQL для отчетов Configuration Manager

При создании отчетов Configuration Manager можно использовать множество полезных инструкций Microsoft SQL Server, которые кратко описаны в этом разделе. Чтобы следовать этому обсуждению, вы должны иметь базовый уровень знаний инструкции SQL query и возможность писать запросы, такие как:

SELECT Name, Comment, CollectionID

FROM v_Collection

WHERE Name LIKE 'All Windows%'

ORDER BY Name

Сведения о написании базовых запросов см. в документации по SQL Server.

Агрегатные функции

Агрегатные функции (такие как SUM, AVG, COUNT, COUNT(*), MAX и MIN) создают сводные значения в результирующих наборах запросов. Агрегатная функция (за исключением COUNT(*)) обрабатывает все выбранные значения в одном столбце для получения одного результирующих значений. Агрегатные функции могут применяться ко всем строкам в представлении, к подмножествию представления, указанному в предложении WHERE, или к одной или нескольким группам строк в представлении. При применении агрегатной функции из каждого набора строк создается одно значение.

Важно!

Имейте в виду, что значения NULL не включаются в статистические результаты. Например, если у вас есть 100 записей и 8 из них имеют значение столбца NULL для подсчитываемого свойства, счетчик вернет только 92 результата.

Пример использования агрегатной функции COUNT(*) показан в следующем запросе (из счетчика клиентов для каждого предопределенного отчета сайта) и в примере результированного набора.

SELECT v_Site.SiteCode, v_Site.SiteName, v_Site.ReportingSiteCode,

Count(SMS_Installed_Sites0) AS 'Count'

FROM v_Site, v_RA_System_SMSInstalledSites InsSite

WHERE v_Site.SiteCode = InsSite.SMS_Installed_Sites0

GROUP BY SiteCode, SiteName, ReportingSiteCode

ORDER BY SiteCode

Код сайта	Имя_сайта	ReportingSiteCode	Count
АЗБУКА	Сайт ABC		928
123	Сайт 123	АЗБУКА	1010

Функции даты и времени

Во многих встроенных отчетах используются функции даты и времени. Наиболее распространенными функциями являются GETDATE, DATEADD, DATEDIFF и DATEPART.

GETDATE ()

Функция GETDATE создает текущую дату и время в SQL Server внутреннем формате для значений datetime. GETDATE принимает параметр NULL ().

В следующем примере показано, как показано текущее системное время и дата:

SELECT GETDATE()

(без имени столбца)
2005-05-29 10:10:03.001

DATEADD (datepart, number, date)

Функция DATEADD возвращает новое значение datetime на основе добавления интервала к указанной дате.

Datepart — это параметр, указывающий, в какой части даты возвращается новое значение (например, год, месяц, день, час, минута и т. д.), number — значение, используемое для приращения datepart, а date — начальная дата.

Следующий пример приводит к дате, которая составляет два дня с 29 мая 2005 г.:

SELECT DATEADD([day], 2, '2005-05-29 10:10:03.001')

(без имени столбца)
2005-05-31 10:10:03.001

DATEDIFF (datepart, startdate, enddate)

Функция DATEDIFF возвращает число границ даты и времени, пересекаемых между двумя указанными датами.

Datepart — это параметр, указывающий, в какой части даты возвращается новое значение (например, год, месяц, день, час, минута и т. д.), startdate — начальная дата, enddate — конечная дата.

В следующем примере показано количество минут между первой и второй датами:

SELECT DATEDIFF (minute, '2005-05-29 10:10:03.001',

'2005-06-12 09:28:11.111')

(без имени столбца)
20118

DATEPART (datepart , date)

Функция DATEPART возвращает целое число, представляющее указанную часть даты указанной даты.

Datepart — это параметр, указывающий, в какой части возвращаемой даты, а date — указанная дата.

В следующем примере показано, как месяц в указанной дате:

SELECT DATEPART (month, '2005-05-29 10:10:03.001')

(без имени столбца)
5

Объединение функций даты и времени

Обычно в отчетах Configuration Manager используется сочетание функций даты и времени.

В следующем примере приведены текущие дата и время (2005-05-29 10:10:03.001 в этом примере) минус 100 дней:

SELECT DATEADD([day], - 100, GETDATE())

(без имени столбца)
2005-02-18 10:10:03.001

Пример запроса с использованием функций даты и времени

Следующий запрос приводит к общему количеству сообщений о состоянии за один день. В этом запросе используются функции COUNT, GETDATE и DATEADD, а также логический оператор BETWEEN и предложения GROUP BY и ORDER BY.

SELECT SiteCode, MessageID, COUNT(MessageID) AS [count],

GETDATE() AS [End Date]

FROM vStatusMessages

WHERE ([Time] BETWEEN DATEADD([day], -1, GETDATE()) AND GETDATE())

AND (MessageID BETWEEN '0' AND '10000')

GROUP BY SiteCode, MessageID

ORDER BY SiteCode, MessageID

Код сайта	MessageID	Count	Дата окончания
АЗБУКА	500	190	2005-05-29 10:10:03.001
АЗБУКА	501	130	2005-05-29 10:10:03.001
АЗБУКА	502	190	2005-05-29 10:10:03.001
АЗБУКА	1105	85	2005-05-29 10:10:03.001
АЗБУКА	1106	5	2005-05-29 10:10:03.001

СОЕДИНЕНИЯ

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

Внутренние соединения

Во внутреннем соединении записи из двух представлений объединяются и добавляются в результаты запроса только в том случае, если значения объединенных полей соответствуют определенным условиям. Если вы используете внутреннее соединение с помощью ResourceID для присоединения v_R_System и v_GS_WORKSTATION_STATUS представлений, результатом будет список всех систем и дата последней проверки оборудования.

SELECT v_R_System.Netbios_Name0 AS MachineName,

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]

FROM v_R_System INNER JOIN v_GS_WORKSTATION_STATUS

ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

Имя компьютера	Последнее сканирование HW
Client1	2005-05-29 10:10:03.001
Клиент3	2005-06-12 09:28:11.110

Внешние соединения

Внешнее соединение возвращает все строки из объединенных представлений независимо от того, есть ли между ними соответствующая строка. Предложение ON дополняет данные, а не фильтрует их. Три типа внешних соединений (левое, правое и полное) указывают на источник данных main. Внешние соединения могут быть особенно полезны при наличии значений NULL в представлении.

Слева внешние соединения

При использовании левого внешнего соединения для объединения двух представлений все строки в левом представлении включаются в результаты. В следующем запросе представления v_R_System и v_GS_WORKSTATION_STATUS объединяются с помощью левого внешнего соединения. Представление v_R_System является первым представлением, перечисленным в запросе, что делает его левым. Результат будет включать список всех систем и дату последней проверки оборудования. В отличие от внутреннего соединения, системы, которые не были проверены на наличие оборудования, по-прежнему будут перечислены со значением NULL (как показано в результирующем наборе).

SELECT v_R_System.Netbios_Name0 AS MachineName,

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]

FROM v_R_System LEFT OUTER JOIN v_GS_WORKSTATION_STATUS

ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

Имя компьютера	Последнее сканирование HW
Client1	2005-05-29 10:10:03.001
Client2	NULL
Клиент3	2005-06-12 09:28:11.110

Правые внешние соединения

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

Полное внешнее соединение

Полное внешнее соединение извлекает все строки из обоих объединенных представлений. Он возвращает все парные строки, в которых условие соединения равно true, а также неспаренные строки из каждого представления, объединенные со строками NULL из другого представления. Обычно вы не хотите использовать этот тип внешнего соединения.

Перекрестные соединения

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

Самостоятельное присоединение

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

ФРАЗА NOT IN ключевое слово

Вложенные запросы с ключевое слово фразой NOT IN очень полезны для поиска сведений о наборе данных, которые не соответствуют определенным критериям. В следующем примере запрос возвращает NetBIOS-имя всех компьютеров, на которых не установлены Notepad.exe. Сначала необходимо создать запрос, который может обнаружить все компьютеры, на которых установлен выбранный файл, следующим образом:

SELECT DISTINCT v_R_System.Netbios_Name0

FROM v_R_System INNER JOIN v_GS_SoftwareFile

ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)

WHERE v_GS_SoftwareFile.FileName = 'Notepad.exe'

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

SELECT DISTINCT Netbios_Name0

FROM v_R_System

WHERE Netbios_Name0 NOT IN

(SELECT DISTINCT v_R_System.Netbios_Name0

FROM v_R_System INNER JOIN v_GS_SoftwareFile

ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)

WHERE v_GS_SoftwareFile.FileName = 'Notepad.exe')

ORDER by Netbios_Name0

См. также

Использование конструктора запросов для написания инструкций SQL отчетов для Configuration Manager отчетов