I was looking for a simple way to identify what systems existed in our environment which were not running at the service pack levels which we require in our environment (and patches which are not current). As a quick starting point, there are two existing reports which give a lot of this information:

Count Operating Systems and Service Packs: Shows the breakdown of each Operating System including the service pack levels. The report can be drilled in to identify specific Operating Systems which are not running a current service pack but that requires several different reports to provide a comprehensive list.

Management 1 – Updates required but not deployed: Good starting point to determine what patches are not deployed in the environment.

The ConfigMgr console also provides good information on the status of service packs and patch status. Both of these are available on the same screen, with the first of these showing the Service Packs required and the second showing the Critical Updates which need to be applied:

Software Updates Summary: Vendor, Microsoft, Month and year (last month), Update Classification (Service Packs).

Software Updates Summary: Vendor, Microsoft, Month and year (last month), Update Classification (Critical Updates).

In my case, I needed a single query which would provide a list of Operating Systems which were not current for our environment. The first step on this was mapping out what Operating Systems we have in the environment, and mapping the full name, commonly known name, and service pack version which we expect to have deployed:

Full Operating System Name:            Commonly Known Name:     Expected Service Pack:

Microsoft Windows NT Server 5.0        Windows 2000                        SP4

Microsoft Windows NT Server 5.2        Windows 2003 R2                   SP2 

Microsoft Windows NT Server 6.0        Windows 2008                        SP1

Microsoft Windows NT Workstation 5.1 Windows XP                          SP3

Microsoft Windows NT Workstation 5.2 Windows XP 64                      SP3

Microsoft Windows NT Workstation 6.0 Windows Vista                       SP1

Microsoft Windows 7 Ultimate 6.1        Windows 7                             No SP yet

Using this table, I created the following query which identifies systems which are not currently running on the service pack level we require:

select distinct SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion,

SMS_G_System_OPERATING_SYSTEM.CSDVersion from  SMS_R_System inner join

SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID =

SMS_R_System.ResourceId inner join SMS_GEH_System_OPERATING_SYSTEM on

SMS_GEH_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where

SMS_R_System.OperatingSystemNameandVersion like "%Workstation 5.2%" and

SMS_G_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 3" or

SMS_R_System.OperatingSystemNameandVersion like "%Server 5.2%" and

SMS_G_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 2" or

SMS_R_System.OperatingSystemNameandVersion like "%5.0%" and

SMS_GEH_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 4" or

SMS_R_System.OperatingSystemNameandVersion like "%6.0%" and

SMS_G_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 1" or

SMS_R_System.OperatingSystemNameandVersion like "%Workstation 5.1%" and

SMS_G_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 3" or

SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.0%" and

SMS_G_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 1"

One thing to keep in mind is that systems which are not current enough to actually deploy the ConfigMgr client (such as a Windows 2003 server with no service packs) will not show up in the results of this query because there is no client available to provide the mapping of the service pack (we can see the OperatingSystemNameandVersion but not the CSDVersion which includes the service pack information). The above query seems to be working for me/let me know if you use it how it works for your environment!

The following is a revised version of the query which is specific to server systems and had a more comprehensive result for our environment (thanks Hamid!)

select distinct SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_OPERATING_SYSTEM.CSDVersion, SMS_R_System.ADSiteName from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like "%Server 2003%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 2" or SMS_G_System_OPERATING_SYSTEM.Caption like "%2000 Server%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 4" or SMS_G_System_OPERATING_SYSTEM.Caption like "%2008%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion != "Service Pack 1" order by SMS_R_System.ADSiteName