使用SQL2012報表產生器自訂IT資產報表

標籤：sccm   sql2012   報表產生器   自訂報表   

本文將介紹如何通過SQL Server 2012 SP1的報表產生器自訂報表，為了更貼近實戰，本文將自訂一張IT資產報表。

一、部署IT資產管理系統

1、本文使用微軟SCCM2012R2（System Center Configuration Manager 2012 R2）作為IT資產管理系統，用來收集用戶端的硬體資訊。具體的部署過程因不是本文重點故略過，網上也有很多關於SCCM安裝部署的資料，感興趣的話可以自行查閱。不過需要注意的是在安裝SQL Server 2012SP1時，必須安裝報表功能組件。

2、部署環境如下：

電腦名稱	IP地址	作業系統	角色	安裝軟體
SCCM-DC	192.168.10.150	Win2012R2	網域控制站、DNS伺服器	DC、DNS
SCCM	192.168.10.151	Win2012R2	資料庫伺服器（含報表功能）、 SCCM伺服器	SQLServer2012SP1、 SCCM2012R2、 IIS等

3、部署好SCCM後，給SCCM-DC和SCCM伺服器安裝用戶端代理。

650) this.width=650;" width="1010" height="586" title="01.PNG" style="width:736px;height:420px;float:none;" alt="wKiom1gRs6HzM5B_AAHCrLGd-80583.png" src="http://s2.51cto.com/wyfs02/M00/89/65/wKiom1gRs6HzM5B_AAHCrLGd-80583.png" />

4、啟用SCCM的硬體清單收集功能，成功收集了SCCM、SCCM-DC兩台伺服器的硬體資訊，這些資訊都寫入在SCCM的資料庫中，我們的IT資產報表就是基於資料庫中的這些硬體資訊資料來進行定製開發。

650) this.width=650;" width="1085" height="606" title="02.PNG" style="width:738px;height:427px;float:none;" alt="wKioL1gRs6HiCR_tAACODqdQGk8955.png" src="http://s2.51cto.com/wyfs02/M02/89/62/wKioL1gRs6HiCR_tAACODqdQGk8955.png" />

650) this.width=650;" width="1081" height="602" title="03.PNG" style="width:739px;height:398px;float:none;" alt="wKioL1gRs6KQYpPJAACRV8Td3lA234.png" src="http://s2.51cto.com/wyfs02/M00/89/62/wKioL1gRs6KQYpPJAACRV8Td3lA234.png" />

二、調試IT資產報表的SQL查詢語句

本文原始出處：江健龍的技術部落格http://jiangjianlong.blog.51cto.com/3735273/1867368

1、SCCM的資料庫中，每台電腦只要安裝了用戶端代理都會被分配一個ResourceID（即資源ID），不同的硬體資訊寫入不同的表中，每張表都會有ResourceID欄位來標識是誰的硬體資訊，因此通過資源ID可以進行多表串連查詢，將我們所要的全部欄位顯示在一張報表中。SCCM的資料庫預設定義了一些視圖，本文的SQL查詢語句其實是通過查詢檢視來完成。的SQL語句就是通過視圖v_R_System查詢到資源ID和電腦名稱的資訊。

650) this.width=650;" width="819" height="544" title="04-1.png" style="width:737px;height:480px;float:none;" alt="wKiom1gRs6LT6Du3AAFV2EcmyiU925.png" src="http://s2.51cto.com/wyfs02/M01/89/65/wKiom1gRs6LT6Du3AAFV2EcmyiU925.png" />

2、的SQL語句是通過視圖V_GS_NETWORK_ADAPTER_CONFIGUR查詢到資源ID、IP地址和MAC地址的資訊。用了for XML path函數是為了合并多行為一行，因為有些用戶端電腦擁有多個IP地址，在SCCM資料庫的表中是作為多行顯示的，該函數可以將多行合并在一行顯示，因此還用了+‘，’就是為了在多個IP地址間加一個逗號做間隔。

650) this.width=650;" width="1057" height="547" title="05-1.png" style="width:737px;height:402px;float:none;" alt="wKiom1gRs6LhqD4ZAAGIc04YX5k806.png" src="http://s2.51cto.com/wyfs02/M00/89/65/wKiom1gRs6LhqD4ZAAGIc04YX5k806.png" />

