Collection Queries (Колекции запросов ) SCCM2012

Author link:
http://myitforum.com/cs2/blogs/rbennett806/pages/collection-queries.aspx

OU-Based
If you wish to target specific sections of your Active Directory infrastructure, then you may wish to utilize collections based on computer account object OUs; making sure to watch out for oddities if using subOU levels.
select * from SMS_R_System where SMS_R_System.SystemOUName = "MyDomain.com/OU/SubOU"
Limit to collection: (your collection of healthy clients)
Notes: Remember, currently SCCM is Active Directory (AD) aware, not AD-integrated. This means there is still a lag in time between what's happening in AD and what the SCCM service knows. If you require a more immediate result, you may want to look into using scripts instead of the normal consoles.

Healthy Clients (3 queries)
Now, I'm a big believer that every collection query should be limited. What this does is ensures that your query is running against the desired pool of clients; which hopefully means that you won't get unexpected results. If you're new to SCCM (and maybe even if you aren't), I'd strongly recommend first coming up with a strategy on weeding out your inactive/unhealthy clients from your healthy ones. Then, once you have a collection of known, healthy and active clients, you can use that collection to limit all of your others. This way you don't spend time trying to deploy an item to a client that's not communicating correctly in the first place.
LAPTOPS: select * from SMS_R_System where ResourceID in (select SMS_R_System.ResourceID from SMS_R_System where (SMS_R_System.SystemOUName = "MyDomain.com/OU/SubOU" or SMS_R_System.SystemOUName = "MyDomain.com/OU2")) and ResourceID in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceID where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "8", "9", "10", "11", "12", "14", "18", "21" )) and ResourceID in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceID where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) < 90)
VIRTUAL PCS: select * from SMS_R_System where ResourceID in (select SMS_R_System.ResourceID from SMS_R_System where (SMS_R_System.SystemOUName = "MyDomain.com/OU/SubOU" or SMS_R_System.SystemOUName = "MyDomain.com/OU2")) and ResourceID in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceID where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "1", "2", "3", "4", "5", "6", "7", "13", "15", "16", "17", "19", "20", "22", "23", "24" )) and ResourceID in (select SMS_R_System.ResourceID 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.Model in ("Virtual Machine", "VMWare Virtual Platform", "Parallels Virtual Platform"))) and ResourceID in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceID where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) < 90)
OTHER: select * from SMS_R_System where ResourceID in (select SMS_R_System.ResourceID from SMS_R_System where (SMS_R_System.SystemOUName = "MyDomain.com/OU/SubOU" or SMS_R_System.SystemOUName = "MyDomain.com/OU2")) and ResourceID in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceID where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "1", "2", "3", "4", "5", "6", "7", "13", "15", "16", "17", "19", "20", "22", "23", "24" )) and ResourceID not in (select SMS_R_System.ResourceID 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.Model in ("Virtual Machine", "VMWare Virtual Platform", "Parallels Virtual Platform"))) and ResourceID in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceID where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) < 45)
Limit to collection: (not limited)
Notes: In our environment, while we could have used a complex process, we decided that since we're in one basic geographical location, that we'd deem a client as being "healthy" if it reported its Hardware Inventory information every 90 days for a laptop or virtual machine, and 45 days for anything else (like desktops).

Unhealthy Clients
These are basically the clients that aren't in the healthy collection, and probably need a little manual intervention (even if it's just reminding the end user to connect their laptop to the internet or the VPN).
select * from SMS_R_System where ResourceID in (select SMS_R_System.ResourceID from SMS_R_System where (SMS_R_System.SystemOUName = "MyDomain.com/OU/SubOU" or SMS_R_System.SystemOUName = "MyDomain.com/OU2")) and ResourceID not in (select ResourceID from SMS_FullCollectionMembership where CollectionID = "XXXXXXXX")
Limit to collection: (maybe use an OU-based collection)
Notes: Every environment is different, so keep in mind that these are just suggestions and examples.

