CMPIVOT Queries

Here are sample CMPivot Queries that I have come across since it was introduced in 1806

SCCM CMPIVOT QUERY EXAMPLES

SCCM Client Log Collection added to Tech Preview 1912

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.
Log files
Diagnostics.log

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

Command Line Equivalent of wuauclt in Windows 10 / Windows Server 2016

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

Now, if you try typing the following command in an Administrator command prompt on Windows 10 or Windows Server 2016, nothing will happen – wuauclt /detectnow

Now, in order to force check Windows Update, follow the below instructions –

  1. Open a Command Prompt with Administrator privileges.
  2. Type in usoclient StartScan
  3. 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 Strings on 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 –

C:\Windows\SoftwareDistribution\ReportingEvents.log

Task Scheduler -> Microsoft -> Windows -> Update Orchestrator

Manually remove SCCM client

To manually remove the sCCM client all of the following must be removed

1. SMS Agent Host Service

Stop 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

 

6. \windows\smscfg.ini

 

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

Query to find duplicate Mac addresses in SCCM

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.

Issue:

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.

Resolution:

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.

Implementation:

Update 04/06/2015:
Having a browse around turns out this functionality already exists!

  1. Open the SCCM (ConfigMgr) console
  2. Click the Monitoring tab
  3. Click Reports
  4. Search for mac

SCCM_DuplicateMACAddresss_Reports_03062015

Option 2

  1. Open the SCCM (ConfigMgr) console
  2. Click the Monitoring tab
  3. Create new Query wizard
    > Right click Queries
    Create Query
    SCCM_QueryUserLastLoggedOn_CreateQuery_29042015
  4. 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
    SCCM_DuplicateMACAddresss_GeneralQuerySettings_03062015
  5. Query Statement
    > Click Show Query Language
    SCCM_QueryUserLastLoggedOn_ShowQueryLanguage_29042015
    > 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 Ok
    SCCM_DuplicateMACAddresss_SQLQuery_03062015

  6. Summary
    > Click Close
  7. Test your new query
    > Right click new query
    > Click Run
    SCCM_DuplicateMACAddresss_RunQuery_03062015
  8. MAC Address prompt
    > Enter the MAC Address you would like to query
    > Click Ok
    SCCM_DuplicateMACAddresss_MACPrompt_03062015
  9. You will be presented with the results for a particular MAC address
    SCCM_DuplicateMACAddresss_Results_03062015

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  SMS_R_System

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

SCCM: What’s New in Version 1910

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:

Configuration Manager
Intune
Desktop Analytics
Autopilot


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.

Tip

In Windows 10, when you open the Start menu, type the name to find the icon. For example, enter Configuration Manager or Software Center.

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

Desktop Analytics
For more information on the monthly changes to the Desktop Analytics cloud service, see What’s new in Desktop Analytics.

Real-time management
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.

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

Note

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.

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

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

OS deployment
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)
System locale
UI language
UI language fallback
User locale

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.

Software updates
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 management
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.

Protection
BitLocker management
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.
Other updates
.

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.

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

Tip

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.