3、以上我們在兩個視圖中分別查詢出了不同的硬體資訊，共同點是都具有資源ID欄位，那麼我們就可以使用left join語句，將兩個視圖進行左外串連查詢，效果如所示。因為我們是以查出電腦名稱的表為左表，需以該表為基準，哪怕某些硬體資訊為空白，也要顯示空白，因此需要使用左外串連。通過left join可以很方便地進行模組化操作，將查詢不同硬體資訊的SQL語句不斷串連進來。

650) this.width=650;" width="1044" height="608" title="06-1.png" style="width:741px;height:425px;float:none;" alt="wKiom1gRs6OBkwWNAAGRxjIcmLU367.png" src="http://s2.51cto.com/wyfs02/M01/89/65/wKiom1gRs6OBkwWNAAGRxjIcmLU367.png" />

4、就是我最終完成的SQL語句，查詢出所有要自訂的欄位，作為下文IT資產報表的基礎。其實一個SQL查詢語句的編寫是需要不斷修改和調試的，並在不同的部署環境中得到應用才能檢驗出bug所在，比如上文的for XML path函數，如果只是在用戶端都只有1個IP的簡單環境中調試時，由於根本不需要進行多行合并，也就不會去使用這個函數，但一旦出現一個用戶端具有多個IP的情況時，就會發現bug出現了，需要再修改調試，不斷改進和完善。

650) this.width=650;" width="797" height="494" title="07.PNG" style="width:738px;height:466px;float:none;" alt="wKioL1gRs6PAv-vXAAB0eDTgP7w705.png" src="http://s2.51cto.com/wyfs02/M01/89/62/wKioL1gRs6PAv-vXAAB0eDTgP7w705.png" />

650) this.width=650;" title="08.PNG" style="float:none;" alt="wKioL1gUvFOj2XEvAAB3VvMbVoA165.png" src="http://s4.51cto.com/wyfs02/M02/89/79/wKioL1gUvFOj2XEvAAB3VvMbVoA165.png" />

650) this.width=650;" title="09.PNG" style="float:none;" alt="wKiom1gUvFbiCtiBAACIiMLbgnY219.png" src="http://s1.51cto.com/wyfs02/M01/89/7B/wKiom1gUvFbiCtiBAACIiMLbgnY219.png" />

5、因具體的SQL語句編寫不是本文重點，故略過，但我也把主要的硬體資訊位於哪些視圖給歸納了一下，僅供參考。

硬體資訊	欄位名	所在視圖
電腦名稱	Netbios_Name0	v_R_System
IP地址	IPAddress0	v_GS_NETWORK_ADAPTER_CONFIGUR
MAC地址	MACaddress0
網卡型號	Name0	v_GS_NETWORK_ADAPTER vgna2
CPU類型	Name0	v_GS_PROCESSOR
每CPU核心數	NumberOfCores0
CPU數量	NumberOfProcessors0	v_GS_COMPUTER_SYSTEM
系統架構	SystemType0
硬體廠商	Manufacturer0
所屬域	Domain0
產品型號	Model0
作業系統	caption0	v_GS_OPERATING_SYSTEM
盤符	DeviceID0	v_GS_LOGICAL_DISK
磁碟格式	filesystem0
磁碟大小	Size0
硬碟型號	caption0	v_GS_disk
顯卡型號	name0	v_GS_VIDEO_CONTROLLER
實體記憶體	TotalPhysicalMemory0	v_GS_X86_PC_MEMORY
硬碟型號	caption0	v_GS_disk

三、自訂IT資產報表

本文原始出處：江健龍的技術部落格http://jiangjianlong.blog.51cto.com/3735273/1867368

1、完成了SQL語句的編寫調試後，我們就可以來建立自訂報表了。使用瀏覽器輸入報表伺服器（SQL Server Reporting Services）的URL：sccm.long.me/Reports，登入後點擊報表產生器。

650) this.width=650;" width="758" height="575" title="10.PNG" style="width:739px;height:560px;float:none;" alt="wKioL1gRs6TijbMWAABwY6G9Oxc188.png" src="http://s2.51cto.com/wyfs02/M02/89/62/wKioL1gRs6TijbMWAABwY6G9Oxc188.png" />

