The difficulties of rolling out any operating system (Windows, Linux, Macintosh) lies in building out the list of applications that are installed in your environment. To do this, you have to first figure out how large the scope is that you have to work with. Now keep in mind, I'm an SCCM guy and believe that the ACT utility is ok, but not the greatest way to determine what people have installed.
The realities:
The first thing I did was run a quick query to determine the scope of the application installs that exist in the environment. I use the v_GS_Add_Remove_Programs, v_R_System to find all of the machines that are still Windows XP. This query gives us a quick count of each application installed across the environment.
SELECT ARP.[DisplayName0] AS [Application]
, Count(SYS.[Name0]) AS [Computers]
FROM v_GS_Add_Remove_Programs AS ARP
JOIN v_R_System AS SYS
ON SYS.[ResourceID]=ARP.[ResourceID]
WHERE ([Operating_System_Name_And0]='Microsoft Windows NT Workstation 5.1'
OR [Operating_System_Name_And0]='Microsoft Windows NT Workstation 5.2')
GROUP BY ARP.[DisplayName0]
This query simply provides a list of applications and how many times they are installed. Once this is done, I export this list to Excel for groups of people to be able to work with [side note: in the future my goal is to use Sharepoint lists for this work]. On this list are many Microsoft Updates, I quickly filter those out to bring the list down to a more manageable list of software.
For this particular client, part of the application work was done ahead of time and most applications had been pre-tested, but some pieces of the data was missing. For example, the client used a bunch of ambiguous names for applications (especially those designed in-house) and didn't have any thing matching the name as installed in Add / Remove Programs.
We added a table to SCCM to allow us to track applications that we are certain have issues. We got agreement from management to move forward to Windows 7 if we can't show that the machine has applications that aren't ready. Our table (BrokenWindows7Apps) contains minimal fields (AppName, ARPDisplay, ExeName, ExeVersion) to perform simple queries against. OUR GOAL IS TO KEEP THINGS SIMPLE.
After this, we hunted down application owners (we had a pretty good list) and built a front end to the data to gather this information and use it for performing checks for broken applications. With this data in hand, I built several queries against this table that allow us to determine, in a True / False fashion, for each machine in the company, if Windows 7 will install properly based on the application set. This is useful information even if you are replacing the machine. Remember, you still have to perform some sort of assessment on the old machine that you are replacing.
... Have I ever mentioned how much I love the willingness of the programmers to help when the Infrastructure teams come along asking for information :) ...
And now, a litany of bad applications later, we had information for at least the ones that we believed were the biggest hitters for what we knew about. We now have the important pieces of information in front of us including a list of all machines that are still Windows XP, a list of known bad applications that includes the data for Add / Remove Programs Display Name and Executable information. It's time to put it all to good use.
So, here is our last query for the night (this will give you a list of all computers and if they qualify for Windows 7 based on their application set). This query gets very, very complicated (and yes, I love playing in SQL).
SELECT SYS.[Name0] AS [Computer]
, SYS.[Operating_System_Name_And0] AS [OS]
, SYS.[AD_Site_Name0] AS [AD Site]
, SYS.[User_Name0] AS [User]
, HW.[Model0] AS [Model]
, [Windows 7 Ready] =
CASE
WHEN [Windows 7 Ready] IS NULL THEN 'True'
WHEN [Windows 7 Ready] = 'False' THEN 'False'
END
FROM v_R_System as SYS
LEFT JOIN (
SELECT SYS.[Name0] AS [Computer]
, SYS.[Operating_System_Name_And0] AS [OS]
, SYS.[AD_Site_Name0] AS [AD Site]
, SYS.[User_Name0] AS [User]
, HW.[Model0] AS [Model]
, [Windows 7 Ready] = 'False'
FROM v_GS_Add_Remove_Programs AS ARP
JOIN(
SELECT [Name0], [ResourceID], [Operating_System_Name_And0], [AD_Site_Name0], [User_Name0]
FROM v_R_System
WHERE [Operating_System_Name_And0] = 'Microsoft Windows NT Workstation 5.1' or [Operating_System_Name_And0] = 'Microsoft Windows NT Workstation 5.2'
) AS SYS
ON ARP.ResourceID=SYS.ResourceID
JOIN v_GS_Computer_System AS HW
ON SYS.ResourceID=HW.ResourceID
WHERE
ARP.[DisplayName0] IN (SELECT DISTINCT DisplayName FROM BrokenWindows7Apps)
GROUP BY
SYS.[Name0]
, SYS.[Operating_System_Name_And0]
, SYS.[AD_Site_Name0]
, SYS.[User_Name0]
, HW.[Model0]
UNION
SELECT SYS.[Name0] AS [Computer]
, SYS.[Operating_System_Name_And0] AS [OS]
, SYS.[AD_Site_Name0] AS [AD Site]
, SYS.[User_Name0] AS [User]
, HW.[Model0] AS [Model]
, [Windows 7 Ready] = 'False'
FROM v_GS_SoftwareFile as SF
JOIN(
SELECT [Name0], [ResourceID], [Operating_System_Name_And0], [AD_Site_Name0], [User_Name0]
FROM v_R_System
WHERE [Operating_System_Name_And0] = 'Microsoft Windows NT Workstation 5.1' or [Operating_System_Name_And0] = 'Microsoft Windows NT Workstation 5.2'
) AS SYS
ON SF.ResourceID=SYS.ResourceID
JOIN v_GS_Computer_System AS HW
ON SYS.ResourceID=HW.ResourceID
WHERE
sf.[FileName] IN (SELECT DISTINCT [Executable] FROM BrokenWindows7Apps)
GROUP BY
SYS.[Name0]
, SYS.[Operating_System_Name_And0]
, SYS.[AD_Site_Name0]
, SYS.[User_Name0]
, HW.[Model0]
) as systems
on systems.[Computer] = SYS.[Name0]
LEFT JOIN v_GS_Computer_System AS hw
ON SYS.ResourceID = HW.ResourceID
WHERE SYS.[Operating_System_Name_And0] = 'Microsoft Windows NT Workstation 5.1' or SYS.[Operating_System_Name_And0] = 'Microsoft Windows NT Workstation 5.2'
ORDER BY
SYS.[Name0]
, SYS.[Operating_System_Name_And0]
, HW.[Model0]
