Technical Tip

Here is an example of a query to get information from WSUS. It shows data from the most populated tables.

SELECT ei.EventOrdinalNumber AS OrdinalNumber, ei.EventID AS EID, 
ei.EventNameSpaceID AS ENSID, ei.EventSourceID AS ESID,
ei.TimeAtTarget AS TimeAtTarget, ei.TimeAtServer AS TimeAtServer, 
ei.ComputerID AS ComputerID,  ei.AppName AS AppName, 
ct.TargetID AS TargetID, ct.LastSyncTime AS LastSync, 
ct.LastReportedStatusTime AS LastRep, ct.IPAddress AS IPAddress, 
ct.FullDomainName AS DomainName, ct.OSMajorVersion AS OSMAV, 
ct.OSMinorVersion AS OSMIV, ct.OSBuildNumber AS OSBN, 
ct.OSServicePackMajorNumber AS OSSPMAN, 
ct.OSServicePackMinorNumber AS OSSPMIN, ct.ComputerMake AS CMake, 
ct.ComputerModel AS CModel, ct.ProcessorArchitecture AS Architecture, 
ei.UpdateID AS UpdateID, u.LocalUpdateID AS LocalUpdateID, 
u.UpdateTypeID AS UpdateTypeID, ut.Name AS UpdateName,
uspc.SummarizationState AS SummState, tg.Name AS NameGroup, 
r.IsCritical AS Critical, pcclp.title AS Category 
FROM dbo.tbEventInstance AS ei 
LEFT OUTER JOIN dbo.tbUpdate AS u ON ei.updateid = u.updateid 
LEFT JOIN dbo.tbUpdateStatusPerComputer AS uspc ON ei.UpdateID = uspc.UpdateID 
LEFT JOIN dbo.tbComputerTarget AS ct ON uspc.TargetID = ct.TargetID 
LEFT JOIN dbo.tbUpdateType AS ut ON u.UpdateTypeID = ut.UpdateTypeID 
LEFT JOIN dbo.tbTargetInTargetGroup AS titg ON uspc.TargetID = titg.TargetID 
LEFT JOIN dbo.tbTargetGroup AS tg ON titg.TargetGroupID = tg.TargetGroupID 
LEFT JOIN dbo.tbRevision AS r ON u.LocalUpdateID = r.LocalUpdateID 
LEFT JOIN dbo.tbRevisionInCategory AS ric ON r.RevisionID = ric.RevisionID 
LEFT JOIN dbo.tbPrecomputedCategoryLocalizedProperty AS pcclp ON ric.CategoryID = pcclp.CategoryID 
WHERE ei.EventOrdinalNumber > 0 ORDER BY ei.EventOrdinalNumber ASC