2、點擊運行程式。650) this.width=650;" width="760" height="573" title="11.PNG" style="width:736px;height:564px;float:none;" alt="wKiom1gRs6TCzdyXAAC86mKHtHQ037.png" src="http://s2.51cto.com/wyfs02/M02/89/65/wKiom1gRs6TCzdyXAAC86mKHtHQ037.png" />

3、在啟動報表產生器後，選擇建立一個空白報表。

650) this.width=650;" width="1009" height="607" title="13-1.png" style="width:735px;height:483px;float:none;" alt="wKiom1gRs6XyRZn4AAETEGjNxnU327.png" src="http://s2.51cto.com/wyfs02/M00/89/65/wKiom1gRs6XyRZn4AAETEGjNxnU327.png" />

4、添加資料來源，選擇“使用嵌在我的報表中的串連”，選擇連線類型為“Microsoft SQL Server“，連接字串輸入Data Source=.;Initial Catalog=CM_001，也可以點產生通過圖形介面產生連接字串，最後點擊測試連接，可以看到已成功地建立串連。

650) this.width=650;" width="999" height="606" title="14.PNG" style="width:735px;height:435px;float:none;" alt="wKiom1gRs6WyxgUkAADhzXTAHc4569.png" src="http://s5.51cto.com/wyfs02/M00/89/65/wKiom1gRs6WyxgUkAADhzXTAHc4569.png" />

5、添加資料集，資料來源選擇上文建立的資料來源，在查詢處將我們在前文調試好的SQL語句粘貼進來，馬上就能派上用場了。

650) this.width=650;" width="1001" height="611" title="15.PNG" style="width:741px;height:442px;float:none;" alt="wKioL1gRs6XjLYp3AAD0k-EmLTE100.png" src="http://s5.51cto.com/wyfs02/M00/89/62/wKioL1gRs6XjLYp3AAD0k-EmLTE100.png" />

6、添加完資料集後可以看到欄位都被識別出來了，這時我們再建立表，點擊插入——表——表嚮導。

650) this.width=650;" width="1004" height="600" title="16.PNG" style="width:741px;height:507px;float:none;" alt="wKioL1gRs6biG8S9AAFkArSR_Ns962.png" src="http://s4.51cto.com/wyfs02/M00/89/62/wKioL1gRs6biG8S9AAFkArSR_Ns962.png" />

7、在表嚮導中，選擇資料集預設就行了，因為我們在上文也就建立了一個資料集，直接Next。

650) this.width=650;" width="843" height="602" title="17-1.png" style="width:739px;height:552px;float:none;" alt="wKiom1gRs6aTzCAmAABzTTBOnEc030.png" src="http://s5.51cto.com/wyfs02/M01/89/65/wKiom1gRs6aTzCAmAABzTTBOnEc030.png" />

8、將資源ID拖到行組，再將剩下的全部欄位拖到值。

650) this.width=650;" width="844" height="604" title="18.PNG" style="width:740px;height:530px;float:none;" alt="wKiom1gRs6bCh32QAACQnJ_qAf8947.png" src="http://s4.51cto.com/wyfs02/M01/89/65/wKiom1gRs6bCh32QAACQnJ_qAf8947.png" />

9、將顯示小計和總計、展開/摺疊組的複選框去掉勾選。

650) this.width=650;" width="844" height="601" title="19.PNG" style="width:739px;height:554px;float:none;" alt="wKioL1gRs6eBUOOzAABXkBzUV2Y011.png" src="http://s4.51cto.com/wyfs02/M01/89/63/wKioL1gRs6eBUOOzAABXkBzUV2Y011.png" />

10、選擇自己喜歡的樣式，然後點Finish，完成表的建立。

650) this.width=650;" width="842" height="601" title="20.PNG" style="width:739px;height:557px;float:none;" alt="wKiom1gRs6eT-DO_AABLPiPEUYQ548.png" src="http://s5.51cto.com/wyfs02/M02/89/65/wKiom1gRs6eT-DO_AABLPiPEUYQ548.png" />

11、這樣我們的IT資產報表就基本完成了。

