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.QueryExpression
FROM         dbo.v_Collection INNER JOIN
                      dbo.v_CollectionRuleQuery ON dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID
ORDER 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()) )
-----------------------

Collections: query to check for obsolete clients

 

 

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

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 a collection of Existing

13. All SMS server system collection

14. All windows server, workstation,DP,BDP collection

15. All system’s with AD site based

16. Collection limiting to sub collection, linking

17.System’s are in “A” collection But not in “B” Collection & Vice versa

18. In collection “XYZ” Software installed system

19. In collection “XYZ” File inventory(s\w inventory based) installed system

20. In collection “XYZ” file specific method(H\w inventory based) system

21.XYZ patch Installed & Not Installed system

22. All windows update Agent version 7.6 below

23.XYZ user/group collection

Systems Part of What Collections

--Systems Part of What Collections

SELECT v_R_System.Name0, v_Collection.Name FROM v_FullCollectionMembership INNER JOIN v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID WHERE (v_R_System.Name0 = 'Systemname')

Collection : For software distribution status if system pending for reboot

For software distribution status if system pending for reboot

 

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 AS sms_r_system
     join SMS_StatMsg  as st on sms_r_system.Name = st.MachineName
     join SMS_AdvertisementStatusInformation sti on st.MessageID = sti.MessageID 
 where 
        sti.messagestate = 102
 

102 is the reboot pending code
you can get the complete list of codes using the following sql query

 select distinct messagestate,MessageStateName from dbo.v_AdvertisementStatusInformation

Collections based on software updates deployment status in Configuration Manager

ConfigMgr sccm patching status based collections

LastEnforcementMessageID
LastEnforcementMessageName

1        Enforcement started   

3        Waiting for another installation to complete

6    General failure

8    Installing update   

9    Pending system restart   

10  Successfully installed update   

11  Failed to install update   

12  Downloading update   

13  Downloaded update   

So in this example we would like to use the status of reboot pending, the WQL query for the collection should look like this:

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 AS sms_r_system    
    inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid    
where    
    c.LastEnforcementMessageID = 9

SMS 2003 Patching : Pending for reboot collection

A collection listing all servers/clients that were pending reboot (see query statement):

 

 

 

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_PatchStatus on SMS_G_System_PatchStatus.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PatchStatus.LastStateName = "Reboot Pending"    

FIND Collections and it’s Name in the console SQL Query

SELECT     Name, CollectionID
FROM         dbo.v_Collection

Sample Prompt option for Collection

begin
if (@__filterwildcard = '')
select v_Collection.CollectionID, v_Collection.Name from v_Collection order by v_Collection.Name
else
select v_Collection.CollectionID, v_Collection.Name from v_Collection
WHERE v_Collection.CollectionID like @__filterwildcard
order by v_Collection.Name
end 

To update the collection membership

To update the collection membership

1. Log on as administrator with a password of password.

2. On the Start menu, click SMS Administrator Console.

The SMS Administrator Console window appears.

3. In the console tree, expand Site Database, expand Collections, and then click All Systems.

The members of the All Systems collection appear in the details pane. Notice that the site server computer (<yourSMSServer>) and the Windows XP Professional client computer <yourSMSClient>appear as members.

4. On the Action menu, point to All Tasks, and then click Update Collection Membership.

The All Systems message box appears prompting to update subcollection membership.

5. Click OK, and then on the Action menu, click Refresh.

The collection membership is updated, and the current membership of the All Systems collection is displayed. Notice that the <yourSMSClient> computer is now displayed twice. Notice also that one of the instances is listed as being Obsolete and inactive. This is the old reference of the client.

Restoring Deleted All Systems Collection

Sometimes things just happen, sometimes you accidentally delete the All Systems collection because you were trying to do too many things at once.  I’ll fess up, I did it. 

Here’s how to restore the collection with the appropriate ID.  This solution was given to me by Microsoft Support.

Here is the VBS script that will do the restore:

####begin script

strSMSServer = "."
strParentCollID = "COLLROOT"
'This example creates the collection in the collection root.
'Replace COLLROOT with the CollectionID of an existing collection to make the new collection a child.

strCollectionName = "All Systems"
strCollectionComment = "This is the All Systems Collection."
Set objLoc = CreateObject("WbemScripting.SWbemLocator")
Set objSMS = objloc.ConnectServer(strSMSServer, "root\sms")
Set Results = objSMS.ExecQuery ("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")

For each Loc in Results
If Loc.ProviderForLocalSite = True Then
  Set objSMS = objLoc.ConnectServer(Loc.Machine, "root\sms\site_" & Loc.SiteCode)
End if
Next

Set newCollection = objSMS.Get("SMS_Collection").SpawnInstance_()

'Create new "All Systems" collection
newCollection.Name = "All Systems"
newCollection.OwnedByThisSite = True
newCollection.Comment = strCollectionComment
newCollection.CollectionID = "SMS00001"
path = newCollection.Put_

'Set the Relationship
Set newCollectionRelation = objSMS.Get("SMS_CollectToSubCollect").SpawnInstance_()
newCollectionRelation.parentCollectionID = strParentCollID
newCollectionRelation.subCollectionID = ("SMS00001")
newCollectionRelation.Put_

####end script

Once you’ve recreated the collection with the appropriate ID, then you’ll have to import the All Systems query for your membership rules.

Upgrade Configuration Manager client from SMS 2003

• Create a report that counts all client versions. (This is optional, just for information purposes).
  Report query is:

  SELECT TOP (100) PERCENT Client_Version0 AS [ConfigMgr client version], COUNT(Client_Version0) AS Total
FROM dbo.v_R_System GROUP BY Client_Version0, Client0 HAVING (Client0 = 1)
ORDER BY Total DESC, [ConfigMgr client version]

• Create a collection (“Older Clients” for example) with all system resources with a client version not 4.00.6487.2000.
  Collection query is:

  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 SMS_R_System.ClientVersion != "4.00.6487.2000"

  This way, system resources with older client version will be members of this collection.

• Created a package and program with ConfigMgr client upgrade with custom command line parameters.
  Program command line is:

  CCMSETUP.EXE /noservice SMSSITECODE=CFM SMSCACHESIZE=1024 SMSSLP=CFM.DOMAIN.COM SMSMP=CFM.DOMAIN.COM RESETKEYINFORMATION=TRUE

• Advertised it to “Older Clients” collection.

Now, as system resources with older client version are members of this collection they will receive the advertisement and will silently install the latest ConfigMgr client.

When the collection will have no system resources, I will know that all clients are upgraded. Also, I can check this by opening the same report from any browser on any computer.

Create a collection with systems without Adobe Reader 9

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_R_System.OperatingSystemNameandVersion not like "%Server%" and SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Adobe Reader 9%")