Tuesday, January 24, 2012

ConfigMgr Query: Office 2007 Version

(Note: Another one of my many IT hats is "Systems Management guru".  In my career I have completed 3 different SMS/ConfigMgr implementations.  I started with SMS 1.2 (which was very painful!).  I certified on SMS 2.0 as part of my MCSE.  My previous employer hired me to complete a nation-wide roll-out of SMS 2003.  And, while my current employer hired me to implement and maintain a VMware VI/vSphere environment, they purchased ConfigMgr 2007 but hadn't implemented it yet so I helped them with that (I can't seem to get away from it!).  So from time-to-time you'll see me post Systems Management and ConfigMgr topics such as inventory, queries, software distributions and even Web Remote Tools.)

Turns out that to use some of the new features of Exchange 2010 you must be running Office 2007 SP3.  There's even better integration with Office 2010 of course.

The Office version can be found in Add/Remove Programs.  Here's Microsoft's KB on Office 2007 versions.  At the time of this writing that had not updated it for SP3, which is version 12.0.6612.1000.

To determine what users aren't running Office 2007 SP3, I used the following ConfigMgr 2007 query:
http://support.microsoft.com/kb/928116

select distinct SMS_R_System.Name, SMS_R_System.ADSiteName, SMS_R_System.IPSubnets, SMS_R_System.LastLogonUserName, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version
 from
SMS_R_System
 inner join SMS_G_System_ADD_REMOVE_PROGRAMS
  on
 SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
 where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Basic 2007"
 or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Standard 2007"
 or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional 2007"
 or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2007"
 or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Ultimate 2007"
 or
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Enterprise 2007")
 and
SMS_G_System_ADD_REMOVE_PROGRAMS.Version != "12.0.6612.1000"
order by SMS_R_System.ADSiteName