Collections:

  Computer that failed to run an advertisement: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 = ‘A0120005′ and LastStateName = ‘Failed’ Computer that ran an advertisement successfully: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 = ‘A0120005′ and LastStateName = ‘Succeeded’ Notice … Read more

SQL Query to find the collections Hierarchy : SCCM Colleections

SQL Query to find the collections Hierarchy   WITH folderHierarchy (CollectionID,[Name],ParentCollectionID,[Path])AS(       SELECT             CollectionID,             [Name],             ParentCollectionID,            CAST(‘/’+[Name]+’/’ AS VARCHAR(MAX)) AS [Path]      FROM            (SELECT                   CollectionID,                   [Name],                   ParentCollectionID              FROM v_Collection                  INNER JOIN v_CollectToSubCollect                   ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1      WHERE            ParentCollectionID = ‘COLLROOT’     UNION ALL   SELECT      child.CollectionID,      child.Name,      … Read more

Creating a Collection Query From SQL : Collections Migration from One server to other

Creating a Collection Query From SQL : Source : https://myitforum.com/cs2/blogs/cnackers/archive/2009/04/22/creating-a-collection-query-from-sql.aspx Credit to Dave Fuller for which this information is based upon. Creating a collection query that is based upon SQL can be done.  It’s a little complicated, but not all that bad once you’ve done it a few times.  There are many times where you … Read more

Finding the OU of a system in SMS/ConfigMgr

SELECT     dbo.v_R_System.Name0 AS [Computer Name], A.System_OU_Name0FROM         dbo.v_RA_System_SystemOUName AS A INNER JOIN                          (SELECT     ResourceID, MAX(LEN(System_OU_Name0)) AS len                            FROM          dbo.v_RA_System_SystemOUName                            GROUP BY ResourceID) AS B ON A.ResourceID = B.ResourceID AND LEN(A.System_OU_Name0) = B.len INNER JOIN                      dbo.v_R_System ON B.ResourceID = dbo.v_R_System.ResourceID         If you want just for one computer then…   SELECT     dbo.v_R_System.Name0 AS [Computer … Read more

ALL Collections WQL and collection ID and Names in a Site

SELECT     TOP (100) PERCENT dbo.v_Collection.Name, dbo.v_Collection.CollectionID, dbo.v_CollectionRuleQuery.RuleName, dbo.v_CollectionRuleQuery.QueryID,                       dbo.v_CollectionRuleQuery.LimitToCollectionID, dbo.v_CollectionRuleQuery.QueryExpressionFROM         dbo.v_Collection INNER JOIN                      dbo.v_CollectionRuleQuery ON dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionIDORDER BY dbo.v_Collection.Name, dbo.v_Collection.CollectionID

Last hardware inventory 14 days

———————————————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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-14,GetDate()) )———————–

You must know these collections as handy : Collections End to End

1.Client all system collection 2.Non client systems 3.Inactive systems 4.Obsolete system 5.Duplicate client Yes or No 6. Last hardware inventory 14 days 7.Last software inventory 14 days 8.Last Data discovery cycle. 9. XYZ package, XYZ Advertisement success systems’ collection 10. XYZ package, XYZ Advertisement Failed systems’ collection 11.XYZ subnet collection system 12.Add XYZ system’s to … Read more