What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
For a normal saved Configuration Manager (SCCM) query, use the console’s built-in query wizard—not SQL Server Management Studio (SSMS). Open Monitoring > Queries > Create Query, choose the object type, build criteria and result attributes, and use Show Query Language when you need to edit the underlying WQL directly. This is the supported SSMS-free method for administrative queries.
There is an important terminology boundary: the console query wizard uses WQL (Windows Management Instrumentation Query Language), CMPivot uses a subset of KQL, and Configuration Manager report statements use SQL against SQL Server views. WQL and KQL are not interchangeable with report SQL. Choose the route that matches the data and outcome you need.
First, identify which kind of “SQL query” you need
People often call every Configuration Manager search a SQL query, but the product has three different query paths. Choosing the wrong one is the main reason an SSMS-free attempt fails.
| Route | Best fit | Language | What it produces |
|---|---|---|---|
| Configuration Manager console query wizard | Saved administrative queries, device or user criteria, joins and reusable result sets | WQL | A query object in the console that can be run repeatedly |
| CMPivot | On-demand inspection of current client state across a target collection | Subset of KQL | An interactive result set, with entity discovery, IntelliSense and export options |
| ConfigurationManager PowerShell | Scripted creation or execution of repeatable queries | WQL through the SMS Provider | Automation that follows Configuration Manager role-based access |
| SQL Server Query Designer | Report statements that read Configuration Manager SQL views | SQL | SQL used by reporting and database-view analysis |
If you need a saved query for collections, device searches or console administration, the first route is the easiest. If you need a live answer from clients, use CMPivot. If you specifically need SQL view syntax for a report, the SSMS-free console wizard is not a substitute.
#1 Best Overall
- Book - powershell for sysadmins: workflow automation made easy
- Language: english
- Binding: paperback
Create a saved SCCM query in the console
1. Open the query workspace
- Start the Configuration Manager console with an account that has permission to create and run queries.
- Go to Monitoring > Queries.
- Select Create Query in the ribbon or context menu.
- Enter a descriptive name and, if offered by your current-branch version, a comment that explains the query’s purpose.
Interface labels can vary slightly between Configuration Manager current-branch releases, but the Monitoring, Queries and Create Query path is the standard workflow.
2. Select the object type
Choose whether the query should return devices, users or another object exposed by your site’s SMS Provider. The object type determines which attributes are available in the statement editor. Start with the narrowest object type that answers the question; a device query is easier to validate than a broad query that mixes unrelated resources.
3. Open the statement editor
Choose Edit Query Statement. The editor lets you define the result columns, criteria and joins without typing the entire statement. Add only the attributes you need to identify or act on the returned resources. Fewer columns make the result easier to read and reduce accidental disclosure when the query is shared.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →4. Add result attributes
Use the result-attribute controls to select fields such as a resource name, identifier, operating-system detail or inventory value that is available for the selected object type. Treat the list as a schema exposed by the SMS Provider rather than as a list of arbitrary SQL Server columns. If an attribute is not offered, the console query cannot simply reference it as a report-view column.
5. Add criteria deliberately
Use the criteria tab to add one or more conditions. Combine conditions with the available logical operators and verify whether the editor is building an AND or OR branch. A condition should reflect a stable administrative rule—for example, a naming pattern, ownership field or inventory property—not a temporary value that will silently become stale.
Do not leave criteria blank unless you genuinely intend to return every object in the selected scope. Microsoft explicitly warns: “If you create a query that contains no criteria, the query will return all devices in the All Systems collection.” An unrestricted query can produce a large result and make it easy to target the wrong devices later.
6. Use Show Query Language when you need direct WQL
Select Show Query Language in the statement editor to view the generated WQL. This is the closest equivalent to typing a statement directly, while still using the console and the SMS Provider. You can inspect the generated statement, make a controlled edit, and return to the visual controls to check the result.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Remember that WQL is not unrestricted SQL. Microsoft describes WQL as “SQL for WMI.” Configuration Manager sends the statement through the SMS Provider, which applies the provider’s classes, properties, relationships and permissions. A query that would be valid against a SQL Server table or view may be invalid WQL, even if the words look similar.
7. Save, run and validate
- Save the query after reviewing its name, object type, attributes and criteria.
- Run it from Monitoring > Queries and inspect the returned rows.
- Compare a few results with the corresponding device or user records in the console.
- If the query will drive collection membership, use the query-result preview where available before assigning it to a production collection.
- Document the intended scope and the date of the inventory data used by any criteria.
A query can be syntactically correct and still be operationally wrong. Check for unexpected blank properties, duplicate rows from joins and resources that should have been excluded.
Control scope before a query can affect devices
Configuration Manager queries and collections are powerful because they can identify groups for deployments, compliance actions and maintenance. Build a scope check into your process:
Rank #3
- Start with a small test collection or a limited naming range.
- Review the result count and several representative rows.
- Confirm that the limiting criteria use current inventory values.
- Only then use the query as a collection membership rule or deployment target.
- Recheck the query after inventory cycles or schema changes; an old inventory value can change membership without any edit to the statement.
Role-based access still matters. The console shows only the objects and actions your account is authorized to use, and the SMS Provider evaluates the request in that security context.
Free tools Windows power users keep installed
One-click scans. No signup required.
Use CMPivot for current client data
A saved WQL query answers questions from Configuration Manager’s provider and inventory data. When the real question is “What is true on these clients right now?”, CMPivot is usually the better built-in tool.
Start a CMPivot session
- In the Configuration Manager console, select a target device collection.
- Start CMPivot from the collection’s actions.
- Use the entity and property browser to discover the data exposed by the selected clients.
- Compose a query with the available KQL subset and IntelliSense assistance.
- Run it, inspect the returned device rows, and export the result if you need to retain a snapshot.
CMPivot can return real-time client data as well as cached hardware-inventory data, so read the entity description and timestamp before treating a value as current. It is an interactive collection session, not a replacement for a saved console query. CMPivot also requires the appropriate permissions and an eligible collection and site context; confirm the prerequisites for your Configuration Manager current-branch version.
When CMPivot is the wrong choice
Do not use CMPivot when you need a persistent query object, a collection membership rule or a repeatable administrative definition. Save a WQL query for those jobs. Conversely, do not expect a saved WQL query to report a volatile client state that has not been inventoried or provided through the SMS Provider.
Automate WQL with ConfigurationManager PowerShell
For repeatable administration, the ConfigurationManager PowerShell module provides cmdlets that operate through the SMS Provider. New-CMQuery creates a saved query, and Invoke-CMWmiQuery runs a WQL query in the Configuration Manager context. This preserves the provider’s role-based access model instead of bypassing it with direct database credentials.
Discover the parameters on your installed module
Cmdlet parameters can vary with the Configuration Manager current-branch release and module version. From a PowerShell session that has the ConfigurationManager module available, inspect the local syntax first:
Get-Help New-CMQuery -Full
Get-Help Invoke-CMWmiQuery -FullThen connect to the appropriate site drive and use the WQL expression shown by the console’s Show Query Language view. Keeping the statement in one documented script makes changes reviewable and lets you test in a nonproduction collection before creating a deployment-facing query.
PowerShell safeguards
- Run the module and cmdlets under an account that has the required Configuration Manager security roles.
- Store the WQL statement in a variable or file so it can be reviewed before execution.
- Log the query name, target collection and execution time.
- Do not assume a script that worked on one site has the same provider classes or permissions on another site.
Why report SQL still usually requires SSMS
Configuration Manager also exposes SQL Server views for reporting. Those views are a different data interface from WQL classes. Microsoft’s documented Query Designer procedure for SQL-based report statements starts by opening SSMS. The console query wizard does not become a general SQL editor merely because its WQL resembles SQL.
If your requirement is a report dataset written against Configuration Manager SQL views, treat SSMS—or another separately supported SQL authoring environment—as a distinct requirement. Do not paste report SQL into Show Query Language, and do not point a WQL query at a SQL view name. Microsoft notes qualitatively that direct SQL-view queries can be much faster than WMI/WQL for some reporting workloads, but no universal benchmark or speed percentage is established here; performance depends on the statement, view, indexes, site and workload.
Troubleshoot the common failure modes
The query returns everything
Check that criteria were actually saved and that each condition is attached to the intended AND or OR group. Also check the collection or object scope. A blank-criteria device query can return all devices in All Systems, which is expected behavior rather than a parser error.
Best Value
An attribute is missing
The selected object type or provider class may not expose that property. Reopen the attribute picker, verify that inventory has collected the value, and distinguish an inventory property from a live client property. If the value must be checked immediately on clients, evaluate CMPivot instead.
The statement fails after direct editing
Return to the visual editor, compare the generated WQL with the edited statement, and remove SQL-only syntax such as table aliases or view names that the SMS Provider does not understand. Validate one condition at a time.
CMPivot shows no data
Verify that the selected collection contains eligible clients, that your role permits CMPivot, and that clients can be reached under the site’s current prerequisites. A lack of current data can reflect client availability or cache age rather than an invalid KQL expression.
PowerShell cannot find the cmdlet
Load the ConfigurationManager module installed with the console and review the local help. Confirm that the session is connected to the correct site drive and that your account has permission to use the provider. Module and site-version differences can change available parameters.
Choose the shortest safe route
- Saved device or user search: Monitoring > Queries > Create Query, then Edit Query Statement.
- Need to inspect or tweak the generated statement: use Show Query Language and edit WQL.
- Need current client state: select a device collection and start CMPivot with its KQL subset.
- Need scripted, repeatable administration: use New-CMQuery or Invoke-CMWmiQuery after checking local cmdlet help.
- Need SQL against reporting views: follow the separate SQL Query Designer workflow; the console wizard is not a SQL-view editor.
The practical answer to “How do I create an SCCM SQL query without opening SQL Management Studio?” is therefore conditional: for a saved administrative query, create WQL in the Configuration Manager console; for live client investigation, use CMPivot; for true report SQL, recognize that you are asking for a different authoring path.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