Adobe Reader
You will need to use the correct name and version number to get the needed collection. You may easily find this information in the registry and/or by using Resource Explorer within the ConfigMgr console.
select * from SMS_R_System where SMS_R_System.ResourceID not in (select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Reader 10.0.1" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "10.0.1")
Limit to collection: (your collection of healthy clients)
Notes: I would strongly recommend staying away from the ">", "<", "+" operators when dealing with version numbers. While there's a lengthy answer as to why, the quick answer is that things like 9.0.123 is not a real number (multiple decimal points).

Mozilla Firefox
This is another sample that is identical to the Adobe Reader query so that you may see how you can quickly change a few pieces of information to obtain the desired result.
select * from SMS_R_System where SMS_R_System.ResourceID not in (select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Mozilla Firefox (3.6.13)" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "3.6.13 (en-US)")
Limit to collection: (your collection of healthy clients)
Notes: Try to stay away from mathematical operations using the version number.

Sun JAVA
Yet another query identical to the Adobe Reader and the Mozilla Firefox queries.
select * from SMS_R_System where SMS_R_System.ResourceID not in (select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Java(TM) 6 Update 20" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "6.0.200")
Limit to collection: (your collection of healthy clients)
Notes: If you need a more flexible query, try using the wildcard character (%). So instead of using "6.0.200" you could try using something like "6.0.%".

Internet Explorer 9Identify Windows 7 clients that already have IE 9.x installed.
select * from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceID inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_SoftwareFile.FilePath like "%\\Program Files\\Internet Explorer\\" and SMS_G_System_SoftwareFile.FileName like "iexplore.exe" and SMS_G_System_SoftwareFile.FileVersion like "9.%" and SMS_G_System_OPERATING_SYSTEM.Version like "6.1%"
Limit to collection: (your collection of healthy clients)
Notes: To find the clients that have not yet installed IE 9, simply change "like" to "not like" when checking the file verison number. Also, if you see oddities in the memberships, keep in mind that this is based off of the .EXEs polled and listed in the SCCM database, so when in doubt, use Resource Explorer within the ConfigMgr console to see if the "iexplore.exe" file has been inventoried.

Office 2007 Enterprise
This query will generate a collection of clients that have Office 2007 Enterprise edition installed.
select * 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 = "Microsoft Office Enterprise 2007"
Limit to collection: (your collection of healthy clients)
Notes: While similar to the Adobe Reader, Mozilla Firefox, and Sun JAVA collections, note that this one does not rely on the DisplayVersion information as we want to know where it's installed, regardless of the exact version.

SCCM Client Version
When deploying a newer version of the SCCM client, a collection may come in handy to locate those machines not up to speed.
select * from SMS_R_System where SMS_R_System.ResourceID not in (select ResourceID from SMS_R_System where SMS_R_System.ClientVersion = "4.00.6487.2000")
Limit to collection: (your collection of healthy clients)
Notes:

Not A Member of CollectionX
Often you have an existing collection that is being used for something else, and you simply want to generate a collection of machines that are NOT in that collection.
select * from SMS_R_System where ResourceID not in (select ResourceID from SMS_FullCollectionMembership where CollectionID = "XXXXXXXX")
Limit to collection: (your collection of healthy clients)
Notes: It is recommended that you build a query that's based on some other criteria, but this one can be used in a snap when you need to quickly deploy something to a set of clients.

Laptop Machines
This query will generate a collection that should contain only laptop devices.
select * from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceID where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "8", "9", "10", "14" )
Limit to collection: (your collection of healthy clients)
Notes: You may wish to ensure that the model types used in the sample query are correct for your environment.

Dell Client Machines
This query will generate a collection that should contain only Dell clients.
select * 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.Manufacturer like "Dell%"
Limit to collection: (your collection of healthy clients)
Notes: Depending on when the PC was purchased, and what BIOS level it's at, the manufacturer name could be one of several variations. Thus we use the wildcard character in this example in order to get around the problem.

x64 Operating Systems
This query will generate a collection of clients that are running a 64-bit operating system.
select * 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.SystemType = "x64-based PC"
Limit to collection: (your collection of healthy clients)
Notes: If you wanted a collection of 32-bit operating systems you could query for "x86-based PC" instead.

Windows 7 Operating Systems (without any Service Packs)
This query will generate a collection of clients that are running Windows 7, but have yet to install any service packs.
select * from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_OPERATING_SYSTEM.Version = "6.1.7600"
Limit to collection: (your collection of healthy clients)
Notes: If you wanted a collection of Windows 7 SP1 clients, use 6.1.7601 for the version number, or for ALL versions of Windows 7, you could toss in a wildcard and use "6.1%" (may need to also change "=" to "like"). Please note that this doesn't take into account servers. So if you have them within SCCM, double-check the version number you really want to use.

Windows XP Operating Systems
This query will generate a collection of clients that are running a version of the Windows XP operating system.
select * from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_OPERATING_SYSTEM.Caption like "% Windows XP %"
Limit to collection: (your collection of healthy clients)
Notes: This query uses the wildcard character so as to not be specific on the exact version.

File-Based
This query will generate a collection of clients that do not have the listed .EXE file within the given path.
select * from SMS_R_System where SMS_R_System.ResourceID not in (select SMS_R_System.ResourceID 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 = "MyFile.exe" and SMS_G_System_SoftwareFile.FilePath like "%\\Program Files\\Folder\\Subfolder\\%")
Limit to collection: (your collection of healthy clients)
Notes: If you wanted to get even more specific you could also check for the DisplayVersion.


NOTE 1: Dynamic collection queries are ones where a client should drop out of the collection automatically if it no longer meets the requirements. For example, if the client doesn't have ApplicationX installed, when the query runs it will add the client to the collection. Assuming there is an advertisement targeted to the collection that installs ApplicationX, once the client reports back to the SCCM server that ApplicationX has installed (normally via the Hardware Inventory), then the next time the collection query runs, the client will be automatically removed from the collection. The nice part about a dynamic collection is that if ApplicationX were to be removed, then once that is reflected in the SCCM database, the next time the query runs, the client will be added back into the collection again. Then, assuming that your advertisement is set to reoccur and to run again, the client will have ApplicationX installed again.
NOTE 2: The Add/Remove Programs information is pulled into the SCCM database via the Hardware Inventory scan, and not the Software Inventory scan as one might think.
NOTE 3: If you wish to verify information about an installed piece of software, you may use the Registry Editor on a machine and look in the following subkey locations:
32-bit Application: HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
64-bit Application: HKLM\SOFTWARE\ Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall
NOTE 4: I have intentionally tried to not use aliases for the names within the queries in an effort to help make understanding things a little easier for new SCCM Admins.