Where is my collection

Everyone asks in the forum, "I need to find a collection, how do I do that?" Well there are reports that you can run. You can ask your peers where they stuck it or you can look. If you have several levels it can take awhile.

So I decided to come up with a new tool: Where is my collection?

It will help you locate your collection using a simply HTA program. Simply type in a few characters of the collection and it will return the top 100 matches. Then you find your collection and click on the "Locate collection" It will then give you a read out like this:

Root Collection
--Adobe
----Adobe Professional
------Machines still needing Adobe Profressional

It is accomplished by using the SMS_CollectToSubCollect namespace. Generally this is used to create a subcollection. It occured to me that this property is also read only so it was a good choice to use.

I have the tool on my site as stand alone tool since no arguments are passed from the console to the machine via right click, but I have also included how to add this to the console. I only spent several hours on the tool one evening so if you see something that can be improved, please let me know.

Enjoy, and as always, use at your own risk :)

http://www.sccm-tools.com/tools/standalone/standalone-Whereismycollection.html

Since this is also echoed in the Advertisments and Packages I could also create a tool or expand this one. Some of this has already been solved in ConfigMgr 2012 so go check it out!!

Not all machines in Collection

Did ever create a collection and realize that not all the computers were appearing?
When you create a collection you need to make sure you use:

SMS_R_System.Name or in some cases SMS_R_System.NetbiosName

Using the SMS_G_System_COMPUTER_SYSTEM.Name or other similar options might not reveal all your systems. When creating collections of large amounts of machines use the SMS_R_System.

I have seen large collections lack 1 or even hundres of machines when using a SMS_G_System_XXXXX

Collections Based on Metered Data

Software last run more the X days ago
----------------
select distinct SMS_R_System.ResourceId, SMS_R_System.Name from SMS_R_System inner join SMS_G_System_SoftwareUsageData on SMS_G_System_SoftwareUsageData.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareUsageData.FileName = "sol.exe" and SMS_G_System_SoftwareUsageData.StartTimeLocal > DateAdd(dd,-6,GetDate())


Software NOT run in x days
-----------------
select distinct SMS_R_System.ResourceId, SMS_R_System.Name from SMS_R_System where SMS_R_System.ResourceId not in (select distinct SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_SoftwareUsageData on SMS_G_System_SoftwareUsageData.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareUsageData.FileName = "sol.exe" and SMS_G_System_SoftwareUsageData.StartTimeLocal > DateAdd(dd,-6,GetDate()))

Uninstall when not in a collection

Alot of admins would like to uninstall software when the computer is not in a collection. While this thought processes is sound it can't be done. ConfigMgr needs to send policy to machines that are in a certain collection.

So how do we get around this problem.
I want to point back to a blog post I have labeled, "Collections in reverse"
http://sms-hints-tricks.blogspot.com/2009/03/using-collections-in-reverse.html

What I do is simply have a collection of the software I want people to be allowed to have, say Google Earth.
Then I have a collection of people that are NOT in the google earth collection. Now when I place an "approved" machine in the google Earth collection nothing happens since this is a user based install via the web, but the computer is removed from the uninstall collection. Otherwise, the day after they install it the machine would kick off the uninstall.


So you see, you still need to create a collection of computers, in this example we have 2 the approved collection and the Uninstall collection.

Using collections in Reverse

We all know you can create a collection to uninstall software but how about a collection Authorized software. This basically due to Google Earth. We run our computers in a locked down user only mode, so they can't install software. Well Google Earth gets around this by installing to the applications folder and in the profile. Users download it and then never update it so we create a collection to allow only authorized users to keep it. Here is how we did it.

