• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1427
  • Last Modified:

WSUS SQL Tables - OS txt Version?


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.
1 Solution
Ryan McCauleyData and Analytics ManagerCommented:
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.
usslindstromAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now