SQL queries on WSUS database

LoginToWID

Sometimes you need to query the WSUS database using T-SQL statements.

The WSUS database can be installed using the Windows Internal Database (shipping with WSUS 3.0) or a standard SQL database.

When a Windows Internal Database is used, you can use SQL Management Studio Express to connect to.  Use the following string to connect to this database: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
For more info, see http://blogs.technet.com/b/gborger/archive/2009/02/27/exploring-the-wsus-windows-internal-database.aspx.

The Views are the easiest way to start exploring the SUSDB.

Some View and other examples:

SELECT TOP 1000 [ComputerTargetId]
      ,[ParentServerId]
      ,[Name]
      ,[IPAddress]
      ,[LastSyncResult]
      ,[LastSyncTime]
      ,[LastReportedStatusTime]
      ,[LastReportedInventoryTime]
      ,[ClientVersion]
      ,[OSArchitecture]
      ,[Make]
      ,[Model]
      ,[BiosName]
      ,[BiosVersion]
      ,[BiosReleaseDate]
      ,[OSMajorVersion]
      ,[OSMinorVersion]
      ,[OSBuildNumber]
      ,[OSServicePackMajorNumber]
      ,[OSDefaultUILanguage]
  FROM [SUSDB].[PUBLIC_VIEWS].[vComputerTarget]

USE SUSDB
SELECT [FullDomainName], [LastSyncTime] FROM [tbComputerTarget] ORDER BY [FullDomainName]

SELECT [CategoryType]
      ,[ParentCategoryId]
      ,[DefaultTitle]
      ,[DefaultDescription]
  FROM [SUSDB].[PUBLIC_VIEWS].[vCategory]
  ORDER BY [DefaultTitle]

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s