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

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

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

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

———————————————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()) )———————–

    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 where obsolete = 1