Parent Collection (Computers allowed to run Google Earth)
Child Collection (Query and find every machine that has it not in the parent and uninstall every day.


Parent Collection is based on some queries and some direct memberships. Collection id is SYS00447
Child collection:

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Google Earth" and ResourceId not in (select ResourceID from SMS_CM_RES_COLL_SYS00447)

The child is limited to All Worstations so I can pull in all the computers and then we use the "not in parent collection ID"

Have fun with it....

Collection query of multiple collections

Say you want to create a collection that was the combination of multiple collections. Well below is just one method you can use to pull systems from other collections.
You will first need to find the collection IDs and replace xxxxxxx1 with the ids


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.ResourceId in (select ResourceId from SMS_CM_RES_COLL_xxxxxxx1) or ResourceId in (select ResourceID from SMS_CM_RES_COLL_xxxxxxx2) or ResourceId in (select ResourceID from SMS_CM_RES_COLL_xxxxxxx3)

Add computer to a collection

I have vbscript on this blog that will give you the ability to add a computer to a collection but it command line based. I have created a HTA file that has 2 parts.

1. Allows the user to right click on a collection and add multiple computers
2. Allow the user to right click on a computer in a collection or query and add it to multiple collections

The output list is limited to 50 currently but you can change it per your enviroment. Also I am pulling System Resource from "All computers" collection. I am sure this could be modified to add groups and OUs and such.

Here is the link to look at the code and download the program
http://www.sccm-tools.com/tools/rightclick/rightclick-Collectionaddcomputers.html

I have some ideas to improve it so I might be posting those changes here.

Exclusions based on a Group or OU

All the computers that are only in the A and not the A & B?
Computer 1 Group A
Computer 2 Group B
Computer 3 Group A+B
Computer 4 Group A+B

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 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 where SMS_R_System.SystemOUName = "Group B" ) and SMS_R_System.SystemOUName = "Group A"

Cross link on my blog to other exclusions

http://sms-hints-tricks.blogspot.com/2007/10/excluding-computers-from-collection.html

Glitch in Software Distribution SP1

I don't think I saw this in RTM but it might be there.
Here are the steps to reproduce the problem.


1. Right click on your collection and select properties
2. Under membership rules create a new query for the collection
3. Type in the Name and set Resource Class to System Resource
4. Under Collection Limiting select Limit to Collection and browse to a collection to limit
5. Select Import Query Statement select any query to import and click ok
6. Now look at the Collection Limiting, is now set back to No collection limited

More Excluding Collections

Say you want to create a collection that will be all computer except certian OUs and maybe some other computers:

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 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 where SMS_R_System.SystemOUName = "domain/OU/OU" or SMS_R_System.SystemOUName = "domain/OU/OU2" or SMS_G_System_COMPUTER_SYSTEM.Name = "computer2")

now this assumes 1 computer. You could add multiple " or SMS_G_System_COMPUTER_SYSTEM.Name = "computer2") " but this is not really a well formed statement. Other ways to do this is use the "or SMS_G_System_COMPUTER_SYSTEM.Name in ("computer2","computer3") " and so forth.

Don't forget you can also do something like this if you want to exclude an entire collection :

and ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)

More cross link of exclusions
http://sms-hints-tricks.blogspot.com/2008/10/exclusions-based-on-group-or-ou.html

Add computer to collection, Vbscript

Edit: October 25, 2008
I have added a better way of doing this via a right click
Here at Sccm-tools.com. This uses a HTA file so you can right click on a computer and add it to collections or right click on a computer in a collection or query and add it to multiple collections.

*-------------

I didn't create the script but someone had asked if there was a way to add a computer to a collection. I have the .Net code to do it but here is the vbscript method. This can be used to add the current computer to a collection as soon as the computer has imaged or performed some other action.




