Configuration Manager 報表的 SQL 陳述式參考

 

適用於: System Center 2012 Configuration Manager, System Center 2012 Configuration Manager SP1, System Center 2012 R2 Configuration Manager

建立 System Center 2012 Configuration Manager 報表時,可以使用許多有用的 Microsoft SQL Server 陳述式,本節將予以簡短描述。 若要遵循這些討論中的步驟,您應該要有基本程度的 SQL 查詢陳述式知識和撰寫查詢的能力,如下所示:

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 子句所指定的檢視子集,或該檢視中的一個或多個資料列群組。 套用彙總函數時,會從每個資料列集產生單一的值。

System_CAPS_important重要事項

請注意 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

SiteCode

SiteName

ReportingSiteCode

計數

ABC

ABC 站台

 

928

123

123 站台

ABC

1010

許多內建報表會使用日期和時間函數。 最常使用的函數是 GETDATE、DATEADD、DATEDIFF 和 DATEPART。

GETDATE 函數會以 datetime 值的 SQL Server 內部格式產生目前的日期和時間。 GETDATE 接受 NULL 參數 ()。

下列範例會產生目前的系統日期和時間:

SELECT GETDATE()

(沒有資料行名稱)

2005-05-29 10:10:03.001

DATEADD 函數會根據將間隔加入指定日期,傳回新的 datetime 值。

Datepart 是指定日期的哪個部分 (例如年、月、日、小時、分鐘等等) 要傳回新值的參數,number 是用來遞增 datepart 的值,而 date 是開始日期。

下列範例會產生從 2005 年 5 月 29 日之後某兩天的日期:

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

(沒有資料行名稱)

2005-05-31 10:10:03.001

DATEDIFF 函數會傳回跨越兩個指定日期的日期和時間界線數目。

Datepart 是指定日期的哪個部分 (例如年、月、日、小時、分鐘等等) 要傳回新值的參數,startdate 是開始日期,enddate 是結束日期。

下列範例會產生第一個和第二個日期之間的分鐘數:

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

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

(沒有資料行名稱)

20118

DATEPART 函數會傳回整數,代表指定日期的指定 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

計數

結束日期

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

若要在 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

Client3

2005-06-12 09:28:11.110

外部聯結會傳回所有來自聯結檢視的資料列,不論它們之間有沒有相符的資料列。 ON 子句會補充此資料,而非對其篩選。 外部聯結的三種類型 (左方、右方和完整) 表示主要資料來源。 當您在檢視中有 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

Client3

2005-06-12 09:28:11.110

右方外部聯結的概念與左方外部聯結相同,不同之處在於來自右方檢視的所有資料列都會包含在結果中。

完整外部聯結會從這兩個聯結的檢視中擷取所有資料列。 它會傳回聯結條件為 True 的所有配對資料列,再加上來自其他檢視中,以 NULL 資料列串連之每項檢視的非配對資料列。 您通常不會想要使用這種外部聯結。

交叉聯結會傳回兩個檢視的乘積,而非加總。 在左方檢視中的每個資料列會與右方檢視中的每個資料列比對。 它是所有可能資料列組合的集合,不含任何篩選條件。 不過,如果您加入 WHERE 子句,和交叉聯結做為內部聯結,它就會使用此條件來篩選所有可能的資料列組合,直到篩選出您想要的那一項為止。

自我聯結會使用上述的任何聯結類型,但這種檢視會聯結至本身。 在資料庫圖表中,自我聯結又稱為自反關聯性。

使用 NOT IN 關鍵字片語的子查詢,用來尋找不符合特定準則的一組資料之相關資訊,會相當實用。 在下列範例中,此查詢會傳回沒有安裝 Notepad.exe 之所有電腦的 NetBIOS 名稱。 您必須先建立查詢,該查詢可偵測已安裝所選取檔案之所有電腦,如下所示:

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

顯示: