Sunday, 21 August 2011

A SCCM report showing all compliant systems

Hi All,

The other reports I've written provide you with the information you need to ensure all your systems are up to date however, I haven't got a report that shows all the systems that are compliant.

Here is the query that will give you this report.

SELECT     TOP (100) PERCENT dbo.v_R_System.Netbios_Name0 AS [Computer Name], MAX(DISTINCT dbo.v_UpdateAssignmentStatus.ComplianceTime) 
                      AS [Time Checked]
FROM         dbo.v_UpdateAssignmentStatus INNER JOIN
                      dbo.v_CIAssignment ON dbo.v_UpdateAssignmentStatus.AssignmentID = dbo.v_CIAssignment.AssignmentID LEFT OUTER JOIN
                      dbo.v_R_System ON dbo.v_UpdateAssignmentStatus.ResourceID = dbo.v_R_System.ResourceID
WHERE     (dbo.v_R_System.Obsolete0 = 0) AND (dbo.v_R_System.Client0 = 1) AND (dbo.v_UpdateAssignmentStatus.IsCompliant = 1)
GROUP BY dbo.v_R_System.Netbios_Name0
ORDER BY [Computer Name]



No comments:

Post a Comment