wsus 2.0用SQL文

WSUS 2.0のバックエンドのMSDEは3.0のWMSDEと違い,
DBに直接アクセスできる.
おすすめのソフトはMicrosoft SQL Server Management Studio Express
http://www.microsoft.com/downloads/details.aspx?displaylang=ja&FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796

MSDEへの接続に使用する設定は
Server name: ホスト名\WSUS
Authentication: Windows Authentication

で, 適用されていないパッチの一覧をホスト毎に取得するSQL文などを
作ってみた.
インストールが必要なパッチと対象ホスト.

SELECT     B.FullDomainName, B.IPAddress, B.RequestedTargetGroupName, 
           H.Title AS category, C.Title, A.SummarizationState, 
           B.LastReportedStatusTime, F.SecurityBulletinID, I.KBArticleID, D.updateID
FROM 

SUSDB.dbo.tbUpdateStatusPerComputer A
INNER JOIN
SUSDB.dbo.tbComputerTarget B ON A.targetID = B.targetID
INNER JOIN
SUSDB.dbo.tbPreComputedLocalizedProperty C ON C.updateID = A.updateID
INNER JOIN
SUSDB.dbo.tbUpdate D ON A.updateID = D.UpdateID
INNER JOIN
SUSDB.dbo.tbRevision E ON D.localUpdateID = E.localUpdateID
LEFT OUTER JOIN
SUSDB.dbo.tbSecurityBulletinForRevision F ON E.RevisionID = F.RevisionID
INNER JOIN
SUSDB.dbo.tbRevisionInCategory G ON E.RevisionID = G.RevisionID
INNER JOIN
SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty H ON G.CategoryID = H.CategoryID
LEFT OUTER JOIN
SUSDB.dbo.tbKBArticleForRevision I ON E.RevisionID = I.RevisionID

                      
WHERE     (H.ShortLanguage = 'en') AND (C.ShortLanguage = 'en') AND 
          (H.CategoryType = 'UpdateClassification') AND (NOT (A.SummarizationState IN (1, 4)))

---------------------------------------
インストール済みもしくは, 不要のパッチとそのホスト

SELECT     B.FullDomainName, B.IPAddress, B.RequestedTargetGroupName, 
           H.Title AS category, C.Title, A.SummarizationState, 
           B.LastReportedStatusTime, F.SecurityBulletinID, I.KBArticleID, D.updateID
FROM 

SUSDB.dbo.tbUpdateStatusPerComputer A
INNER JOIN
SUSDB.dbo.tbComputerTarget B ON A.targetID = B.targetID
INNER JOIN
SUSDB.dbo.tbPreComputedLocalizedProperty C ON C.updateID = A.updateID
INNER JOIN
SUSDB.dbo.tbUpdate D ON A.updateID = D.UpdateID
INNER JOIN
SUSDB.dbo.tbRevision E ON D.localUpdateID = E.localUpdateID
LEFT OUTER JOIN
SUSDB.dbo.tbSecurityBulletinForRevision F ON E.RevisionID = F.RevisionID
INNER JOIN
SUSDB.dbo.tbRevisionInCategory G ON E.RevisionID = G.RevisionID
INNER JOIN
SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty H ON G.CategoryID = H.CategoryID
LEFT OUTER JOIN
SUSDB.dbo.tbKBArticleForRevision I ON E.RevisionID = I.RevisionID

                      
WHERE     (H.ShortLanguage = 'en') AND (C.ShortLanguage = 'en') AND 
          (H.CategoryType = 'UpdateClassification') AND (A.SummarizationState IN (1, 4))

---------------------------------------
ちなみに, SUSDB.dbo.tbUpdateStatusPerComputerのSummarizationStateの
意味は以下のとおり.

1 = Not Applicable
2 = Needed
3 = Downloaded
4 = Installed
5 = Failed
6 = Reboot Required

---------------------------------------
ホストの一覧取得

SELECT     C.*, A.Name
FROM
SUSDB.dbo.tbTargetGroup A
LEFT OUTER JOIN
SUSDB.dbo.tbTargetInTargetGroup B ON B.TargetGroupID = A.TargetGroupID
RIGHT OUTER JOIN
SUSDB.dbo.tbComputerTarget C ON B.TargetID = C.TargetID