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 →The easiest supported way to create a saved SCCM (Configuration Manager) query without opening SQL Server Management Studio is the console query wizard: open Monitoring > Queries > Create Query. You build the statement with attribute and criteria controls, or select Show Query Language to edit the underlying WQL directly.
That distinction matters. A console query is WQL (Windows Management Instrumentation Query Language), not unrestricted SQL against the Configuration Manager database. CMPivot uses a subset of Kusto Query Language (KQL) for interactive client data, while SQL report authoring against Configuration Manager SQL views remains a separate workflow traditionally documented in SSMS.
As an Amazon Associate I earn from qualifying purchases.
Choose the right query tool first
“SQL query” is commonly used as shorthand for any Configuration Manager data lookup, but the three built-in routes behave differently. Selecting the right one prevents a query that looks correct yet cannot run in the intended place.
Free tools Windows power users keep installed
One-click scans. No signup required.
| Route | Best fit | Language | What it returns or changes |
|---|---|---|---|
| Configuration Manager console query wizard | Saved administrative queries, collection criteria, joins and reusable results | WQL | Runs through the SMS Provider and can be saved in the console |
| CMPivot | On-demand inspection of a target device collection, especially current client state | Subset of KQL | Interactive results from clients, plus some cached hardware-inventory data; it is not a saved WQL query |
| ConfigurationManager PowerShell | Repeatable or scripted creation and execution | WQL through the SMS Provider | Creates saved queries or runs WQL while honoring Configuration Manager role-based access |
| SQL Server Query Designer | Report statements written against Configuration Manager SQL views | SQL | Uses the reporting database model; Microsoft’s documented designer procedure starts in SSMS |
For most administrators who need a reusable device or user query, use the first row. If you need a live answer from clients right now, use CMPivot. If you need a report query written in SQL, avoiding SSMS is not the same as replacing SQL with WQL: the languages and data paths are different.
#1 Best Overall
Create a saved SCCM query in the console
1. Open the query wizard
- Start the Configuration Manager console and select Monitoring.
- Expand Queries.
- Select Create Query in the ribbon or context menu.
- Enter a descriptive query name. Include the platform, purpose or version in the name so another administrator can recognize its scope.
The exact labels can vary slightly by current-branch release and by administrative role, but the Monitoring workspace and Queries node are the normal entry point for saved console queries.
2. Select the object type
Choose the resource type that the query should return, such as systems, users or user groups. The object type determines which attributes are available in the statement editor. Starting with the correct type is important: a device attribute cannot be selected as though it were a user attribute.
3. Build the statement with the visual editor
- In the query properties window, choose Edit Query Statement.
- Use the General or result-attributes area to add the columns you want returned.
- Open the Criteria tab to add one or more filters.
- For each criterion, select an attribute, an operator such as is equal to or like, and a value.
- Use the joins or related-attribute controls when the required value belongs to another WMI class exposed by the provider.
The wizard is useful because it supplies provider-aware attributes and operators instead of requiring you to remember every class and property name. It also makes the resulting query easier for another console administrator to maintain.
4. Edit WQL directly when the wizard is limiting
Select Show Query Language in the statement editor to view and edit the generated WQL. This is the closest console equivalent to typing a query by hand, but it is still WQL sent through the SMS Provider. It is not a free-form editor for SQL Server tables or views.
A typical WQL statement uses a WMI class and a filter, for example:
Rank #2
SELECT SMS_R_System.Name, SMS_R_System.ResourceId
FROM SMS_R_System
WHERE SMS_R_System.Name LIKE "PC-%"Use the class and property names offered by the editor for your site’s data model. Inventory extensions, discovery settings and Configuration Manager versions can affect which properties are populated. If a property is empty for every resource, check that the relevant discovery or inventory data exists before changing the syntax.
5. Save, scope and validate
- Save the query after reviewing its statement and criteria.
- When using it to populate or evaluate a collection, set an intentional limiting collection rather than assuming the broadest scope is safe.
- Run the query and inspect the result count and returned columns.
- For collection-membership work, use the query result preview where available before applying membership changes.
- Document the purpose, expected population and refresh assumptions in the query’s description or your team’s change record.
A query with no criteria can return every device in the All Systems collection. Microsoft explicitly warns that an unfiltered query has this behavior. An accidental all-device result can create a collection much larger than intended, so add a test criterion or limit the collection while developing.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Use CMPivot for current client information
CMPivot is the better fit when the question is “What is true on these clients now?” rather than “Which resources match a reusable provider query?” It targets a selected device collection and uses a KQL subset with an entity browser, property discovery and IntelliSense.
CMPivot workflow
- In the console, open the device collection that should be queried.
- Start CMPivot from the collection’s actions.
- Browse the available entities and properties in the CMPivot interface instead of guessing names.
- Compose the KQL expression with IntelliSense, then run it against the collection.
- Review which rows are live client responses and which values come from cached hardware-inventory data.
- Export results when you need to analyze or share the output, and close the session when finished.
CMPivot requires suitable permissions and an eligible site and collection context. Its output can differ from a saved query because the client may be offline, the data may be cached, or a policy may not yet have reached the device. Treat a missing response as a state to investigate, not automatically as proof that the device lacks the property.
CMPivot is not a replacement for the query wizard. It does not create the same reusable WQL query object, and KQL expressions cannot simply be pasted into the WQL editor.
Automate WQL with ConfigurationManager PowerShell
PowerShell is useful when a query must be created consistently across environments, checked into an administrative script, or run on a schedule. The ConfigurationManager module sends these operations through the SMS Provider rather than bypassing Configuration Manager’s security model.
Create a saved query
Run the command from a Configuration Manager PowerShell session connected to the appropriate site:
New-CMQuery -Name "Windows workstation names" -Expression 'SELECT SMS_R_System.Name, SMS_R_System.ResourceId FROM SMS_R_System WHERE SMS_R_System.Name LIKE "WS-%"'Use a unique name and retain the expression in source control or your change documentation. Before running automation against production, test the WQL in the console’s Show Query Language window so syntax and expected columns are visible to an administrator.
Run WQL without saving it
For a one-off or scripted lookup, use the query invocation cmdlet:
Invoke-CMWmiQuery -Query 'SELECT SMS_R_System.Name, SMS_R_System.ResourceId FROM SMS_R_System'The cmdlet operates in the Configuration Manager PowerShell context. If it cannot find the site or provider, connect to the correct site drive and verify that the account has permission to read the requested resources. PowerShell does not grant access that the console account does not already have.
Rank #4
What you cannot do without SSMS
Configuration Manager also exposes SQL Server views for reporting. Those views are a different data interface from WMI classes and the SMS Provider. Microsoft’s documented Query Designer workflow for SQL-based report statements begins by opening SQL Server Management Studio.
Therefore, the console wizard does not provide a hidden general-purpose SQL editor. WQL, CMPivot’s KQL subset and report SQL have different syntax, joins, permissions and freshness characteristics. Do not point a SQL statement at a WMI class, and do not paste WQL into a report dataset that expects SQL Server view names.
Microsoft notes qualitatively that querying SQL views directly can be much faster than querying through WMI/WQL, but no universal performance number applies. Choose the interface for the job, not simply for the language label. If a report specifically requires SQL views and your organization forbids SSMS, the documented material does not establish another supported authoring environment; ask your reporting administrator about an approved workflow rather than treating WQL as an equivalent.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Common failures and fixes
The query returns every device
Check the Criteria tab and the limiting collection. An omitted criterion is not interpreted as “none”; it means no filter. Add a narrow test condition, preview the result, then broaden it deliberately.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11The property is missing or empty
Confirm that you selected the correct resource type and WMI class. Check whether discovery, hardware inventory or another client data source actually collects the property. A syntactically valid query cannot return data that the site has never received.
The query runs but results are stale
Saved WQL queries read provider data, which may reflect the last discovery or inventory cycle. For current client state, run CMPivot and account for offline devices and cached values.
CMPivot shows no results
Verify collection membership, CMPivot permissions, client health and connectivity. Review the entity and property names offered by IntelliSense; KQL spelling and entity names are not interchangeable with WQL class names.
PowerShell reports a provider or authorization error
Start the ConfigurationManager module in the intended site context, confirm the provider connection and use an account with the required role-based permissions. A command copied from another site can fail if its site code, provider location or available property differs.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →A SQL statement fails in the console
That is expected when the statement references SQL views or SQL Server syntax. Rewrite the requirement as WQL using the console query wizard, or move the report work to the approved SQL reporting workflow.
A practical decision checklist
- Need a reusable query object for collections or administration? Use Monitoring > Queries > Create Query.
- Need to refine generated syntax or add a join? Use Show Query Language and edit WQL.
- Need live or near-current client state from a selected collection? Use CMPivot and its KQL IntelliSense.
- Need repeatable deployment or scheduled execution? Use New-CMQuery or Invoke-CMWmiQuery in the ConfigurationManager module.
- Need a report dataset against SQL views? Treat it as SQL reporting work; the documented designer path starts in SSMS.
- Before production use, constrain scope, preview results and record the expected population.
Bottom line
For the ordinary SCCM query, you do not need SQL Server Management Studio: create it in the Configuration Manager console, use the visual criteria builder, and switch to Show Query Language when you need direct WQL control. Use CMPivot for interactive client checks and ConfigurationManager PowerShell for automation. Reserve SQL tooling for report statements that genuinely target Configuration Manager’s SQL views.
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.
