SCCM report /collection for computers on Group policy not updated for past 7 days
Posted by Eswar Koneti on July 15th, 2009
This gives list of machines where the group policy database file not updated recently.Before creating the SCCM web report,software inventory has to be enabled for secedit.sdb file which will be present in %windir%\security\database.
collection(WQL):
select SMS_R_SYSTEM.ResourceID
,SMS_R_SYSTEM.ResourceType
,SMS_R_SYSTEM.Name
,SMS_R_SYSTEM.SMSUniqueIdentifier
,SMS_R_SYSTEM.ResourceDomainORWorkgroup
,SMS_R_SYSTEM.Client
from
SMS_R_System inner join SMS_G_System_SoftwareFile
on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_SoftwareFile.FileName = “secedit.sdb”
and DATEDIFF(dd,SMS_G_System_SoftwareFile.ModifiedDate,GetDate()) > 7
Now you have to figure out why it is not updating 
SCCM Report(SQL):
select a.Name0 ,a.User_Name0 ,a.Operating_System_Name_and0
, CONVERT(VARCHAR(12),b.ModifiedDate,107)As “GPO Date Last Applied”
from v_R_System a join v_GS_SoftwareFile b on b.ResourceID=a.ResourceID
where b.FileName=’secedit.sdb’
and DATEDIFF(dd,b.ModifiedDate,GetDate()) > 7
order by b.ModifiedDate