Artigo
11/20/2015

Referência de instrução SQL para relatórios do Configuration Manager

Aplica-se a: System Center 2012 Configuration Manager, System Center 2012 Configuration Manager SP1, System Center 2012 R2 Configuration Manager

Muitas instruções úteis do Microsoft SQL Server podem ser usadas durante a criação de relatórios do System Center 2012 Configuration Manager, e elas são descritas brevemente nesta seção. Para acompanhar esta discussão, você deve ter um nível básico de conhecimento em instruções de consulta SQL e a capacidade de escrever consultas como a seguinte:

SELECT Name, Comment, CollectionID

FROM v_Collection

WHERE Name LIKE 'All Windows%'

ORDER BY Name

Para obter informações sobre como escrever consultas básicas, veja a documentação do SQL Server.

Funções de agregação

As funções de agregação (como SUM, AVG, COUNT, COUNT(*), MAX e MIN) geram valores de resumo em conjuntos de resultados de consulta. Uma função de agregação (com exceção dos processos COUNT(*)) processa todos os valores selecionados em uma única coluna para produzir um valor de único resultado. As função de agregação podem ser aplicadas a todas as linhas em uma exibição, a um subconjunto da exibição especificada por uma cláusula WHERE ou a um ou mais grupos de linhas na exibição. Quando uma função de agregação é aplicada, um valor único é gerado de cada conjunto de linhas.

Importante
Lembre-se de que os valores NULL não são incluídos nos resultados de agregação. Por exemplo, se você tiver 100 registros e 8 deles tiverem um valor de coluna NULL para a propriedade que estiver sendo contada, a contagem retornará somente 92 resultados.

Um exemplo de como usar a função de agregação COUNT(*) é exibido na consulta a seguir (do relatório predefinido Contar clientes para cada site) e no conjunto de resultados de exemplo.

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

SiteCode	SiteName	ReportingSiteCode	Contagem
ABC	Site ABC		928
123	123 Site	ABC	1010

Funções de data e hora

Muitos relatórios internos usam as funções de Data e Hora. As funções mais comuns usadas são GETDATE, DATEADD, DATEDIFF e DATEPART.

GETDATE ()

A função GETDATE produz a data e hora atuais no formato interno do SQL Server para valores do datetime. GETDATE assume o parâmetro () do NULL.

O seguinte exemplo resulta na data e hora atuais do sistema:

SELECT GETDATE()

(nenhum nome de coluna)
29/05/2005 10:10:03.001

DATEADD (datepart, número, data)

A função DATEADD retorna um novo valor datetime com base na adição de um intervalo à data especificada.

Datepart é o parâmetro que especifica em qual parte da data será retornado um novo valor (por exemplo, ano, mês, dia, hora, minuto e assim por diante), number é o valor usado para incrementar datepart e date é a data de início.

O seguinte exemplo resulta em uma data que está a dois dias de 29 de maio de 2005:

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

(nenhum nome de coluna)
31/05/2005 10:10:03.001

DATEDIFF (datepart, data de início, data de término)

A função DATEDIFF retorna o número de limites de data e hora cruzados entre duas datas especificadas.

Datepart é o parâmetro que especifica em qual parte da data será retornado um novo valor (por exemplo, ano, mês, dia, hora, minuto e assim por diante), startdate é a data de início e enddate é a data de término.

O seguinte exemplo resulta no número de minutos entre a primeira e a segunda datas:

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

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

(nenhum nome de coluna)
20118

DATEPART (datepart, data)

A função DATEPART retorna um inteiro que representa o datepart especificado da data especificada.

Datepart é o parâmetro que especifica em qual parte da data será retornado e date é a data especificada.

O seguinte exemplo resulta no mês da data especificada:

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

(nenhum nome de coluna)
5

Combinando funções de data e hora

É comum usar uma combinação de funções de Data e Hora nos relatórios do Configuration Manager.

O seguinte exemplo resulta na data e hora atuais (2005-05-29 10:10:03.001 neste exemplo) menos 100 dias:

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

(nenhum nome de coluna)
18/02/2005 10:10:03.001

Exemplo de consulta com uso de funções de data e hora

A consulta a seguir resulta na contagem total de mensagens de status para o período de um dia. Nesta consulta, as funções COUNT, GETDATE e DATEADD são usadas, bem como o operador lógico BETWEEN e as cláusulas GROUP BY e 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

