SCCM2007的报表功能比较强大，我可以通过SQL SELECT语句来定制报表结果，以下是我在日常生活中所用到的一些报表

系统
 
SELECT
Caption0 as 'Operating System', CSDVersion0, COUNT(*) AS 'Count'
FROM
v_GS_OPERATING_SYSTEM OPSYS GROUP BY Caption0,
CSDVersion0 ORDER BY Caption0,
CSDVersion0
 
推送失败的软件
SELECT
a.Name0,
b.AdvertisementID,
c.AdvertisementName,
b.LastStateName,
d.Name,
c.ProgramName
FROM
v_R_System a,
v_ClientAdvertisementStatus b,
v_Advertisement c,
v_Package d
WHERE a.ResourceID = b.ResourceID AND b.AdvertisementID = c.AdvertisementID AND c.PackageID = d.PackageID AND b.LastStateName = 'failed'

软件查询

 

ADOBE ACROBAT
SELECT
DisplayName0, Count(*) AS 'Count',
Publisher0, Version0,
ProdID0
FROM v_GS_ADD_REMOVE_PROGRAMS
WHERE Publisher0 Like 'adobe%'
OR Publisher0 Like '%adobe'
GROUP BY DisplayName0, Publisher0, Version0, ProdID0

 

计算资产收集

Computer Name Computer User Computer Model Computer Manufacture Total Disk Space Free Disk Space Total

SELECT
A.Name0,
B.SerialNumber0,
A.Manufacturer0,
A.Model0,
C.Name0 AS 'Processor',
D.TotalPhysicalMemory0 AS 'Memory (KBytes)',
E.Size0 AS 'Disk Size (MBytes)',
F.MACAddress0, F.IPAddress0,
G.AD_Site_Name0 AS 'AD Site',
A.UserName0 AS 'Last user logged in',
H.Caption0 AS 'Operating System',
H.CSDVersion0 AS 'Service Pack',
G.Creation_Date0 AS 'Creationdate in SMS',
I.LastHWScan
FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_R_System G,v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I
WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID AND
F.IPAddress0 != NULL AND
F.IPAddress0 != '0.0.0.0'
GROUP BY
A.Name0,
B.SerialNumber0,
A.Manufacturer0,
A.Model0,
C.Name0,
D.TotalPhysicalMemory0,
E.Size0,
F.MACAddress0,
F.IPAddress0,
G.AD_Site_Name0,
 A.UserName0,
 H.Caption0,
 H.CSDVersion0,
G.Creation_Date0, I.LastHWScan