SQL Query tbv WSUS 3.0 SP1 Reporting

[punisher007]

Sinds kort ben ik overgegaan van WSUS 2.0 SP1 naar WSUS 3.0 SP1 omdat Server 2008 niet wordt ondersteund als client in WSUS 2.0 SP1.

Ten opzichte van WSUS 2.0 SP1 zijn er dan ook behoorlijk wat dingen veranderd, zowel positief als negatief.

Hetgene waar ik echter momenteel mee zit, is de reporting mogelijkheid. De reporting in WSUS zelf heeft voor mijn wensen 2 nare eigenschappen:
1. Hij toont alle needed updates, terwijl ik alleen needed en approved updates wil zien.
2. De standaard reporting export naar excel maakt voor iedere server 2 tabbladen in een excel document aan.

Wat ik graag zou willen, is een overzicht van servers die needed en approved updates hebben en ook welke updates dit zijn met eventuele toelichting op deze updates. Een voorbeeld zou dan als volgt zijn:

Servername | MSRC Needed Update | KB# Needed update | Description needed update | Etc | Etc2 | Etc3 |
=======================================================
Server1 MS06-012 905413 Security update for ......
Server1 MS06-013 905414 Security update for ......
Server1 MS06-014 905415 Security update for ......
Server2 MS06-012 905413 Security update for ......

Momenteel heb ik onderstaande query die een soortgelijke output geeft, echter deze geeft alle needed updates weer, terwijl ik het liefst ook de needed AND approved updates zou willen zien. Tevens heb ik hierin een aantal filters ingebouwd:

code:

USE SUSDB

DECLARE @CatType nvarchar(30)
SELECT @CatType = 'ProductFamily'

DECLARE @Language int
SELECT @Language = 1033

SELECT CAT.DefaultTitle
,       CT.name
,       U.SecurityBulletin
,       U.KnowledgebaseArticle
,       U.RevisionNumber
,       U.MsrcSeverity
,       Clas.DefaultTitle
,       U.DefaultTitle
--,     U.DefaultDescription
,       UAIU.Url
,       MU.State
,       U.InstallationRebootBehavior
,       U.InstallationCanRequestUserInput
,       U.InstallationRequiresNetworkConnectivity
,       U.isWsusInfrastructureUpdate
,       MU.UninstallationSupported
,       MU.UninstallationImpact
,       MU.UninstallationRebootBehavior
,       MU.UninstallationCanRequestUserInput
,       MU.UninstallationRequiresNetworkConnectivity
,       U.UpdateId
,       Clas.ClassificationId


FROM   PUBLIC_VIEWS.vUpdate                               U  
,      PUBLIC_VIEWS.vComputerTarget                       CT
,      PUBLIC_VIEWS.vCategory                             CAT
,      PUBLIC_VIEWS.vUpdateInCategory                     UIC
,      PUBLIC_VIEWS.vClassification                       Clas
,      PUBLIC_VIEWS.vUpdateInstallationInfoBasic          UIIB
,      PUBLIC_VIEWS.vUpdateAdditionalInfoUrl              UAIU
,      dbo.vwMinimalUpdate                                MU

WHERE  U.UpdateId = UIIB.UpdateId 
AND    U.UpdateId = UAIU.UpdateId 
AND    U.UpdateId = MU.UpdateId 
AND    U.UpdateId = UIC.UpdateId
AND    U.RevisionNumber = MU.RevisionNumber
AND    UIC.CategoryId = CAT.CategoryId
AND    U.ClassificationId = Clas.ClassificationId

-- computer status for that update is either 2 (not installed), 3 (downloaded),
-- 5 (failed), or 6 (installed pending reboot), and
AND    UIIB.State in (2, 3, 5, 6)

-- UAIU.LocaleId='1033' means language is English
AND    UAIU.LocaleId =@language

--AND    CAT.ParentCategoryId is NULL

AND    CAT.CategoryType=@CatType

AND    UIIB.ComputerTargetId = CT.ComputerTargetId
 

--Filter on Clas.DefaultTitle 
AND Clas.DefaultTitle  in ('Security Updates','Critical Updates')

-- Filter based on security bulletin

--AND    U.SecurityBulletin='MS09-014'

--AND    U.UpdateId= '47936cd4-a9af-456e-93e6-9229bc0093df'

--Filter based on product category Windows, SQL, Exchange, Office
AND CAT.DefaultTitle = 'Windows'

--Filter based on Servername
--AND CT.Name = 'server.domain.local'

Verder heb ik de volgende query (aangepast vanuit MS voorbeelden) die de servers toont die nog approved en needed updates hebben. Deze toont echter alleen de servers zonder gespecificeerd de updates die nog nodig zijn.

code:

-- 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 = 'All Computers'

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

)

Ik ben zelf niet zo handig in SQL en kom er ook niet meer uit op dit moment. Hopelijk is er iemand die kan helpen om deze queries te combineren tot een enkele query.

[RobIII]

punisher007 schreef op dinsdag 30 juni 2009 @ 14:20:
het liefst ook de needed AND approved updates

Daar zit al een redenatie fout. Iets kan niet needed AND approved zijn. Je wil dus needed OR approved; ik heb geen idee welk veld dat "type" (needed/approved/...) bevat, maar dan krijg je dus:

SQL:

Select...
From...
Where ... and .... and (category='needed' or category='approved')

Verder: schrijf die lelijke impliciete joins een netjes uit naar fatsoenlijke joins... (Hoe werken joins?)

[ Voor 7% gewijzigd door RobIII op 30-06-2009 14:57 ]

[punisher007]

Dankjewel, ik zal eens kijken hoever ik kom met de joins. Het helpt ook niet echt dat er weinig info te vinden is over de opbouw van de WSUSDB, waardoor ik zelf maar een beetje moet gaan beredeneren om een ERD te maken.

Wat betreft needed en approved wordt dit vanuit WSUS als volgt benaderd. Als het niet geinstalleerd is, maar wel van toepassing is, wordt het als needed gezien. Dit is dus ongeacht of het approved is of niet.
Ofwel, naast dat een update needed is, dient vUpdateApproval.Action = 'Install' van toepassing te zijn.

[ Voor 17% gewijzigd door punisher007 op 30-06-2009 17:44 ]

[RobIII]

punisher007 schreef op dinsdag 30 juni 2009 @ 17:43:
Wat betreft needed en approved wordt dit vanuit WSUS als volgt benaderd. Als het niet geinstalleerd is, maar wel van toepassing is, wordt het als needed gezien. Dit is dus ongeacht of het approved is of niet.
Ofwel, naast dat een update needed is, dient vUpdateApproval.Action = 'Install' van toepassing te zijn.

Nah; met wat booleaanse logica moet dat toch peanuts zijn?

SQL:

Select...
From...
Where ... and .... and Action = 'Install' and (somefield='needed' or (somefield is null) or (somefield = ''))

Of whatever... De juiste combinatie wordt (en is) me nog steeds niet helemaal duidelijk, maar het hoe dan ook gewoon basic combineren van wat and's/or's.