Código do Site	MessageID	Contagem	Data de Término
ABC	500	190	29/05/2005 10:10:03.001
ABC	501	130	29/05/2005 10:10:03.001
ABC	502	190	29/05/2005 10:10:03.001
ABC	1105	85	29/05/2005 10:10:03.001
ABC	1106	5	29/05/2005 10:10:03.001
…	…	…	…

JUNÇÕES

Para criar relatórios efetivos no Configuration Manager, você precisa entender como unir diferentes exibições para obter os dados esperados. Há três tipos de junções: interna, externa e cruzada. Além disso, há três tipos de junções externas: esquerda, direita e completa. A autojunção utiliza qualquer uma das junções acima, mas une registros da mesma exibição.

Junções internas

Em uma junção interna, os registros das duas exibições são combinados e adicionados aos resultados de uma consulta somente se os valores dos campos unidos atenderem a alguns critérios especificados. Se você usar uma junção interna usando o ResourceID para unir as exibições v_R_System e v_GS_WORKSTATION_STATUS, o resultado seria uma lista de todos os sistemas e sua última data de verificação de hardware.

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

Nome da Máquina	Última verificação de hardware
Client1	29/05/2005 10:10:03.001
Client3	12/06/2005 09:28:11.110

Junções externas

Uma junção externa retorna todas as linhas das exibições unidas, independentemente se há ou não uma linha de correspondência entre elas. A cláusula ON complementa os dados em vez de filtrá-los. Os três tipos de junções externas (esquerda, direita e completa) indicam a principal fonte de dados. As junções externas podem ser particularmente úteis quando você tiver valores NULL em uma exibição.

Junções externas esquerdas

Ao usar uma junção externa esquerda para combinar duas exibições, todas as linhas na exibição da esquerda são incluídas nos resultados. Na consulta a seguir, as exibições v_R_System e v_GS_WORKSTATION_STATUS são unidas por meio da junção externa esquerda. A exibição v_R_System é a primeira exibição listada na consulta, tornando-se a exibição da esquerda. O resultado incluirá uma lista de todos os sistemas e sua última data de verificação de hardware. Ao contrário da junção interna, os sistemas que não foram examinados quanto ao hardware ainda serão listados com um valor NULL (como visto no conjunto de resultados).

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

Nome da Máquina	Última verificação de hardware
Client1	29/05/2005 10:10:03.001
Client2	NULL
Client3	12/06/2005 09:28:11.110

Junções externas direitas

Uma junção externa direita é conceitualmente igual a uma junção externa esquerda, com a exceção de que todas as linhas da exibição à direita são incluídas nos resultados.

Junção externa completa

Uma junção externa completa recupera todas as linhas de ambas as exibições unidas. Ele retorna todas as linhas emparelhadas em que a condição de junção é verdadeira, além das linhas não emparelhadas de cada exibição concatenada com linhas NULL da outra exibição. Você normalmente não desejará usar este tipo de junção externa.

União cruzada

Uma união cruzada retorna o produto de duas exibições, não a soma. Cada linha na exibição à esquerda é correspondida a cada linha na exibição à direita. É o conjunto de todas as possíveis combinações de linha, sem qualquer filtragem. No entanto, se você adicionar uma cláusula WHERE, uma junção cruzada funcionará como uma junção interna – ela usa a condição para filtrar todas as combinações de linha possíveis para aquelas que você deseja.

Autojunção

Uma autojunção usa qualquer um dos tipos de junção acima, mas é uma exibição que é unida em si mesma. Em diagramas de banco de dados, uma autojunção é chamada uma relação reflexiva.

Frase de palavra-chave NOT IN

Subconsultas com a frase de palavra-chave NOT IN são muito úteis para encontrar informações sobre um conjunto de dados que não atendam a determinados critérios. No exemplo a seguir, a consulta retorna o nome NetBIOS de todos os computadores que NÃO têm o Notepad.exe instalado. Você deve primeiro criar uma consulta que possa detectar todos os computadores com o arquivo selecionado instalado da seguinte maneira:

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'

Depois de confirmar que a primeira consulta exibe todos os computadores que têm Notepad.exe instalado, a seguinte instrução de subconsulta usará a frase de palavra-chave NOT IN para encontrar todos os nomes do computador que NÃO têm o arquivo Notepad.exe instalado:

SELECT DISTINCT Netbios_Name0

FROM v_R_System

WHERE Netbios_Name0 NOT IN

(SELECT DISTINCT v_R_System.Netbios_Name0