If you are looking for a collection to query all the machines only from a particular OU not the SUB OU .Let say ,I have Top OU called Workstations\comp(120 systems) in eskon.net domain and in turn it has 3 sub OU's OU1(10 systems),OU2(20 systems),OU3(49 systems). Here is the simple query to get machines only from TOP OU called workstations.…

Here is the collection that gives you a list of machines with specific staus message ID: select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID  WHERE AdvertisementID = 'GSA2035F' and LastStatusMessageID = 10009 10009  is success, replace advertisementID with your advertisement ID. With specific State name: SELECT sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client FROM sms_r_system as sys…

some times its hard to know the systems which are having issues in reporting their inventory and even some systems doesnt reporte anything to the site server though the client is installled on it. Here is the collection which gives you all computers which doesnt report inventory for x days(taken 15days). 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…
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 =…

Query based versus Direct membership: Direct membership rules should be created when you want to target a very specific user or SMS client. Direct membership rules are not automatically updatable; therefore, any change of targeted resources is a manual process. If a targeted resource(workstation) is removed from the site database, the associated direct membership rules…

  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…