Effective WQL Query Examples for SCCM

Windows Query Language (WQL) is a powerful tool for managing Configuration Manager (SCCM) environments. It enables precise data retrieval from the SCCM database. Mastering effective WQL queries is essential for maintaining large Config Manager server environments. This post delivers practical examples and explanations to optimize your SCCM operations.

Understanding Effective WQL Queries in SCCM

WQL is a subset of SQL designed for querying Windows Management Instrumentation (WMI). SCCM leverages WQL to extract system and configuration data. Queries must be efficient and accurate to avoid performance degradation in large environments.

Effective WQL queries follow these principles:

• Use specific class names and properties.

• Filter results with precise WHERE clauses.

• Avoid SELECT * to reduce data load.

• Use JOINs carefully to link related classes.

• Test queries in the SCCM console or PowerShell before deployment.

  
  

These practices ensure queries run quickly and return relevant data.

Eye-level view of a server rack with blinking network equipment

Practical WQL Query Examples for SCCM

Below are several WQL query examples tailored for common SCCM tasks. Each example includes a brief explanation and syntax.

1. Query All Active Devices

Retrieve all active devices in the SCCM database.

```sql

SELECT * FROM SMS_R_System WHERE IsActive = TRUE

```

This query filters devices marked as active. Use this to target only currently managed systems.

2. Query Devices by Operating System

Find devices running Windows 10.

```sql

SELECT * FROM SMS_R_System WHERE OperatingSystemNameandVersion LIKE "%Windows 10%"

```

This query uses a wildcard to match any Windows 10 version. Modify the string for other OS versions.

3. Query Devices with Low Disk Space

Identify devices with less than 10 GB free on the C: drive.

```sql

SELECT * FROM SMS_G_System_LOGICAL_DISK WHERE DeviceID = "C:" AND FreeSpace < 10737418240

```

FreeSpace is in bytes. This query helps target devices needing disk cleanup.

4. Query Devices by Installed Software

List devices with a specific application installed, e.g., Microsoft Office.

```sql

SELECT * FROM SMS_G_System_ADD_REMOVE_PROGRAMS WHERE DisplayName LIKE "%Microsoft Office%"

```

This query assists in software inventory and compliance checks.

5. Query Devices by Last Hardware Scan

Find devices that have not reported hardware inventory in the last 30 days.

```sql

SELECT * FROM SMS_R_System WHERE DATEDIFF(day, LastHardwareScan, GETDATE()) > 30

```

This query helps identify devices potentially offline or misconfigured.

Optimizing Queries for Large Environments

Large Config Manager environments require optimized queries to maintain performance. Follow these recommendations:

• Limit returned columns: Specify only needed properties instead of SELECT *.

• Use indexed properties: Filter on properties indexed by SCCM for faster results.

• Avoid complex joins: Use subqueries or multiple queries if necessary.

• Test query execution time: Use the SCCM console query editor or PowerShell to measure performance.

• Cache frequent queries: Store results when possible to reduce repeated database hits.

  
  

Example of a refined query limiting columns:

```sql

SELECT Name, ResourceId, OperatingSystemNameandVersion FROM SMS_R_System WHERE IsActive = TRUE

```

This returns only essential device details, reducing data transfer.

Close-up view of a computer screen displaying SCCM query results

Leveraging WQL Queries in SCCM Reports and Collections

WQL queries are integral to SCCM collections and reports. Collections use queries to dynamically group devices or users based on criteria. Reports use queries to extract data for analysis.

Creating Dynamic Collections

Example: Create a collection of Windows 10 devices with low disk space.

```sql

SELECT SMS_R_System.ResourceId, SMS_R_System.Name FROM SMS_R_System

INNER JOIN SMS_G_System_LOGICAL_DISK ON SMS_G_System_LOGICAL_DISK.ResourceId = SMS_R_System.ResourceId

WHERE SMS_R_System.OperatingSystemNameandVersion LIKE "%Windows 10%"

AND SMS_G_System_LOGICAL_DISK.DeviceID = "C:"

AND SMS_G_System_LOGICAL_DISK.FreeSpace < 10737418240

```

This query joins system and disk data to target specific devices.

Building Custom Reports

Use WQL queries in SQL Server Reporting Services (SSRS) reports to provide actionable insights. Ensure queries are optimized to avoid long report generation times.

Final Thoughts on WQL Query Usage

Mastering WQL queries is critical for managing complex SCCM infrastructures. Efficient queries improve system responsiveness and data accuracy. Regularly review and refine queries to adapt to changing environments.

For detailed guidance and more wql query examples for sccm, consult official documentation and community resources.

Effective query design supports seamless operations, future-proofing, and long-term support of your Configuration Manager environment.