WSUS Views 3.0

 

Using WSUS Views
WSUS database views are useful for generating custom reports. You can use them by themselves to get quick information on updates, approvals computers, downstream servers update installation, and computer inventory, or you can combine information from different views for more sophisticated reports.

Connecting to the WSUS database

In order to connect to the WSUS database, you will need to know the name of the database server and instance, as well as the name of the database.
Database instance
The database instance used by WSUS may be one of the following:
  • Windows Internal Database (the database installed by default:Microsoft##SSEE)

     
  • A local default instance of SQL Server 2005

     
  • A local named instance of SQL Server 2005

     
  • A remote instance of SQL Server 2005

     

If you are using the API, the best way to get the WSUS database instance for a given WSUS server is to call ServerName.

Database name
In general, the name of the WSUS database is SUSDB. However, if you are using the API, the safest way to get the database name is to call DatabaseName.
Named pipes with Windows Internal Database
You can connect to the Windows Internal Database instance only by means of a named pipe. The default format of the named pipe string for Windows Internal Database is \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query.

If you want to know whether the WSUS server is using Windows Internal Database or SQL Server 2005, call IsUsingWindowsInternalDatabase.

Permissions for WSUS public views

WSUS views can be accessed (read-only) by the PublicViewAccess database role. Members of the local SQL Server administrators group have access to public views by default. If you wish to allow other users access to these views, you can create a group login for these users and provision it on the SQL server in the PublicViewAccess database role.

The PUBLIC_VIEW namespace

All the public views in WSUS 3.0 belong to a namespace called PUBLIC_VIEWS. If new public views are created in a later version of WSUS, they will be created in a different namespace.

WSUS public views

The following tables list the WSUS 3.0 public views.
PUBLIC_VIEWS.vUpdate
Returns one row for the latest revision of each update. The values of UpdateId are unique.

 

Column name Data type Description Matching API property
UpdateId uniqueidentifier Identifier that uniquely identifies the update. UpdateId
RevisionNumber int Revision number of a specific revision of an update. RevisionNumber
DefaultTitle nvarchar(200) Title of the update in English. No direct mapping.
DefaultDescription nvarchar(1500) Description of the update in English. No direct mapping.
ClassificationId uniqueidentifier Update classification identifier based on PUBLIC_VIEWS.vClassification. No direct mapping.
ArrivalDate datetime Date and time when the metadata for this revision of the update finished downloading to the WSUS server. ArrivalDate
CreationDate datetime Date and time when this revision of the update's metadata was authored. CreationDate
IsWsusInfrastructureUpdate bit Specifies whether this update is a WSUS infrastructure update. IsWsusInfrastructureUpdate
IsDeclined bit Specifies whether this update was declined. IsDeclined
MsrcSeverity nvarchar(20) Maximum severity rating of the Microsoft Security Response Center (MSRC) bulletin associated with the update. MsrcSeverity
PublicationState nvarchar(9) Publication state of the update. PublicationState
UpdateType nvarchar(256) Type of the update. UpdateType
UpdateSource nvarchar(15) Original source of the update. UpdateSource
KnowledgebaseArticle nvarchar(15) Knowledge Base article number that describes an issue related to or fixed by this update. Can be null. KnowledgebaseArticles
SecurityBulletin nvarchar(15) Security Bulletin number for the bulletin that describes security issues and changes that are related to the update. Can be null. SecurityBulletins
InstallationCanRequestUserInput bit Specifies whether the update installation program may request input from the user. CanRequestUserInput
InstallationRequiresNetworkConnectivity bit Specifies whether the installation of the update requires network connectivity. RequiresNetworkConnectivity
InstallationImpact nvarchar(25) Impact to the user and other applications when installing the update on the client computer. Impact
InstallationRebootBehaviore nvarchar(20) Restart behavior of the update. RebootBehavior
PUBLIC_VIEWS.vCategory
Returns one row for each update category. The values of CategoryId are unique.

 

Column name Data type Description Matching API property
CategoryId uniqueidentifier Identifier that uniquely identifies the category. Id
CategoryType nvarchar(256) Type of the category. Type
ParentCategoryId uniqueidentifier Parent category identifier of this category. No direct mapping.
DefaultTitle nvarchar(200) Title of this category in English. No direct mapping.
DefaultDescription nvarchar(1500) Description of this category in English. No direct mapping.
PUBLIC_VIEWS.vClassification
Returns one row for each update classification. The values of ClassificationId are unique.

 

Column name Data type Description Matching API property
ClassificationId uniqueidentifier Identifier that uniquely identifies the classification. Id
DefaultTitle nvarchar(200) Title of this classification in English. No direct mapping.
DefaultDescription nvarchar(1500) Description of this classification in English. No direct mapping.
PUBLIC_VIEWS.vUpdateInCategory
Returns one row for each category and update combination, if the update belongs to the category. An update can be belong to more than one category. This view should be used to obtain update membership in the categories exposed by PUBLIC_VIEWS.vCategory. The values of the UpdateId/CategoryId combination are unique.

 

Column name Data type Description
UpdateId uniqueidentifier Update identifier from PUBLIC_VIEWS.vUpdate.
CategoryId uniqueidentifier Category identifier from PUBLIC_VIEWS.vCategory.
CategoryType nvarchar(256) Type of the category. Same as PUBLIC_VIEWS.vCategory.CategoryType.
PUBLIC_VIEWS.vLanguage
Returns one row for each language supported by the server for locale specific information. The values of LocaleId are unique.

 

Column name Data type Description
LocaleId int Language identifier.
Name nvarchar(16) Language code in RFC1766 format (for example, “en” for English).
EnglishName nvarchar(32) Language name in English.
PUBLIC_VIEWS.vUpdateText
Returns one row for each update title and description, in the languages specified in the update metadata. The values of the UpdateId/LocaleId combination are unique.

The title and description rows correspond to the API properties Title and Description.

 

Column name Data type Description
UpdateId uniqueidentifier Update identifier from PUBLIC_VIEWS.vUpdate.
LocaleId int Language identifier from PUBLIC_VIEWS.vLanguage.
Title nvarchar(200) Title of the update.
Description nvarchar(1500) Description of the update.
PUBLIC_VIEWS.vUpdateAdditionalInfoUrl
Returns one row for each additional info URL for an update in the languages specified in the update metadata. These URLs are provided by the author of the update to publish additional information about the update. There can be more than one additional information URL for and update for a given language.

The Url row corresponds to the API property AdditionalInformationUrls.

 

Column name Data type Description
UpdateId uniqueidentifier Update identifier from PUBLIC_VIEWS.vUpdate.
LocaleId int Language identifier from PUBLIC_VIEWS.vLanguage.
Url nvarchar(2083) Additional info URL.
PUBLIC_VIEWS.vCategoryText
Returns one row for each product category title and description, in the languages specified in the category metadata. The values of the CategoryId/LocaleId combination are unique. The Title and Description rows correspond to the API properties Title and Description.

 

Column name Data type Description
CategoryId uniqueidentifier Category identifier from PUBLIC_VIEWS.vCategory.
LocaleId int Language identifier from PUBLIC_VIEWS.vLanguage.
Title nvarchar(200) Title of the category.
Description nvarchar(1500) Description of the category.
PUBLIC_VIEWS.vClassificationText
Returns one row for each product classification title and description, in the languages specified in the classification metadata. The values of the ClassificationId/LocaleId combination are unique.The Title and Description rows correspond to the API properties Title, Description.

 

Column name Data type Description
ClassificationId uniqueidentifier Classification identifier from PUBLIC_VIEWS.vClassification.
LocaleId int Language identifier from PUBLIC_VIEWS.vLanguage.
Title nvarchar(200) Title of the classification.
Description nvarchar(1500) Description of the classification.
PUBLIC_VIEWS.vDownstreamServer
Returns one row for each downstream WSUS server connected to this in the server hierarchy.

 

Column name Data type Description Matching API property
ServerId uniqueidentifier Identifier that uniquely identifies the WSUS server. Id
Name nvarchar(255) Full domain name of the downstream server. FullDomainName
IsReplica bit Specifies whether the downstream server is a replica server. IsReplica
ParentServerId uniqueidentifier Identifier of the upstream WSUS server this server last synchronized from, or null for servers directly connected to this server. GetParentServer
Version nvarchar(32) Version of WSUS that is installed on the downstream server Version
LastSyncTime datetime Date and time in UTC when the downstream server last synchronized with its parent server. LastSyncTime
LastRollupTime datetime Date and time in UTC when the downstream server last rolled up reporting data to its parent server. LastRollupTime
PUBLIC_VIEWS.vComputerTarget
Returns one row for each computer that connected to this WSUS server including computers that have been rolled-up from downstream servers. The values of ComputerTargetId are unique.

 

Column name Data type Description Matching API property
ComputerTargetId nvarchar(256) Identifier that uniquely identifies the computer. Id
ParentServerId uniqueidentifier WSUS server identifier from vDownstreamServer, if the computer is connected to a downstream server, otherwise null. ParentServerId
Name nvarchar(255) Full domain name of the computer. FullDomainName
IPAddress nvarchar(40) IP address of the computer. IPAddress
LastSyncResult nvarchar(9) Status of the most recent scan. LastSyncResult
LastSyncTime datetime Date and time in UTC of the most recent scan. LastSyncTime
LastReportedStatusTime datetime Date and time in UTC the computer last reported update status information to its server. LastReportedStatusTime
LastReportedInventoryTime datetime Date and time in UTC the computer last reported inventory information to its server. LastReportedInventoryTime
ClientVersion nvarchar(20) Version of the Automatic Update agent installed on the computer. ClientVersion
OSArchitecture nvarchar(100) Target processor architecture of the operating system on the computer. OSArchitecture
Make nvarchar(64) Make of the computer. Make
Model nvarchar(64) Model of the computer. Model
BiosName nvarchar(64) BIOS name of the computer. Name
BiosVersion nvarchar(64) BIOS version of the computer. Version
BiosReleaseDate datetime BIOS release date of the computer. ReleaseDate
OSMajorVersion int Major version number of the operating system on the computer. Major
OSMinorVersion int Minor version number of the operating system on the computer. Minor
OSBuildNumber int Version number of the operating system build. Build
OSServicePackMajorNumber int Major version number of the operating system service pack. ServicePackMajor
OSDefaultUILanguage nvarchar(10) Locale of the operating system. DefaultUILanguage
PUBLIC_VIEWS.vComputerTargetGroup
Returns one row for each computer group in the server including in-built computer groups. The values of ComputerTargetGroupId are unique.

 

Column name Data type Description Matching API property
ComputerTargetGroupId uniqueidentifier Identifier that uniquely identifies the computer group. Id
Name nvarchar(256) Name of the group. Name
ParentTargetGroupId uniqueidentifier Identifier from this view for the parent group, or null for the All Computers group at the root of the hierarchy. GetParentTargetGroup
PUBLIC_VIEWS.vComputerGroupMembership
Returns one row for each computer and computer group if the computer is part of the group, including computer groups of which the computer is indirectly a member. The values of the ComputerTargetId/ComputerTargetGroupId combination are unique.

 

Column name Data type Description
ComputerTargetId nvarchar(256) Computer identifier from PUBLIC_VIEWS.vComputerTarget.
ComputerTargetGroupId uniqueidentifier Computer group identifier from PUBLIC_VIEWS.vComputerTargetGroup.
IsExplicitMember bit Specifies whether the computer is a direct member or indirect member (member of a child computer group in the group hierarchy).
PUBLIC_VIEWS.vUpdateApproval
Returns one row with approval information for each update and computer group if the update is approved to that computer group. The values of UpdateApprovalId are unique. In addition, the values of the combination UpdateId/ComputerTargetGroupId are unique.

 

Column name Data type Description Matching API property
UpdateApprovalId uniqueidentifier Identifier that uniquely identifies the update approval. Id
UpdateId uniqueidentifier Update identifier from PUBLIC_VIEWS.vUpdate. UpdateId
ComputerTargetGroupId uniqueidentifier Computer group identifier from PUBLIC_VIEWS.vComputerGroup. ComputerTargetGroupId
Action nvarchar(11) Action that the client performs when applying the update. Action
Deadline datetime Date and time in UTC by when a computer will be forced to apply the update. Deadline
CreationDate datetime Date and time in UTC an administrator approved the update CreationDate
AdministratorName nvarchar(385) Name of the administrator who approved the update AdministratorName
IsOptional bit Specifies whether an update is optional to a computer receiving this approval. IsOptional
IsStale bit Specifies whether this approval is for an older revision of the update. n/a
PUBLIC_VIEWS.vUpdateInstallationInfoBasic
Returns one row for each update and computer if the computer has reported status for that update with the reported status information. The results do not include the Unknown and NotApplicable states. This view is the optimal way to obtain computer status when the above states are not relevant to the solution. The values of the UpdateId/ComputerTargetId combination are unique.

 

Column name Data type Description Matching API property
UpdateId uniqueidentifier Update identifier from PUBLIC_VIEWS.vUpdate. UpdateId
ComputerTargetId nvarchar(256) Computer target identifier from PUBLIC_VIEWS.vComputerTarget. ComputerTargetId
State int State of the update installation on the computer. UpdateInstallationState
noteNote:
Use the table-valued function PUBLIC_VIEWS.fnUpdateInstallationStateMap to map string representations of the installation state to the integer value exposed by this view. The integer representations are subject to change between releases.

PUBLIC_VIEWS.vUpdateInstallationInfo
Returns one row for each update and computer with status information of all possible states. The values of the UpdateId/ComputerTargetId combination are unique.

 

Column name Data type Description Matching API property
UpdateId uniqueidentifier Update identifier from PUBLIC_VIEWS.vUpdate. UpdateId
ComputerTargetId nvarchar(256) Computer target identifier from PUBLIC_VIEWS.vComputerTarget. ComputerTargetId
State int State of the update installation on the computer. UpdateInstallationState
noteNote:
Use the table-valued function PUBLIC_VIEWS.fnUpdateInstallationStateMap to map string representations of the installation state to the integer value exposed by this view. The integer representations are subject to change between releases.

PUBLIC_VIEWS.vUpdateEffectiveApprovalPerComputer
Returns one row for each update and computer along with the effective approval identifier when the computer belongs to multiple groups and the update is approved to some of those groups. The values of the UpdateId/ComputerTargetId combination are unique.

 

Column name Data type Description Matching API property
UpdateId uniqueidentifier Update identifier from PUBLIC_VIEWS.vUpdate. UpdateId
ComputerTargetId nvarchar(256) Computer target identifier from PUBLIC_VIEWS.vComputerTarget. ComputerTargetId
UpdateApprovalId uniqueidentifier Update approval identifier of the effective approval from PUBLIC_VIEWS.vUpdateApproval. UpdateApprovalAction, UpdateApprovalTargetGroupId
PUBLIC_VIEWS.fnUpdateInstallationStateMap
This table-valued function returns the mapping from the string representation of update installation state to the corresponding integer representation used in the public views PUBLIC_VIEWS.vUpdateInstallationInfo and PUBLIC_VIEWS.vUpdateInstallationInfoBasic.

 

Column name Data type Description
Id tinyint Integer representation of the installation state.
Name nvarchar(256) String representation of the installation state.
PUBLIC_VIEWS.vSupportedInventory
Returns one row for each inventory type supported by the server. The values of the ClassName/PropertyName combination are unique.

 

Column name Data type Description
ClassName nvarchar(256) Name of the inventory collection class.
PropertyName nvarchar(256) Name of the property within an inventory class.
PropertyType nvarchar(10) CLR data type of the property.
PUBLIC_VIEWS.vComputerInventory
Returns collected inventory data for all computers. The values of the ComputerTargetId/ClassName/InstanceId/PropertyName combination are unique. Note that there can be multiple sets of inventory data for the same inventory class, differentiated by the InstanceId field.

 

Column name Data type Description
ComputerTargetId nvarchar(256) Computer target identifier from PUBLIC_VIEWS.vComputerTarget for the computer on which the inventory was collected.
ClassName nvarchar(256) Name of the inventory class.
InstanceId bigint A serial number to differentiate multiple inventory items that are collected for the same computer and inventory class.
KeyValue nvarchar(256) Differentiating inventory property for the current instance.
PropertyName nvarchar(256) Inventory property name.
Value nvarchar(256) Value of the collected data for the given class and property.

WSUS samples

The following samples show the kinds of SQL queries you can use to get usefule information with WSUS views.
Update summaries for unassigned computers
The following query gets update summaries for all Security Updates across all the computers in the Unassigned Computers group.
DECLARE @securityUpdates uniqueidentifier
DECLARE @unassignedGroup uniqueidentifier
SET @securityUpdates = '0FA1201D-4330-4FA8-8AE9-B877473B6441'
SET @unassignedGroup = 'B73CA6ED-5727-47F3-84DE-015E03F6A88A'
SELECT
u.UpdateId
, u.DefaultTitle
, usc.State
, COUNT(*)
FROM
    PUBLIC_VIEWS.vUpdate AS u
    INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfo AS usc ON u.UpdateId = usc.UpdateId
WHERE
usc.ComputerTargetId IN (
        SELECT ComputerTargetId FROM PUBLIC_VIEWS.vComputerGroupMembership WHERE ComputerTargetGroupID = @unassignedGroup)
GROUP BY
u.UpdateID, u.DefaultTitle, usc.State
Computers with installation failures for a specific update
The following query lists the computers with effective approvals that are showing failures for a given update.
DECLARE @updateID uniqueidentifier
DECLARE @failureState int
SELECT @failureState = Id FROM PUBLIC_VIEWS.fnUpdateInstallationStateMap() WHERE Name = 'Failed'
SET @updateID = '106F464C-2995-4ED0-946D-8230A95677FE'
SELECT
usc.ComputerTargetId
, c.Name
, ua.Action
FROM
PUBLIC_VIEWS.vUpdateInstallationInfoBasic AS usc
INNER JOIN PUBLIC_VIEWS.vUpdateEffectiveApprovalPerComputer AS ea 
ON usc.UpdateId = ea.UpdateId and usc.ComputerTargetId = ea.ComputerTargetId
INNER JOIN PUBLIC_VIEWS.vComputerTarget AS c 
        ON usc.ComputerTargetId = c.ComputerTargetId
    INNER JOIN PUBLIC_VIEWS.vUpdateApproval AS ua 
        ON ua.UpdateApprovalId = ea.UpdateApprovalId
WHERE
usc.UpdateId = @updateID
AND usc.State = @failureState

//////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Baseline compliance report, using public WSUS views

Customers at TechEd asked how to generate a compliance report that shows computers that are out of compliance against updates that have been approved for install to them for N days. This can't be done in the public UI because it has no ability to specify the length of time an update has been approved, or to scope to just updates approved-for-install to that computer. However it can be done in WSUS 3 and later via our public SQL views.

Information on how to use our public DB views can be found here: http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx. As described in the article, to do this with the Windows Internal Database with WSUS, one first downloads SQL Studio Express Edition, and then connects to the DB using Windows Auth and the connection string "\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query".

Anyways, here's a query that accomplishes this. This query has not been tried on large DBs yet and may have performance challenges on such DBs. We will probably update this blog next week with a tweaked version of this query that performs better on large systems, and that lists the particular updates that are needed by the computer (and also says if they are needed just because a reboot is needed). But so many folks asked about how to use the public views to do this type of query last week that we wanted to show how it is done.

-Marc Shepard

Lead Program Manager, WSUS

-- Find computers within a target group that need updates

-- which have been approved for install for at least N days

USE SUSDB

DECLARE @TargetGroup nvarchar(30)

DECLARE @Days int

SELECT @TargetGroup = 'Test Machines'

SELECT @Days = 7

-- Find all computers in the given @TargetGroup

SELECT vComputerTarget.Name

FROM PUBLIC_VIEWS.vComputerGroupMembership

INNER JOIN PUBLIC_VIEWS.vComputerTarget on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId

INNER JOIN PUBLIC_VIEWS.vComputerTargetGroup on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId

WHERE

vComputerTargetGroup.Name = @TargetGroup

-- And only select those for which an update is approved for install, the

-- computer status for that update is either 2 (not installed), 3 (downloaded),

-- 5 (failed), or 6 (installed pending reboot), and

-- the update has been approved for install for at least @Days

AND EXISTS

(

select * from

PUBLIC_VIEWS.vUpdateEffectiveApprovalPerComputer

INNER JOIN PUBLIC_VIEWS.vUpdateApproval on vUpdateApproval.UpdateApprovalId = vUpdateEffectiveApprovalPerComputer.UpdateApprovalId

INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId

WHERE

vUpdateEffectiveApprovalPerComputer.ComputerTargetId = vComputerTarget.ComputerTargetId

AND vUpdateApproval.Action = 'Install'

AND vUpdateInstallationInfoBasic.UpdateId = vUpdateApproval.UpdateId

AND vUpdateInstallationInfoBasic.State in (2, 3, 5, 6)

AND DATEDIFF (day, vUpdateApproval.CreationDate, CURRENT_TIMESTAMP) > @Days

)