650) this.width=650;" width="1005" height="606" title="21-1.png" style="width:737px;height:471px;float:none;" alt="wKioL1gRs6fA0ewaAAFxABavezE179.png" src="http://s5.51cto.com/wyfs02/M02/89/63/wKioL1gRs6fA0ewaAAFxABavezE179.png" />

12、當然，我們可以添加標題、插入圖片、調整儲存格的寬度、置中顯示等進行自訂和美化，把位於頁尾的[&ExecutionTime]（自動產生時間）拖到表內，就可以在匯出報表時一起匯出。

650) this.width=650;" width="1200" height="515" title="22.PNG" style="width:735px;height:375px;" alt="wKiom1gVmE-TT6k7AAF5gN8i1QY535.png" src="http://s3.51cto.com/wyfs02/M01/89/81/wKiom1gVmE-TT6k7AAF5gN8i1QY535.png" />

13、最後點擊左上方的運行，你將看到我們自訂的IT資產報表的運行效果。

650) this.width=650;" width="1200" height="591" title="23.PNG" style="width:724px;height:442px;float:none;" alt="wKioL1gRs6iQqFN4AAGc8bko_vE803.png" src="http://s1.51cto.com/wyfs02/M00/89/63/wKioL1gRs6iQqFN4AAGc8bko_vE803.png" />

14、最後別忘了儲存，預設是儲存到報表伺服器上。

650) this.width=650;" title="24.PNG" style="float:none;" alt="wKioL1gRs6jigex0AABeJcCdwCA312.png" src="http://s1.51cto.com/wyfs02/M01/89/63/wKioL1gRs6jigex0AABeJcCdwCA312.png" />

15、登入報表伺服器，可以看到我們的IT資產報表，點擊它，將可以看到在瀏覽器中的運行效果，如果頁面顯示不太正常，可以開啟瀏覽器的相容視圖模式。

650) this.width=650;" width="757" height="405" title="25-1.png" style="width:739px;height:406px;float:none;" alt="wKiom1gRs6nzOH-XAACPooi8e98559.png" src="http://s1.51cto.com/wyfs02/M02/89/65/wKiom1gRs6nzOH-XAACPooi8e98559.png" />

650) this.width=650;" width="1200" height="519" title="26.PNG" style="width:724px;height:365px;float:none;" alt="wKiom1gRs6ngZSelAAFwE-Lne3Y973.png" src="http://s5.51cto.com/wyfs02/M01/89/65/wKiom1gRs6ngZSelAAFwE-Lne3Y973.png" />

16、我們可以將該報表匯出，比如匯出為Excel檔案。

650) this.width=650;" width="1200" height="530" title="27.PNG" style="width:733px;height:395px;float:none;" alt="wKioL1gRs6mh_yoKAAF0RDFsQsM244.png" src="http://s5.51cto.com/wyfs02/M01/89/63/wKioL1gRs6mh_yoKAAF0RDFsQsM244.png" />

650) this.width=650;" width="1200" height="533" title="28.PNG" style="width:735px;height:406px;float:none;" alt="wKiom1gRs6rTrXrPAAGA9AmLFzc152.png" src="http://s5.51cto.com/wyfs02/M02/89/65/wKiom1gRs6rTrXrPAAGA9AmLFzc152.png" />

17、匯出後使用WPS或Excel開啟，內容跟在瀏覽器中顯示的一樣。

650) this.width=650;" width="1200" height="521" title="29.PNG" style="width:735px;height:348px;" alt="wKioL1gVmS6BEDZhAAGfwS5RzTE551.png" src="http://s3.51cto.com/wyfs02/M01/89/7E/wKioL1gVmS6BEDZhAAGfwS5RzTE551.png" />

      至此我們的IT資產報表就完成了，只要資料庫中有相應的資料，編寫好SQL查詢語句，使用SQL Server 2012的報表產生器就可以自訂我們想要的任何報表了，並且報表可以在瀏覽器中訪問運行，也可以匯出成報表檔案，非常實用。

本文出自 “江健龍的技術部落格” 部落格，請務必保留此出處http://jiangjianlong.blog.51cto.com/3735273/1867368

使用SQL2012報表產生器自訂IT資產報表