' *************************************************************************
' === Initialize Variables and Constants
' *************************************************************************
Option Explicit ' --- Force Declaration of variables
' === Script arguments override ====
' These variables are for testing only. To test, assign values to the variables and then comment out
' the Comment-out the ValdateArguments()subrourtine. To restur, comment out the variables below, and
' uncomment the ValdateArguments() subroutine to allow original argument values to be passed to the script.
'TargetCollectionName = "#Test-AddUser"
'NewComputerName = "LT01385"
' *************************************************************************
' == SMS Envronment Variables
CONST SMSSiteCode = "xxx" ' --- SMS Server Central Site Code
CONST SMSServer = "xxxxx" ' --- SMS Server
' -------------------------------------------------------------
' === Script varables
DIM lLocator ' --- Object handle for WMI Scripting "Locator object" ?
DIM gService ' --- Object hanlde for WMI object model ConnectServer object ?
DIM ResID ' --- Store the SMS ResourceID for the desired resource
DIM RuleName ' --- Name for Membership Rule (label seen in SMS collection properties)
DIM AllComputersSet ' --- Object handle to store the query results of the SMS System_R_System table
DIM AllComputersItem ' --- Index variable used for the AllComputersSet Object Collection (Array)
DIM CollectionRule ' --- CollectionRule
DIM oCollectionSet ' --- Object handle to store collection names from table SMS_Collection
DIM CollListItem ' --- Index variable for Collection Object (Array)
DIM WshShell ' --- Object handle for WshShell object (Windows Script Host Shell)
DIM oFSO ' --- Object handle for the FSO (File System Object)
DIM AddMembRuleSuccess ' --- Flag to track added collection membership rule status
Public TargetCollectionName ' --- Store command-line argument for desired collection name to add a computer resource
Public NewComputerName ' --- Store command-line argument for desired computer resource to add to the collection
' === Script Environment
' *********************************************************************************************
' ===== Connect to provider namespace for local computer.
' *********************************************************************************************
Set lLocator = CreateObject("WbemScripting.SWbemLocator")
Set gService = lLocator.ConnectServer(SMSServer, "Root\SMS\Site_" & SMSSiteCode)
' -- /// FileSytemObject already instantiated in subroutine ScriptPath() into object handle oFSO
' ********************************************
' ===== Script Start =======================
' ********************************************
ValdateArguments() ' --- Load command-line arguments into variables
ValidateComputerResource() ' --- Find computer name match in SMS "All Systems" table SMS_R_System
FindCollandAddMembRule() ' --- Find specific collection and add membership rule to the collection
wscript.quit
' *************************************************************************************
' *************************************************************************************
' Subroutines
' *************************************************************************************
' *************************************************************************************
' *************************************************************************************
' **** Validate command-line parameters and load them into variables to be used
' **** by the script. Otherwise, display an error message and exit the script
' *************************************************************************************
Sub ValdateArguments() ' --- Load command-line arguments into variables
If wscript.arguments.count<> 2 Then
wscript.echo "Two arguments please - The collection followed by Computer Name"
wscript.quit ' --- quit if invalid number of arguments
Else
TargetCollectionName = wscript. arguments(0)
NewComputerName = wscript. arguments(1)
End if
End Sub
' **************************************************************************
' **** Search the SMS_R_System table for a match to the computer name passed by the
' **** command-line argument, then store the corresponding SMS ResourceID
' **** in a variable.
' **************************************************************************
Sub ValidateComputerResource() ' --- Find computer name match in SMS "All Systems" table SMS_R_System
' --- Create object with query results from the SMS SQL table SMS_R_System
Set AllComputersSet = gService.ExecQuery("Select * From SMS_R_System WHERE Name LIKE ""%" + NewComputerName + "%""")
' --- Iterate through the SMS_R_System table resultset (loaded into the variable "AllComputersSet")
' and match the name to be added. Then retreive the ResourceID for the corresponding computer record.
For Each AllComputersItem In AllComputersSet
If UCase(AllComputersItem.Name) = UCase(NewComputerName) Then
ResID = AllComputersItem.ResourceID
RuleName = "ResourceID = " & ResID & " " & AllComputersItem.NetBiosName
End If
Next
End Sub
'***************************************************************************
'**** Walk through the the whole list of SMS collections and search for a match to the
'**** desired collection. If the desired collection is sucessfully found, then
'**** add the Membership rule created in the subrioutine CreateSMSDirectRule() to the
'**** existing matched collection.
'***************************************************************************
Sub FindCollandAddMembRule() ' --- Find specific collection and add membership rule to the collection
' --- Create object with query results from the SMS_Collection table
Set oCollectionSet = gService.ExecQuery("Select * From SMS_Collection")
AddMembRuleSuccess = "False" ' --- Initialize flag to indicate Added Collection Membership Rule Failed
' --- Walk through the list of SMS collections and find a match to the collection name passed by the
' command-line argument. If the collection exists, then add the new direct rule that includes
' the new computer name to the collection.
For Each CollListItem In oCollectionSet
If CollListItem.Name = TargetCollectionName Then
'***************************************************************************
'**** Create a direct membership rule (spawn a blank instance) to add to an existing collection.
'**** Then give that instance the values it needs - this is REQUIRED
'***************************************************************************
Set CollectionRule = gService.Get("SMS_CollectionRuleDirect").SpawnInstance_()
CollectionRule.ResourceClassName = "SMS_R_System"
CollectionRule.RuleName = RuleName
CollectionRule.ResourceID = ResID
' ***** Add the direct membership rule to matched collection. ************
CollListItem.AddMembershipRule CollectionRule
If Err.Number = 0 Then ' --- If Error = 0, then adding membership was successful.
AddMembRuleSuccess = "True" ' --- Set flag to "True" to indicate memb. rule added Successfully
End If
End If
Next
End Sub

Excluding computers from a collection

There are many ways to exclude computers from a collection. One way is to have a sub collection and exclude that one. The next is to create a sub select statement with a few machines..

