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

Pode utilizar várias instruções do Microsoft SQL Server ao criar relatórios do System Center 2012 Configuration Manager. Nesta secção, pode encontrar uma breve descrição das mesmas. Para seguir este debate, deverá ter um nível básico de conhecimento sobre a instrução de consulta SQL e a capacidade de escrever consultas como as seguintes:

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, consulte a documentação do SQL Server.

Funções de Agregação

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

Importante
Tenha em atenção que os valores NULL não são incluídos nos resultados de agregação. Por exemplo, se tiver 100 registos e 8 dos quais tiverem um valor de coluna NULL para a propriedade que está a contar, a contagem devolverá apenas 92 resultados.

Pode encontrar um exemplo de como utilizar a função de agregação COUNT(*) na seguinte consulta (no 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

Código do Site	SiteName	ReportingSiteCode	Contagem
ABC	Site ABC		928
123	Site 123	ABC	1010

Funções de Data e Hora

Muitos relatórios incorporados utilizam as funções de Data e Hora. As funções mais comuns utilizadas 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 datetime. GETDATE assume o parâmetro NULL ().

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

SELECT GETDATE()

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

DATEADD (datepart, number, date)

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

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

O exemplo seguinte resulta numa data dois dias após 29 de maio de 2005:

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

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

DATEDIFF (datepart , startdate , enddate)

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

Datepart é o parâmetro que especifica em que parte da data devolver um valor novo (por exemplo, ano, mês, dia, hora, minuto e assim consecutivamente), startdate é a data de início e enddate é a data de fim.

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

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

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

(nenhum nome de coluna)
20118

DATEPART (datepart , date)

A função DATEPART devolve um número inteiro que representa o datepart especificado da data especificada.

Datepart é o parâmetro que especifica em que parte da data devolver e date é a data especificada.

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

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

(nenhum nome de coluna)
5

Combinar Funções de Data e Hora

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

O exemplo seguinte 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)
2005-02-18 10:10:03.001

Consulta de Exemplo com Funções de Data e Hora

A consulta seguinte resulta na contagem total de mensagens de estado correspondentes ao período de um dia. Nesta consulta, são utilizadas as funções COUNT, GETDATE e DATEADD, 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 Fim
ABC	500	190	2005-05-29 10:10:03.001
ABC	501	130	2005-05-29 10:10:03.001
ABC	502	190	2005-05-29 10:10:03.001
ABC	1105	85	2005-05-29 10:10:03.001
ABC	1106	5	2005-05-29 10:10:03.001
…	…	…	…

ASSOCIAÇÕES

Para criar relatórios eficientes no Configuration Manager, é necessário compreender como associar diferentes vistas para obter os dados esperados. Existem três tipos de associações: interna, externa e cruzada. Além disso, existem três tipos de associações externas: à esquerda, à direita e completa. A associação automática utiliza qualquer uma das associações acima, mas associa registos da mesma vista.

Associações Internas

Numa associação interna, os registos de duas vistas são combinados e adicionados aos resultados de uma consulta apenas se os valores dos campos associados corresponderem a determinados critérios especificados. Se utilizar uma associação interna através de ResourceID para associar as vistas v_R_System e v_GS_WORKSTATION_STATUS, o resultado será uma lista de todos os sistemas e da respetiva data da última análise 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 do Computador	Última Análise HW
Client1	2005-05-29 10:10:03.001
Client3	2005-06-12 09:28:11.110

Associações Externas

Uma associação externa devolve todas as linhas das vistas associadas independentemente de existir uma linha correspondente entre elas. A cláusula ON complementa os dados em vez de os filtrar. Os três tipos de associações externas (à esquerda, à direita e completa) indicam a origem dos dados principais. As associações externas podem ser particularmente úteis quando tem valores NULL numa vista.

Associações Externas À Esquerda

Quando utiliza uma associação externa à esquerda para combinar duas vistas, todas as linhas na vista da esquerda são incluídas nos resultados. Na consulta seguinte, as vistas v_R_System e v_GS_WORKSTATION_STATUS são associadas através da associação externa à esquerda. A vista v_R_System é a primeira vista listada na consulta, pelo que esta é a vista da esquerda. O resultado incluirá uma lista de todos os sistemas e a respetiva data da última análise de hardware. Ao contrário da associação interna, os sistemas que não tenham sido analisados relativamente a hardware continuarão a ser listados com um valor NULL (conforme verificado 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 do Computador	Última Análise HW
Client1	2005-05-29 10:10:03.001
Client2	NULL
Client3	2005-06-12 09:28:11.110

Associações Externas À Direita

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

Associação Externa Completa

Uma associação externa completa obtém todas as linhas de ambas as vistas associadas. Devolve todas as linhas emparelhadas em que a condição de associação seja verdadeira, bem como as linhas desemparelhadas de cada vista concatenada com linhas NULL da outra vista. Normalmente, não quererá utilizar este tipo de associação externa.

Associação Cruzada

Uma associação cruzada devolve o produto de duas vistas, não a soma. Cada linha na vista da esquerda é correspondida a cada linha na vista da direita. É o conjunto de todas as possíveis combinações de linhas, sem qualquer filtragem. No entanto, se adicionar uma cláusula WHERE, uma associação cruzada funciona como uma associação interna, na medida em que utiliza a condição para filtrar todas as possíveis combinações de linhas até obter as que pretende.

Associação Automática

A associação automática utiliza qualquer um dos tipos de associação acima, com a diferença de ser uma vista que está associada a si própria. Nos diagramas de base de dados, uma associação automática denomina-se relação reflexiva.

Expressão de Palavra-chave NOT IN

As subconsultas com a expressão de palavra-chave NOT IN são muito úteis para encontrar informações sobre um conjunto de dados que não satisfaça determinados critérios. No exemplo seguinte, a consulta devolve o nome NetBIOS de todos os computadores que NÃO tenham o Notepad.exe instalado. Primeiro, tem de criar uma consulta que detete todos os computadores que tenham o ficheiro selecionado instalado da seguinte forma:

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 apresenta todos os computadores com o Notepad.exe instalado, a seguinte instrução de subconsulta utilizará a expressão de palavra-chave NOT IN para localizar todos os nomes de computador que NÃO tenham o ficheiro Notepad.exe instalado:

SELECT DISTINCT Netbios_Name0

FROM v_R_System

WHERE Netbios_Name0 NOT IN

(SELECT DISTINCT v_R_System.Netbios_Name0