Informations de référence sur les instructions SQL pour les rapports Configuration Manager

De nombreuses instructions microsoft SQL Server utiles peuvent être utilisées lors de la création de rapports Configuration Manager, et elles sont brièvement décrites dans cette section. Pour suivre cette discussion, vous devez disposer d’un niveau de connaissances de base des instructions de requête SQL et de la capacité à écrire des requêtes telles que les suivantes :

SELECT Name, Comment, CollectionID

FROM v_Collection

WHERE Name LIKE 'All Windows%'

ORDER BY Name

Pour plus d’informations sur l’écriture de requêtes de base, consultez votre documentation SQL Server.

Fonctions d’agrégation

Les fonctions d’agrégation (telles que SUM, AVG, COUNT, COUNT(*), MAX et MIN) génèrent des valeurs récapitulatives dans les jeux de résultats de requête. Une fonction d’agrégation (à l’exception de COUNT(*)) traite toutes les valeurs sélectionnées dans une seule colonne pour produire une seule valeur de résultat. Les fonctions d’agrégation peuvent être appliquées à toutes les lignes d’une vue, à un sous-ensemble de l’affichage spécifié par une clause WHERE ou à un ou plusieurs groupes de lignes dans la vue. Lorsqu’une fonction d’agrégation est appliquée, une valeur unique est générée à partir de chaque ensemble de lignes.

Importante

N’oubliez pas que les valeurs NULL ne sont pas incluses dans les résultats agrégés. Par exemple, si vous avez 100 enregistrements et que 8 d’entre eux ont une valeur de colonne NULL pour la propriété que vous comptez, le nombre retourne uniquement 92 résultats.

Un exemple d’utilisation de la fonction d’agrégation COUNT(*) est affiché dans la requête suivante (à partir du rapport count clients pour chaque rapport prédéfini de site) et un exemple de jeu de résultats.

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

Code de site	SiteName	ReportingSiteCode	Count
ABC	ABC Site		928
123	123 Site	ABC	1010

Fonctions de date et d’heure

De nombreux rapports intégrés utilisent les fonctions Date et Heure. Les fonctions les plus courantes utilisées sont GETDATE, DATEADD, DATEDIFF et DATEPART.

GETDATE ()

La fonction GETDATE génère la date et l’heure actuelles dans SQL Server format interne pour les valeurs datetime. GETDATE prend le paramètre NULL ().

L’exemple suivant indique la date et l’heure système actuelles :

SELECT GETDATE()

(aucun nom de colonne)
2005-05-29 10:10:03.001

DATEADD (datepart, number, date)

La fonction DATEADD retourne une nouvelle valeur datetime basée sur l’ajout d’un intervalle à la date spécifiée.

Datepart est le paramètre qui spécifie la partie de la date à laquelle renvoyer une nouvelle valeur (par exemple, année, mois, jour, heure, minute, etc.), nombre est la valeur utilisée pour incrémenter datepart et date est la date de début.

L’exemple suivant génère une date de deux jours à partir du 29 mai 2005 :

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

(aucun nom de colonne)
2005-05-31 10:10:03.001

DATEDIFF (datepart , startdate , enddate)

La fonction DATEDIFF retourne le nombre de limites de date et d’heure franchies entre deux dates spécifiées.

Datepart est le paramètre qui spécifie la partie de la date à laquelle renvoyer une nouvelle valeur (par exemple, année, mois, jour, heure, minute, etc.), startdate est la date de début, enddate est la date de fin.

L’exemple suivant indique le nombre de minutes entre la première et la deuxième dates :

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

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

(aucun nom de colonne)
20118

DATEPART (datepart , date)

La fonction DATEPART retourne un entier représentant le datepart spécifié de la date spécifiée.

Datepart est le paramètre qui spécifie la partie de la date à retourner, et date est la date spécifiée.

L’exemple suivant montre comment afficher le mois à la date spécifiée :

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

(aucun nom de colonne)
5

Combinaison des fonctions Date et Heure

Il est courant d’utiliser une combinaison des fonctions Date et Heure dans Configuration Manager rapports.

L’exemple suivant indique la date et l’heure actuelles (2005-05-29 10:10:03.001 dans cet exemple) moins 100 jours :

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

(aucun nom de colonne)
2005-02-18 10:10:03.001

Exemple de requête utilisant les fonctions Date et Heure

La requête suivante génère le nombre total de messages status pour une période d’un jour. Dans cette requête, les fonctions COUNT, GETDATE et DATEADD sont utilisées, ainsi que l’opérateur logique BETWEEN et les clauses GROUP BY et 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

