利用Windows Server Update Service 資料庫的資料

WSUS資料庫裡面已經建立好的VIEW對於自己建立自客製報告很有用。您可以自己使用它們來獲取有關更新，允許更新的電腦，下游服務器更新安裝和客戶端的資訊，也可以將來自不同視圖的信息整併到更複雜的報告中。

首先我們要連接到WSUS資料庫

為了連接到WSUS資料庫，您將需要知道資料庫伺服器和資料庫instance的名稱以及資料庫名稱。

WSUS使用的database instance例可能是以下之一：
Windows內部資料庫(WID)（默認情況下安裝的數據庫：Microsoft ## SSEE）
SQL Server 2005的本地預設instance
SQL Server 2005的本地命名instance
SQL Server 2005的遠端instance
如果您正在使用API​​，獲取WSUS伺服器的WSUS資料庫instance的最佳方式是調用ServerName。
一般來說，WSUS資料庫的名稱是SUSDB。但是，如果您使用API​​，獲得資料庫名稱的最安全的方法是呼叫DatabaseName。

命名管道與Windows內部資料庫
您只能通過命名管道連接到Windows內部資料庫實例。 Windows內部資料庫的命名管道字符串的默認格式為
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query.

PublicViewAccess數據庫角色可以訪問WSUS VIEW（唯讀）。 預設情況下，本地SQL Server管理員組的成員可以訪問公開VIEW。 如果您希望允許其他用戶使用這些視圖，可以為這些用戶建一個組登錄名，並在PublicViewAccess資料庫角色的SQL服務器上進行配置。
PUBLIC_VIEW命名空間
WSUS 3.0中的所有VIEW都屬於名為PUBLIC_VIEWS的命名空間。 如果在更高版本的WSUS中建新的VIEW，則它們將在不同的命名空間中創建。

下表列出了WSUS 3.0 public View。
PUBLIC_VIEWS.vUpdate

  UpdateId的值是唯一的。

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

Note
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

Note
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資料的範例

查詢獲取“未分配計算機”組中所有計算機上所有安全更新的更新摘要。

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  

具有特定更新安裝失敗的計算機
以下查詢列出了具有顯示給定更新失敗的有效批准的計算機。

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  

See also :
Using WSUS Views