This post provides various WQL queries to generate custom
SCCM collections (07/12) to group the machines in a collection.
SCCM Collection query computer name:
The following WQL query will add the machines to a collection based on their naming conventions. This query is useful if we want to only some machines to a collection using a query with starting names (ABC-XXXX) or machines named using business unit (HR-XXXXXX).
All of the queries from this post have tested in my lab. If
you see any errors in a query please post a comment so I can fix them.
This page will be updated periodically.
Please note; when you are copying a query from a web browser
you need to make sure that the browser is not adding any extra characters.
SCCM Collection query for various models in one collection:
The following WQL query will populate a collection with all the dell Latitude and XPS models. Change the models names as required.
******************************************************************************
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.Model like "Latitude%" or SMS_G_System_COMPUTER_SYSTEM.Model like "XPS%"
*******************************************************************************
SCCM Collection query for all workstations:
The following WQL query for SCCM will poplulate all the workstaions into one collection.
This query will be useful when
*******************************************************************************
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.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%"
*******************************************************************************
SCCM Collection query for various models in one collection:
The following WQL query will populate a collection with all the dell Latitude and XPS models. Change the models names as required.
******************************************************************************
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.Model like "Latitude%" or SMS_G_System_COMPUTER_SYSTEM.Model like "XPS%"
*******************************************************************************
SCCM Collection query for all workstations:
The following WQL query for SCCM will poplulate all the workstaions into one collection.
This query will be useful when
*******************************************************************************
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.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%"
*******************************************************************************
This WQL query will create a SCCM collection for all the
64 bit Workstations in the organization.
To create a collection for 32-bit workstations, replace
the “x64” with “X32”.
*********************************************************************************
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_SYSTEM on
SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where
SMS_G_System_SYSTEM.SystemType = "X64-based PC"
*********************************************************************************
This WQL query will create a SCCM collection for all the
VMWare Workstations in the organization.
To create a collection for specific model workstation,
replace the “VMware virtual Platform” with a model name of the machine you want
to create a 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_COMPUTER_SYSTEM on
SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where
SMS_G_System_COMPUTER_SYSTEM.Model like "VMware Virtual Platform"
*********************************************************************************
This query makes a collection of all Windows 7 machines
on different domains.
You can rename
DOMAIN1 to DOMAIN5 as you
needed.
************************************************************
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.ResourceDomainORWorkgroup like
"DOMAIN1" or SMS_R_System.ResourceDomainORWorkgroup like
"DOMAIN2" or SMS_R_System.ResourceDomainORWorkgroup like
"DOMAIN3" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN4"
or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN5") and
SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT
Workstation 6.1"
************************************************************
SCCM Collection for Windows 7 machines from a OU and multiple domains:
This WQL Query will create a collection for all Windows 7 machines with combination of OU and domains.
*********************************************************
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.SystemOUName like "AD.YOURDOMAIN.COM/OU" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN1" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN2")and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation 6.1"
*********************************************************
SCCM Collection for Laptops:
This WQL Query will create a collection for all Windows 7 machines with combination of OU and domains.
*********************************************************
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.SystemOUName like "AD.YOURDOMAIN.COM/OU" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN1" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN2")and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation 6.1"
*********************************************************
Below WQL query creates a collection for all the laptops
in the environment based on the System Enclosure attribute and Chassis type.
*********************************************************
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_SYSTEM_ENCLOSURE on
SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = SMS_R_System.ResourceId where
SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes like "8 , 9, 10, 11, 12, 14,
18, 21"
**********************************************************
Below WQL query creates a collection for all the desktops
in the environment based on the System Enclosure attribute and Chassis type.
***********************************************************
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_SYSTEM_ENCLOSURE on
SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where
SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes like
"('3','4','5','6','7','15','16')"
************************************************************
Below WQL query creates a collection with all inactive
machines
for 90 days in the environment. The 90 days can be modified as required by
editing the last line of the query.
*************************************************************
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.Name in (select Name from SMS_R_System where
((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=90) and AgentName =
"SMS_AD_SYSTEM_DISCOVERY_AGENT")) and SMS_R_System.Name in (select
Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate())
>=90) and AgentName = "Heartbeat Discovery"))
*************************************************************
This is an easy WQL query to get the machine details
which hasn’t been re-booted for 30 days.
We can change the 30 days at end of the statement to
adjust desired days.
This query is targeted only for workstations such as XP,
Vista and Windows 7.
*********************************************************
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_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where
(SMS_G_System_OPERATING_SYSTEM.Caption like "%xp%" or
SMS_G_System_OPERATING_SYSTEM.Caption like "%vista%" or
SMS_G_System_OPERATING_SYSTEM.Caption like "%windows 7%") and
(DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) >30)
**********************************************************
The following SCCM Collection query is useful when you
have many machines without SCCM Client on the client machines.
Just create a new collection using the below query;
***********************************************************
select
R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client
from SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on
s2.Name = s1.Name where s1.Name =
s2.Name and s1.ResourceId != s2.ResourceId
and R.Client = null
***********************************************************
The following WQL query will add all the machines with duplicate
machine name.
***********************************************************
select
R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client
from SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on
s2.Name = s1.Name where s1.Name = s2.Name
and s1.ResourceId != s2.ResourceId and
R.Client = null
****************************************************************SCCM Collection query computer name:
The following WQL query will add the machines to a collection based on their naming conventions. This query is useful if we want to only some machines to a collection using a query with starting names (ABC-XXXX) or machines named using business unit (HR-XXXXXX).
Change the values ( ABC and DEF ) as your requirements.
*************************************************************************
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.Name like "%HR%" or
SMS_R_System.Name like "%ABC%" or SMS_R_System.Name like
"%DEF%"
**************************************************************************
No comments:
Post a Comment