WSUS SQL Tables - OS txt Version?

Posted on 2011-04-21
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

    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
    FROM PUBLIC_VIEWS_vComputerTarget

    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.
    LVL 5

    Author Closing Comment

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Learn about cloud computing and its benefits for small business owners.
    This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
    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…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now