Tuesday, September 11, 2012

SCCM 2007 Windows 7 Project Dashboard

My major project for this year is preparing, designing, and building a process and methodology for upgrading 5000+ XP workstations to Windows 7 at the hospital.  Upgrading to Windows 7 is a major project and took a number of resources.   Me and my partner were only responsible for building the OS and using the SCCM tools.  There was a Desktop Manager to handle purchasing the hardware and make decisions on where that hardware us used.  There was a project manager who was very useful just keeping everybody in line and keeping track of what was said in previous meetings.  There was an Applications Manager who managed a team of individuals who would have to test all our applications with Windows 7 (later on Windows 7 with 64-bit).  And of course we had a Desktop Team lead who managed the resources necessary for moving hardware around and interfacing with the customer/clients.

The project started in January as a hardware attrition model whereby we would replace XP workstations with new hardware with Windows 7.  Not surprisingly, we only received the budget to replace 10-12% of our inventory. If anyone is keeping track, that would put the end date for this project at best into 2020 and at worst 2022 -- time for plan B, upgrading current hardware.

It took my partner and I three months to build a decent Windows 7 Operating System Deployment task sequence.  From there we added local User State Migration but asked for some direct Microsoft Professional Field Engineer (PFE) to verify our process and make sure that we approached USMT for 5000 without too much egg on our face.  Microsoft helped us leverage Microsoft Deployment Toolkit (MDT) 2012 and tweaked our USMT.  Best of all they gave the USMT the stamp of approval for which I was looking.


Here is the Windows 7 Dashboard I built and the queries used to create it:

Left side:

SELECT OPSYS.Caption0 as C054, COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM OPSYS
inner join v_R_System sys on OPSYS.ResourceID=sys.ResourceID
join v_FullCollectionMembership SYS1 on SYS1.ResourceID=SYS.ResourceID
WHERE SYS1.CollectionID = 'CM0004C5'
GROUP BY OPSYS.Caption0
ORDER BY Count DESC

Right side:

DECLARE @daysleft int, @workdaysleft int, @targetdate datetime, @previousos nvarchar(30)
SET @previousos = '%Microsoft Windows XP%'
SET @targetdate = '2013-04-30'
SET @daysleft = DATEDIFF(day, GETDATE(), @targetdate)
SET @workdaysleft = (@daysleft*5)/7
SELECT
CAST(GETDATE() AS nvarchar(30)) AS 'Today',
CAST(@targetdate AS nvarchar(30)) AS 'Target Date',
@daysleft AS 'Days Left',
@workdaysleft AS 'Work Days Left',
OPSYS.Caption0 as 'Previous OS',
COUNT(*) AS 'Count',
COUNT(*)/@workdaysleft AS 'Upgrades Per Day'
FROM v_GS_OPERATING_SYSTEM OPSYS
inner join v_R_System sys on OPSYS.ResourceID=sys.ResourceID
join v_FullCollectionMembership SYS1 on SYS1.ResourceID=SYS.ResourceID
WHERE SYS1.CollectionID = 'CM0004C5'
AND OPSYS.Caption0 like @previousos
--WHERE SYS1.CollectionID = @CollID
GROUP BY OPSYS.Caption0
ORDER BY OPSYS.Caption0