Sentinel Query for WSUS

Sentinel Query for WSUS

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

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2008-04-09 15:36
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.