WSUS SQL Tables - OS txt Version?

Posted on 2011-04-21
Medium Priority
Last Modified: 2012-06-21

I'm peering into our WSUS 3.0 SQL tables to get some information.  In this information, I need to separate the units that are reporting into WSUS by Operating System.

I see that in the PUBLIC_VIEWS_vComputerTarget, it has the following fields:

OSMajorVersion (2 Digit)
OSMinorVersion (2 Digit)
OSBuildNumber (4 Digit)
OSServicePackMajorNumber (2 Digit)

If possible, I need to join those values with their text based counterparts.  (ie OS Build "2195" = Windows Server 2000).

Would somebody know which table I can link up to, in order to grab that info?

Thanks for any assistance that can be provided.
Question by:usslindstrom
LVL 28

Accepted Solution

Ryan McCauley earned 2000 total points
ID: 35452443
That information is likely not in the SUS database directly, but is reported by the client OS. You can see the mappings at this Microsoft article:


Note that the major.minor can be used to determine the OS version, and the build will tell you what service pack or patch level it's at (with the RTM versions listed in that article I linked). You'd likely need to do a case statement of some kind of print the name of an OS - something like (for desktop OSes):

SELECT CASE OSMajorVersion + '.' + OSMinorVersion
       WHEN '5.0' THEN 'Windows 2000'
       WHEN '5.1' THEN 'Windows XP'
       WHEN '5.2' THEN 'Windows XP x64'
       WHEN '6.0' THEN 'Windows Vista'
       WHEN '6.1' THEN 'Windows 7'
       ELSE 'Unknown'
END as Version

However, aside from examining patch levels, I don't know that there's an easy way to tell apart desktop versions of Windows from server versions, since they sometimes have the same major.minor versions - for example, both Windows 7 and Windows Server 2008 R2 both read as v6.1.

Author Closing Comment

ID: 35457335
Thank you very much for the information.  I'll look into throwing something together that's a bit more user friendly than WSUS' back end tables.

Much appreciated.

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
While rebooting windows server 2003 server , it's showing "active directory rebuilding indices please wait" at startup. It took a little while for this process to complete and once we logged on not all the services were started so another reboot is …
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question