Site Code	MessageID	Count	End Date
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

REJOINT

Pour créer des rapports efficaces dans Configuration Manager, vous devez comprendre comment joindre différents affichages afin d’obtenir les données attendues. Il existe trois types de jointures : interne, externe et croisée. En outre, il existe trois types de jointures externes : gauche, droite et complète. La jointure auto utilise l’une des jointures ci-dessus, mais joint les enregistrements de la même vue.

Jointures internes

Dans une jointure interne, les enregistrements de deux vues sont combinés et ajoutés aux résultats d’une requête uniquement si les valeurs des champs joints répondent à certains critères spécifiés. Si vous utilisez une jointure interne à l’aide du ResourceID pour joindre les vues v_R_System et v_GS_WORKSTATION_STATUS , le résultat est une liste de tous les systèmes et leur date de dernière analyse matérielle.

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

Nom de l'ordinateur	Dernière analyse HW
Client1	2005-05-29 10:10:03.001
Client3	2005-06-12 09:28:11.110

Jointures externes

Une jointure externe retourne toutes les lignes des vues jointes, qu’il y ait ou non une ligne correspondante entre elles. La clause ON complète les données au lieu de les filtrer. Les trois types de jointures externes (gauche, droite et complète) indiquent la source des données main. Les jointures externes peuvent être particulièrement utiles lorsque vous avez des valeurs NULL dans une vue.

Jointures externes gauches

Lorsque vous utilisez une jointure externe gauche pour combiner deux vues, toutes les lignes de la vue de gauche sont incluses dans les résultats. Dans la requête suivante, les vues v_R_System et v_GS_WORKSTATION_STATUS sont jointes à l’aide de la jointure externe gauche. La vue v_R_System est la première vue répertoriée dans la requête, ce qui en fait la vue de gauche. Le résultat inclut une liste de tous les systèmes et leur date de dernière analyse matérielle. Contrairement à la jointure interne, les systèmes qui n’ont pas été analysés pour le matériel sont toujours répertoriés avec une valeur NULL (comme indiqué dans le jeu de résultats).

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

Nom de l'ordinateur	Dernière analyse HW
Client1	2005-05-29 10:10:03.001
Client2	NULL
Client3	2005-06-12 09:28:11.110

Jointures externes droites

Une jointure externe droite est conceptuellement identique à une jointure externe gauche, sauf que toutes les lignes de la vue de droite sont incluses dans les résultats.

Jointure externe complète

Une jointure externe complète récupère toutes les lignes des deux vues jointes. Elle retourne toutes les lignes jumelées pour lesquelles la condition de jointure est true, ainsi que les lignes non appariées de chaque vue concaténées avec les lignes NULL de l’autre vue. En règle générale, vous ne souhaitez pas utiliser ce type de jointure externe.

Jointure croisée

Une jointure croisée retourne le produit de deux vues, et non la somme. Chaque ligne de l’affichage de gauche est mise en correspondance avec chaque ligne de l’affichage de droite. Il s’agit de l’ensemble de toutes les combinaisons de lignes possibles, sans aucun filtrage. Toutefois, si vous ajoutez une clause WHERE, une jointure croisée fonctionne comme une jointure interne, elle utilise la condition pour filtrer toutes les combinaisons de lignes possibles jusqu’à celles souhaitées.

Auto-jointure

Une jointure automatique utilise l’un des types de jointure ci-dessus, mais il s’agit d’une vue qui est jointe à elle-même. Dans les diagrammes de base de données, une jointure automatique est appelée relation réflexive.

EXPRESSION NOT IN mot clé

Les sous-requêtes avec l’expression mot clé NOT IN sont très utiles pour trouver des informations sur un ensemble de données qui ne répondent pas à certains critères. Dans l’exemple suivant, la requête retourne le nom NetBIOS de tous les ordinateurs qui n’ont PAS Notepad.exe installé. Vous devez d’abord créer une requête capable de détecter tous les ordinateurs sur lesquels le fichier sélectionné est installé comme suit :

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'

Après avoir vérifié que la première requête affiche tous les ordinateurs sur lesquels Notepad.exe sont installés, l’instruction de sous-requête suivante utilise l’expression NOT IN mot clé pour rechercher tous les noms d’ordinateurs sur lesquels le fichier Notepad.exe n’est pas installé :

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

Voir aussi

Utiliser le Concepteur de requêtes pour écrire des instructions SQL de rapport pour les rapports Configuration Manager