Share via

Example WQL Queries for Configuration Manager

  • Article

This page is based on the example queries section in the How to Create Queries in Configuration Manager topic on TechNet. You can copy and paste these WQL queries, and edit if necessary, so we can use them in our own Configuration Manager hierarchy to run ad-hoc queries or create collections from them.

Add new sections for our own WQL example queries, with an explanation of what they do and how they work. Also, add any other notes or callouts that other people might need to know for learning purposes or if they want to run them in their own environment. For example, some queries might be suitable for System Center 2012 Configuration Manager but not Configuration Manager 2007, and some queries that we had to use for Configuration Manager 2007 can now be replaced with new functionality in System Center 2012 Configuration Manager.

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 the 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 we 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 we 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 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 or Configuration Manager 2007. 

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 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.

Tip: This query uses the Configuration Manager WQL extension IN operator, which allows for a 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 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.

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.')