The below scripts works for only Win 2000 and XP not for other operating systems .Here are the simple steps that you can achive this(getting a report for local administrators).*******test it once before taking it to production***************** 1.Go to the X:\smsinboxes\clifiles.src\hinv\sms_def.mof where X is SMS installed Drive,edit SMS_DEF.MOF file (If you have already edited it…
SCCM Configmgr collection report how to check Group policy file updated or not for X days

This gives list of machines where the group policy database file not updated .Before creating the SCCM web report,software inventory has to be enabled for GPO file secedit.sdb file which will is available 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 =…

This Report will help you in identifying the collection that are member of any other parent collection. SELECT COL.CollectionID, COL.Name, COL.Comment, CTSC.parentCollectionID FROM dbo.v_Collection COL, dbo.v_CollectToSubCollect CTSC WHERE CTSC.subCollectionID = COL.CollectionID The above report is to get collection with its parent collection but if you want to know the root map of particular, go with…

Gives list of computers where Programs installed recently. SELECT CS.Name0, CS.UserName0, ISW.ProductName0, ISW.VersionMajor0, ISW.VersionMinor0, ISW.Publisher0, ISW.RegisteredUser0, ISW.InstallDate0, ISW.InstallSource0 FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_INSTALLED_SOFTWARE ISW WHERE ISW.ResourceID = CS.ResourceID ORDER BY ISW.InstallDate0 DESC, CS.Name0, CS.UserName0, ISW.ProductName0

SELECT ISC.NormalizedPublisher, ISC.NormalizedName, ISC.NormalizedVersion, ISC.CategoryName, count(*) as ‘count’ FROM dbo.v_GS_Installed_Software_Categorized ISC WHERE ISC.FamilyName In (‘Unidentified’,'Uncategorized’) group by ISC.NormalizedPublisher, ISC.NormalizedName, ISC.NormalizedVersion, ISC.CategoryName order by ISC.NormalizedPublisher, ISC.NormalizedName, ISC.NormalizedVersion, ISC.CategoryName

  There are cases,where a computer might be added to different collection and it would be difficult to find out the computer is member of which collections.Try this report to list all the collection the computer member of. select a.CollectionId, b.Name from dbo.v_R_System r join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID join dbo.v_Collection b on…