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.

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.

System_CAPS_importantImportante

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

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.

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

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

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

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

É 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

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

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.

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

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.

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

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.

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.

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.

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.

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

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

Mostrar: