SCCM Report for Windows 7 Deployment

  • Article

I built this SCCM 2007 report for our daily Windows 7 deployment progress.  It shows a simple count of both Windows XP and 7, Advertisement Name, Distribution State, Count of clients in each state.  Further down in the report it shows computer name, model, OS, User Name, Last Boot Time, Last Status Message Name and time.  It was very helpful monitoring each nights status.

Code:

DECLARE @ADVID
varchar(20) SET @ADVID = 'PRI0001'
DECLARE @COLLID
varchar(20) SET @COLLID = 'PRI0001'
DECLARE @Total INT
DECLARE @FailedPct FLOAT
DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
SELECT
OS.Caption0 'Operating System',
COUNT(OS.Caption0)
from v_GS_Operating_System OS
Join v_FullCollectionMembership FCM on OS.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
WHERE COL.CollectionID = @COLLID
GROUP BY OS.Caption0
SELECT
AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS 'Number of clients with this Status'
FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as 'number'
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState
WHERE
AdvState.AdvertisementID = @ADVID
AND AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID
GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName

SELECT @Total = COUNT(DISTINCT ResourceID) FROM v_ClientAdvertisementStatus
WHERE AdvertisementID = @ADVID
IF @Total > 0
 SELECT @FailedPct = 100.0 * COUNT(DISTINCT ResourceID)/@Total FROM v_ClientAdvertisementStatus
 WHERE AdvertisementID = @ADVID AND LastState = 11
ELSE
 SET @FailedPct = 0
SELECT @StartTime = MIN(ExecutionTime) FROM v_TaskExecutionStatus
WHERE LastStatusMessageID = 11140 AND Step = 0 AND AdvertisementID = @ADVID
SELECT @EndTime = MAX(ExecutionTime) FROM v_TaskExecutionStatus
WHERE AdvertisementID= @ADVID
SELECT
 ts.Name AS TaskSequenceName,
 ts.Description,
 DATEADD(ss, @__timezoneoffset, @StartTime) AS StartTime,
 DATEADD(ss, @__timezoneoffset, @EndTime) AS EndTime,
 @Total AS TSRPT_C002,
 ROUND(@FailedPct, 1) AS C011
FROM v_TaskSequencePackage ts
INNER JOIN v_Advertisement ad ON ad.PackageID = ts.PackageID
WHERE ad.AdvertisementID = @ADVID

Select SYS.Netbios_Name0 'Computer Name'
,CS.Model0 'Model'
,OS.Caption0 'Operating System'
,U.Full_User_Name0 'User Name'
,OS.LastBootUpTime0
,STAT.LastStatusMessageIDName
,STAT.LastStatusTime
,STAT.AdvertisementID
From v_R_System SYS
INNER Join v_GS_Operating_System OS on SYS.ResourceID = OS.ResourceID
INNER Join v_FullCollectionMembership FCM on OS.ResourceID = FCM.ResourceID
INNER Join v_Collection COL on FCM.CollectionID = COL.CollectionID
INNER Join v_R_User U on SYS.User_Name0 = U.User_Name0
INNER Join v_GS_COMPUTER_SYSTEM CS on SYS.ResourceID = CS.ResourceID
INNER Join v_ClientAdvertisementStatus STAT on SYS.ResourceID = STAT.ResourceID
WHERE COL.CollectionID = @COLLID
AND STAT.AdvertisementID = @ADVID
ORDER BY OS.Caption0 DESC