where SMS_G_System_COMPUTER_SYSTEM.Name not in ( "comp1", "comp2" )

Assuming you have a collection of computers and you want to exclude certain ones then simply append this statement to your Where clause. This will remove these two computer from your list. Test it and see what happens..

Computers with Old Scan catalogs

This collection will house computers with the scan packager version smaller than 47 and if they have been HW inventoried in the last 60 days. This assumes that your current scan package is 47. Send the scan advertisment to this collection.
-------------------------
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_SCANPACKAGEVERSION on SMS_G_System_SCANPACKAGEVERSION.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SCANPACKAGEVERSION.PackageVer < "47" and SMS_G_System_SCANPACKAGEVERSION.PackageID = "SMS00032" and SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-60,GetDate())
--------------
What does this get us. You can now see what computers are not updating their scan package. Maybe they are offline, or broken. You can now send an adv every 3 hours to this to make sure these computers scan as soon as they are turned on. Or send down the fix as soon as they turn on.
If you will notice the HTA file I created a while back: We use this to push a popup to the user informing them that their machine is out of date and it will be scanned, please don't restart. "if you continue to see this please contact tech support." This gives you a more active approach to finding systems that have problems

Out of date Hardware Inventory sms

Here is a query to create a collection of computers (last hardware scan > 60 days ) that haven't had there HW inventory updated in 60 days, you can change this to what ever time period you want. This will work on both SCCM or SMS



---------------------------------------------
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,-60,GetDate()) )

-----------------------
You could also add a query to this collection to pull all computers with no SMS client then you have all your comptuers in one collection to work with. You could force HW scans on the collection, force Client installs or whatever maintenance you need.

DateAdd in Query/Collection

Many of you have already discovered some of the nice features and added benifits of upgrading to Sp 2 for SMS 2003. What you might not be aware of is that you can now use the DateAdd function in your query. The only down side is that you can only add it while editing the query manually. There is no WYSISYG method of adding or editing it. So once you add it you no longer have the editor.

Below is an example of how to use it to keep a computer in a collection for 2 days based on the MCNSDATA.ImageDate. I will describe this more later. Basically we are using a custom MIF file to add a computer to a new computer collection where it will stay until the end of the second day. It will then automatically remove itself. Expect to see more of the "New Computer" subject. I will have more about what we are doing with new computer provisioning.
------------------------------
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 inner join SMS_G_System_MCNSDATA on SMS_G_System_MCNSDATA.ResourceID = SMS_R_System.ResourceId where SMS_G_System_MCNSDATA.ImageDate >= DateAdd(dd,-2,GetDate())
---------------------------------

Collection of Computers based on Users

SMS has a nice feature that allows you to create a collection based on users. That way when a users logs into a computer SMS will then run an advertisement for whatever computer they are on. The problem is maybe you want to have a collection of computers not the users. The worst flaw this the collection query I have created below and using collections based on users is that if a person logs in to another machine, for whatever reason, SMS will find them and push software to them.
That in mind, here is a collection that will take any OU group of users and create a collection of computers.


---------------------------------------
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.UserName in (select UniqueUserName from SMS_R_User where UserOUName = "xxxx /SECURITY USERS & GROUPS” )
--------------------------------------
Windows User account and group discovery must be enabled and run on your server to use this.

How does this work. It first grabs the list of users. Then matches them to the comptuers that SMS currently sees them logged into. To change this to Last Logged in user you need to change the SMS_G_System_Computer_System to SMS_R_System.LastLogonUserName for computers. This will give you last logged in user.

"Limit to Collection" for all your computer Workstations or further limit it to based on departments

If you don't know what group then you can create a query of just (select UniqueUserName from SMS_R_User where UserOUName = "") click values until you have the value you need and paste it into the larger query.

Collection where XYZ needs to install

Sometimes you want to create a collection that will find all the computers that don't have a particular software installed and push it to it. This is a good dynamic way to use SMS instead of direct computer addition.

Assuming you have Adobe Reader setup to install to all computers you can key this off of the Workstation collection or all computers if you wish. You notice that I use the "LIKE" condition instead of the "equals." I do this because you never know what version you might have. Look at Reader 7.09, even though you update your package with the latest reader you would need to change the collection. This way it will always look for the computers with no reader. This is assuming of course, that you push the updates to reader and not uninstal and reinstall the whole application each time.
--------------------------------
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 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 8%")
--------------------------------