Example: Computers that run Windows 7
Use the following query to return the NetBIOS name and operating system version of all computers that run Windows 7.
Tip: To return computers that run Windows Server 2008 R2, change %Workstation 6.1% to %Server 6.1%
SELECT *
FROM SMS_R_System
WHERE
OperatingSystemNameAndVersion LIKE ‘%Workstation 6.1’
Example: Computers with a specific software package installed
Use the following query to return the NetBIOS name of all computers that have a specific software package installed. This example displays all computers with a version of Microsoft Visio installed. Replace %Visio% with the software package you want to query for.
Tip: This query searches for the software package by using the names that are displayed in the programs list in Windows Control Panel. The term DISTINCT is used to ensure that only one result is returned per computer. The query also uses the AS term to create an alias to enhance readability of the query.
SELECT DISTINCT *
FROM SMS_R_System AS Sys
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS AS ARP ON
ARP.ResourceId = Sys.ResourceId
WHERE
ARP.DisplayName LIKE ‘%Visio%’
Example: Computers WITHOUT a specific software package installed
Use the following query to return the computers that do not have a specific software package installed. This example displays all computers that do not have a version of Microsoft Visio installed. Replace %Visio% with the software package you want to query for.
Tip: This query uses the ability to use sub-queries to filter results. The key is the use of the NOT IN operator is used to filter only computers not found in the sub-query (that is the query after the WHERE clause between brackets).
SELECT *
FROM SMS_R_System AS Sys
WHERE
Sys.ResourceID NOT IN
(
SELECT ResourceID
FROM SMS_G_System_Add_Remove_Programs AS ARP
WHERE ARP.DisplayName LIKE ‘%Visio%’
)
Example: Computers that are in a specific Active Directory Domain Services Organizational Unit (OU)
Use the following query to return the NetBIOS name and OU name of all computers in a specified OU. Replace the OU Name in the example with the name of the OU that you want to query for.
SELECT DISTINCT *
FROM SMS_R_System AS Sys
WHERE
Sys.SystemOUName = ‘Contoso.Domain.local/OUName’
Example: Computers with a specific NetBIOS name
Use the following query to return the NetBIOS name of all computers that begin with a specific string of characters. In this example, the query returns all computers with a NetBIOS name that begins with ABC.
SELECT DISTINCT *
FROM SMS_R_System AS Sys
WHERE
Sys.NetbiosName LIKE ‘ABC%’
Example: All computers that are laptops (method 1)
Use the following query to return the all computers that are laptops. This is achieved by finding all machines that have batteries.
This query uses hardware inventory of the Win32_Battery WMI class.
Tip: This query requires that hardware inventory be extended to include the Win32_Battery class (and at least the DeviceID property). To do this, see one of the following TechNet articles for System Center 2012 Configuration Manager This link is external to TechNet Wiki. It will open in a new window. or Configuration Manager 2007 This link is external to TechNet Wiki. It will open in a new window. .
SELECT DISTINCT *
FROM SMS_R_System AS Sys
INNER JOIN SMS_G_System_Battery AS Batt ON
Batt.ResourceId = Sys.ResourceId
WHERE
Batt.DeviceID LIKE ‘%’
Example: All computers that are laptops (method 2)
Use the following query to return the all computers that are laptops. This is achieved by determining the chassis type defined by the hardware manufacturer.
This query uses hardware inventory of the Win32_SystemEnclosure WMI class. This query identifies devices classified as ‘Laptop’ or ‘Notebook’ or ‘Portable’ – there are additional classes that a hardware vendor may choose to identify their devices. A complete list of chassis types can be found in the MSDN documentation of the Win32_SystemEnclosure class here This link is external to TechNet Wiki. It will open in a new window. .
Tip: This query uses the Configuration Manager WQL extension IN operator, which allows for case like query.
SELECT DISTINCT *
FROM SMS_R_System AS Sys
INNER JOIN SMS_G_System_System_Enclosure AS Case ON
Case.ResourceId = Sys.ResourceId
WHERE
Case.ChassisTypes IN (’10’, ‘9’, ‘8’)
Example: All computers that are virtual machines
Use the following query to return the all computers that are running virtual machines (either Microsoft or VMWare). This is achieved by determining the manufacturer of the guest’s “hardware” as reported by the virtual BIOS.
This query uses hardware inventory of the Win32_ComputerSystem WMI class. A complete list of properties can be found in the MSDN documentation of the Win32_ComputerSystem class here This link is external to TechNet Wiki. It will open in a new window. .
SELECT *
FROM SMS_R_System AS Sys
INNER JOIN SMS_G_System_Computer_System AS CompSys ON
CompSys.ResourceId = Sys.ResourceId
WHERE
(CompSys.Manufacturer = ‘Microsoft Corporation’
OR CompSys.Manufacturer = ‘VMware, Inc.’)
http://social.technet.microsoft.com/wiki/contents/articles/12050.example-wql-queries-for-configuration-manager.aspx
Leave a Reply