Wish me luck
Here are sample CMPivot Queries that I have come across since it was introduced in 1806
SCCM CMPIVOT QUERY EXAMPLES
Client Log Collection
You can now trigger a client device to upload its client logs to the site server by sending a client notification action from the Configuration Manager console.
Permissions for client log collection
To collect client logs, your administrative user needs:
Notify resource permission on the Collection
The Full Administrator and Operations Administrator built-in roles have this permission by default.
Collect client logs
In the Assets and Compliance workspace, go to either the Devices or Device Collections node.
Right-click on a device, or a device collection.
Select Client Diagnostics, then select Collect Client Logs.
Collect client logs from the console
A client notification message is sent to the selected clients to gather the CCM logs. The logs are returned using software inventory file collection. You can also select Collect Client Logs under Client Diagnostics from either the Device Collections or Devices node using the ribbon.
View client logs
From the Devices node, right-click on the device you want to view logs for.
Select Start, then Resource Explorer.
From Resource Explorer, click on Diagnostic Files.
In the Diagnostic Files list, you can see the collection date for the files. The name format of the client logs is Support_.zip.
Right-click on the zip file and select one of the following options:
Open Support Center: Launches Support Center.
Copy: Copies the row information from Resource Explorer.
View file: Opens the folder where the zip file is located with File Explorer.
Save: Opens a Save File dialog for the selected file.
Export: Saves the Resource Explorer columns shown in Diagnostic Files.
Refresh: Refreshes the file list.
Properties: Returns the properties on the selected file.
Review and save client logs from Resource Explorer
Windows Update with Windows 10 & Windows Server 2016 was modified so that it could be integrated with the new UI. The old
Windows Update is now gone from Control Panel (I am so sad…) and if you have to view updates, all you can do is
Start -> Settings -> Update & Security -> Windows Update
With earlier versions of Windows, there used to be utility wuauclt but that does not work anymore since it does not update the UI which is accessed through
Now, if you try typing the following command in an Administrator command prompt on Windows 10 or Windows Server 2016, nothing will happen –
Now, in order to force check Windows Update, follow the below instructions –
- Open a Command Prompt with Administrator privileges.
- Type in
- You will see that Windows Update in Settings.exe will start refreshing
Note: USOClient.exe is located in C:\Windows\System32
This will also force the client to report its status to the WSUS server (if configured).
On using Sysinternal’s
UsoClient.exe, I found that there are more switches which can be used –
- StartScan Used To Start Scan
- StartDownload Used to Start Download of Patches
- StartInstall Used to Install Downloaded Patches
- RefreshSettings Refresh Settings if any changes were made
- StartInteractiveScan May ask for user input and/or open dialogues to show progress or report errors
- RestartDevice Restart device to finish installation of updates
- ScanInstallWait Combined Scan Download Install
- ResumeUpdate Resume Update Installation On Boot
Note – Attempting to run the Interactive mode if WU is configured to be solely non-Interactive, or on a non-Interactive server, should trigger an error (something along the lines of “AU Can not perform non-interactive scan if AU is interactive-only”).
Recently, I was working with the Windows Update API on Server 2016 and initially this caused a lot of trouble because patches were installed but were not being reported properly in the UI. I found this executable by checking out the following –
Task Scheduler -> Microsoft -> Windows -> Update Orchestrator
To manually remove the sCCM client all of the following must be removed
1. SMS Agent Host Service
from elevated command line enter (not powershell)
SC Delete ccmexec
2. CCMSetup service (if present)
3. \windows\ccm directory
4. \windows\ccmsetup directory
5. \windows\ccmcache directory
7. \windows\sms*.mif (if present)
8. HKLM\software\Microsoft\ccm registry keys
9. HKLM\software\Microsoft\CCMSETUP registry keys
10. HKLM\software\Microsoft\SMS registry keys
found on Configmonkey.co.uk consolidating here for my own records
Using SCCM to query the ConfigMgr database to find clients with duplicate MAC addresses.
We discovered an issue on a client site where multiple distinct clients could have the same MAC address. In this case the end-point would not behave as expected as the deployments the client would receive may not be the ones you expected.
There are many ways to skin this particular cat but this seemed like a viable scenario to demo a how to query the ConfigMgr database to scan for instances where a client had a particular MAC Address.
Having a browse around turns out this functionality already exists!
- Open the SCCM (ConfigMgr) console
- Click the Monitoring tab
- Click Reports
- Search for mac
- Open the SCCM (ConfigMgr) console
- Click the Monitoring tab
- Create new Query wizard
> Right click Queries
> Create Query
- General Query Settings
> Name: All Systems – Find Clients with a given MAC Address
> Comments: Brief description of what the query is for
> Click Edit Query Statement
- Query Statement
> Click Show Query Language
> Paste the following query into the Query Statement
SELECT SMS_R_System.Name, SMS_R_System.MACAddresses FROM SMS_R_System WHERE SMS_R_System.MACAddresses = ##PRM:SMS_R_System.MACAddresses##
> Click Close
- Test your new query
> Right click new query
> Click Run
- MAC Address prompt
> Enter the MAC Address you would like to query
> Click Ok
- You will be presented with the results for a particular MAC address
Query Statement Explained
SELECT SMS_R_System.Name, SMS_R_System.MACAddresses
The SELECT statement is used to decide what information you would like retrieved by the Query in this case the following:
|SMS_R_System.Name||Name of client|
|SMS_R_System.MACAddresses||MAC Address of Client|
You can use the query builder to add/remove fields to your preference.
FROM statement indicates which table the information is stored in, in this case SMS_R_SYSTEM
WHERE SMS_R_System.MACAddresses = ##PRM:SMS_R_System.MACAddresses##
The WHERE statement is the condition by which results are filtered.
##PRM: ## will prompt the user to enter information, the message prompt and data type will match that of the field you have targeted.
In this case System.Resource.MACAddresses and data type text
Update 1910 for Configuration Manager current branch is available as an in-console update. Apply this update on sites that run version 1806 or later. This article summarizes the changes and new features in Configuration Manager, version 1910.
Always review the latest checklist for installing this update. For more information, see Checklist for installing update 1910. After you update a site, also review the Post-update checklist.
To take full advantage of new Configuration Manager features, after you update the site, also update clients to the latest version. While new functionality appears in the Configuration Manager console when you update the site and console, the complete scenario isn’t functional until the client version is also the latest.
Microsoft Endpoint Configuration Manager
Configuration Manager is now part of Microsoft Endpoint Manager.
Microsoft Endpoint Configuration Manager
Microsoft Endpoint Manager is an integrated solution for managing all of your devices. Microsoft brings together Configuration Manager and Intune with simplified licensing. Continue to make use of your existing Configuration Manager investments while you take advantage of the power of the Microsoft cloud at your own pace.
The following Microsoft management solutions are all now part of the Microsoft Endpoint Manager brand:
What things change in Configuration Manager with Microsoft Endpoint Manager?
In version 1910, aside from the name change, Configuration Manager still functions the same. Some of the name changes might impact your use of the following components:
Configuration Manager console: Find shortcuts to the console and the Remote Control Viewer under the Windows Start menu in the Microsoft Endpoint Manager folder.
Software Center: Find the Software Center shortcut under the Windows Start menu in the Microsoft Endpoint Manager folder.
Microsoft Endpoint Manager Start menu icons
Make sure to update any internal documentation that you maintain to include these new locations.
In Windows 10, when you open the Start menu, type the name to find the icon. For example, enter Configuration Manager or Software Center.
Reclaim SEDO lock
Starting in current branch version 1906, you could clear your lock on a task sequence. Now you can clear your lock on any object in the Configuration Manager console.
Extend and migrate on-premises site to Microsoft Azure
This new tool helps you to programmatically create Azure virtual machines (VMs) for Configuration Manager. It can install with default settings site roles like a passive site server, management points, and distribution points. After you validate the new roles, use them as additional site systems for high availability. You can also remove the on-premises site system role and only keep the Azure VM role.
For more information on the monthly changes to the Desktop Analytics cloud service, see What’s new in Desktop Analytics.
Optimizations to the CMPivot engine
We’ve added some significant optimizations to the CMPivot engine. Now you can push more of the processing to the ConfigMgr client. The optimizations drastically reduce the network and server CPU load needed to run CMPivot queries. With these optimizations, you can now sift through gigabytes of client data in real time.
Additional CMPivot entities and enhancements
We’ve added a number of new CMPivot entities and entity enhancements to aid in troubleshooting and hunting. We’ve included the following entities to query:
Windows event logs (WinEvent)
File content (FileContent)
DLLs loaded by processes (ProcessModule)
Azure Active Directory information (AADStatus)
Endpoint protection status (EPStatus)
This release also includes several other enhancements to CMPivot. For more information, see CMPivot starting in version 1910.
Microsoft Connected Cache support for Intune Win32 apps
When you enable Microsoft Connected Cache on your Configuration Manager distribution points, they can now serve Microsoft Intune Win32 apps to co-managed clients.
Configuration Manager current branch version 1906 included Delivery Optimization In-Network Cache (DOINC), an application installed on Windows Server that’s still in development. Starting in current branch version 1910, this feature is now called Microsoft Connected Cache.
When you install Connected Cache on a Configuration Manager distribution point, it offloads Delivery Optimization service traffic to local sources. Connected Cache does this behavior by efficiently caching content at the byte-range level.
Include custom configuration baselines as part of compliance policy assessment
You can now add evaluation of custom configuration baselines as a compliance policy assessment rule. When you create or edit a configuration baseline, you can now use the Evaluate this baseline as part of compliance policy assessment option. When you add or edit a compliance policy rule, you have a condition called Include configured baselines in compliance policy assessment.
For co-managed devices, and when you configure Intune to take Configuration Manager compliance assessment results as part of the overall compliance status, this information is sent to Azure Active Directory. You can then use it for conditional access to your Office 365 resources.
Enable user policy for Windows 10 Enterprise multi-session
Configuration Manager current branch version 1906 introduced support for Windows Virtual Desktop. This Microsoft Azure environment supports several OS versions, some of which allow multiple concurrent active user sessions. For example, Windows 10 Enterprise multi-session is one of these OS versions.
If you require user policy on these multi-session devices and accept any potential performance impact, you can now configure a client setting to enable user policy. In the Client Policy group, configure the Enable user policy for multiple user sessions setting.
Deploy Microsoft Edge, version 77 and later
The all-new Microsoft Edge is ready for business. You can now deploy Microsoft Edge, version 77 and later, to your users. Admins can pick the Beta or Dev channel, along with a version of the Microsoft Edge client to deploy.
For more information, see Deploy Microsoft Edge, version 77 and later.
Improvements to application groups
Starting in current branch version 1906, you can create a group of applications to send to a device collection as a single deployment. This release improves upon this feature:
Users can select Uninstall for the app group in Software Center.
You can deploy an app group to a user collection.
For more general information, see Create application groups.
Improvements to the task sequence editor
The task sequence editor includes the following improvements:
Search the task sequence editor: If you have a large task sequence with many groups and steps, it can be difficult to find specific steps. You can now search in the task sequence editor. This action lets you more quickly locate steps in the task sequence.
Copy and paste task sequence conditions: If you want to reuse the conditions from one task sequence step to another, you can now copy and paste conditions in the task sequence editor.
For more information, see the new article on how to use the task sequence editor.
Task sequence performance improvements: Power plans
You can now run a task sequence with the high-performance power plan. This option improves the overall speed of the task sequence. It configures Windows to use its built-in high-performance power plan, which delivers maximum performance at the expense of higher power consumption.
Task sequence download on demand over the internet
You can use the task sequence to deploy a Windows 10 in-place upgrade via the cloud management gateway (CMG). However, it requires the deployment to download all content locally before starting the task sequence.
Starting in this release, the task sequence engine can download packages on-demand from a content-enabled CMG or a cloud distribution point. This change provides additional flexibility with your Windows 10 in-place upgrade deployments to internet-based devices.
Improvements to OS deployment
This release includes the following improvements to OS deployment.
Boot image keyboard layout
Configure the default keyboard layout for a boot image. On the Customization tab of a boot image, use the new Set default keyboard layout in WinPE option. If you select a language other than en-us, Configuration Manager still includes en-us in the available input locales. On the device, the initial keyboard layout is the selected locale, but the user can switch the device to en-us if needed.
Import a single index of an OS upgrade package
When you import an OS upgrade package, you can use the Extract a specific image index from install.wim file of selected upgrade package option. This behavior is similar as with OS images, except that it overwrites the existing install.wim in the OS upgrade package. It extracts the image index to a temporary location and then moves it into the original source directory.
Output the results of a Run Command Line step to a variable during a task sequence
The Run Command Line step now includes an Output to task sequence variable option. When you enable this option, the task sequence saves the output from the command to a custom task sequence variable that you specify.
Improvements to task sequence debugger
This release includes the following improvements to the task sequence debugger:
Use the new task sequence variable TSDebugOnError to automatically start the debugger when the task sequence returns an error.
If you create a breakpoint in the debugger and then the task sequence restarts the computer, the debugger keeps the breakpoints after restart.
For more information, see Task sequence debugger and Task sequence variables – TSDebugOnError.
Improved language support in task sequence
This release adds control over language configuration during OS deployment. If you’re already applying these language settings, this change can help you simplify your OS deployment task sequence. Instead of using multiple steps per language or separate scripts, use one instance per language of the built-in Apply Windows Settings step with a condition for that language.
Use the Apply Windows Settings task sequence step to configure the following new settings:
Input locale (default keyboard layout)
UI language fallback
New variable for Windows 10 in-place upgrade
To address timing issues with the Window 10 in-place upgrade task sequence on high-performance devices when Windows setup is complete, you can now set a new task sequence variable, SetupCompletePause. When you assign a value in seconds to this variable, the Windows setup process delays that amount of time before it starts the task sequence. This timeout provides the Configuration Manager client additional time to initialize.
Additional options for third-party update catalogs
You now have more granular controls over synchronization of third-party updates catalogs. Starting in Configuration Manager version 1910, you can configure the synchronization schedule for each catalog independently. When you use catalogs that include categorized updates, you can configure synchronization to include only specific categories of updates to avoid synchronizing the entire catalog. With categorized catalogs, when you’re confident you’ll deploy a category, you can configure it to automatically download and publish to Windows Server Update Services (WSUS).
Use Delivery Optimization for all Windows updates
Previously, you could use Delivery Optimization only for express updates. With Configuration Manager version 1910, it’s now possible to use Delivery Optimization for the distribution of all Windows Update content for clients running Windows 10 version 1709 or later.
Optimize Windows 10 update delivery
Client settings for software updates
Client settings for Delivery Optimization
Additional software update filter for ADRs
You can now use Deployed as an update filter for your automatic deployment rules (ADRs). This filter helps identify new updates that might need to be deployed to your pilot or test collections.
Office 365 ProPlus Pilot and Health Dashboard
The Office 365 ProPlus Pilot and Health Dashboard helps you plan, pilot, and deploy Office 365 ProPlus. The dashboard provides health insights for devices with Office 365 ProPlus to help identify possible issues that might affect your deployment plans. The Office 365 ProPlus Pilot and Health Dashboard provides a recommendation for pilot devices based on add-in inventory.
Configuration Manager now provides the following management capabilities for BitLocker Drive Encryption:
Deploy the BitLocker client to managed Windows devices.
Manage device encryption policies.
Generate compliance reports.
Use an administration and monitoring website for key recovery.
Access a user self-service portal.
Configuration Manager console
View active consoles and message administrators through Console Connections
We’ve made the following improvements to Console Connections:
The ability to message other Configuration Manager administrators through Microsoft Teams.
The Last Console Heartbeat column has replaced the Last Connected Time column.
An open console in the foreground sends a heartbeat every 10 minutes to help determine which console connections are currently active.
For more information, see View recently connected consoles and Message administrators.
Client diagnostics actions
There are new device actions for Client Diagnostics in the Configuration Manager console:
Enable verbose logging: Change the global log level for the CCM component to verbose, and enable debug logging.
Disable verbose logging: Change the global log level to default, and disable debug logging.
For more information, see Client diagnostics.
Improvements to console search
This release includes the following improvements to search in the Configuration Manager console:
You can now use the All Subfolders search option from the Driver Packages and Queries nodes.
When a search returns more than 1,000 results, select OK on the notice bar to view more results.
Aside from new features, this release also includes additional changes such as bug fixes. For more information, see Summary of changes in Configuration Manager current branch, version 1910.
As of December 20, 2019, version 1910 is globally available for all customers to install.
When you’re ready to install this version, see Installing updates for Configuration Manager and Checklist for installing update 1910.
To install a new site, use a baseline version of Configuration Manager.
Learn more about:
After you update a site, also review the Post-update checklist.
Computers that are running on virtual (Hyper-V or VMWare) will have an entry in win32_computersystem with attribute model in WMI.
You can create a collection to using table called SMS_G_System_COMPUTER_SYSTEM.
SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System
inner join SMS_G_System_COMPUTER_SYSTEM on
SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Model like “%Virtual%”
To list all physical computers ,create another collection using operator not in from above collection like below:
select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model like “%Virtual%”)
Here is a chart showing the patching cadence of the different rings.
|Deployment ring||Servicing channel||Deferral for feature updates||Deferral for quality updates||Example|
|Preview||Windows Insider Program||None||None||A few machines to evaluate early builds prior to their arrival to the semi-annual channel|
|Targeted||Semi-annual channel (Targeted)||None||None||Select devices across various teams used to evaluate the major release prior to broad deployment|
|Broad||Semi-annual channel||120 days||7-14 days||Broadly deployed to most of the organization and monitored for feedback
Pause updates if there are critical issues
|Critical||Semi-annual channel||180 days||30 days||Devices that are critical and will only receive updates once they’ve been vetted for a period of time by the majority of the organization|
On the Management Server saw this error under the Operations Manager Log.
Source: Console Operations
Event ID: 33569
Cannot connect to SQL Reporting Services Server. Message= An unexpected error occured while connecting to SQL Reporting Services server: System.Net.WebException: The request failed with HTTP status 401: Unauthorized.at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object parameters)at Microsoft.EnterpriseManagement.Reporting.ReportingService.ReportingService2005.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, SearchCondition Conditions at Microsoft.EnterpriseManagement.Reporting.EnterpriseReporting.FindItems(String searchPath, IList`1 criteria, Boolean And)at Microsoft.EnterpriseManagement.Reporting.EnterpriseReporting.FindItems(String itemPath)at Microsoft.EnterpriseManagement.Reporting.EnterpriseReporting.FindItem(String itemPath, ItemTypeEnum desiredTypes) at Microsoft.EnterpriseManagement.Reporting.EnterpriseReporting.GetFolder(String path) at Microsoft.EnterpriseManagement.Reporting.EnterpriseReportingGroup.Initialize()at Microsoft.EnterpriseManagement.Reporting.ServiceManagerReportingGroup..ctor(DataWarehouseManagementGroup managementGroup, String reportingServerURL, String reportsFolderPath, NetworkCredential credentials) at Microsoft.EnterpriseManagement.Reporting.ServiceManagerReportingGroup..ctor(DataWarehouseManagementGroup managementGroup, String reportingServerURL, String reportsFolderPath) at Microsoft.EnterpriseManagement.UI.SdkDataAccess.ManagementGroupServerSession.TryConnectToReportingManagementGroup() Remediation = Please contact your Administrator.
If you are getting this error is because you haven’t registered the SPN’s and delegated the appropiate permissions to Service Manager and Reporting Services.
Open Command Line as administrator on the Management Server
Run the following commands:
This command will set the appropriate SPNs that are needed for SCSM and SSRS.
Setspn -A HTTP/SCSMDWSERVER.domain.com DOMAIN\SCSMServiceaccount
To list the SPNs
Setspn –L DOMAIN\SCSMServiceaccount