Windows XP to Windows 7 : Let's talk applications

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]

Why I've been quiet

So ...  I've been off, rambling about for the last 6 weeks at a client trying to enjoy this little thing I call life.

Yes, you read that correctly, I left the stability of a full time job doing SCCM administration and Service-Now Architecture to head out into the world as it's newest Sr Consultant pushing solutions using Microsoft System Center based solutions.  So, if you have an implementation need, hey, give me a shout.

I've been off for 6 weeks working on getting a Windows 7 client roll out.  What that means will come about in more details over the next few posts.  I'm hanging around bad hotels, eating boat loads of sushi (literally, I'll have to post some pics) and just in general enjoying long days.  Man, that makes life so much more interesting than working the same old nine to five.

Oh well, time to work on some posts about technology.