Tag Archives: SCCM CB

CMPivot Operators

== Equals
!= Not equals
like LHS contains a match for RHS
!like LHS doesn’t contain a match for RHS
contains RHS occurs as a subsequence of LHS
!contains RHS doesn’t occur in LHS
startswith RHS is an initial subsequence of LHS
!startswith RHS isn’t an initial subsequence of LHS
endswith RHS is a closing subsequence of LHS
!endswith RHS isn’t a closing subsequence of LHS

Reference: https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot

CMPivot Examples

To accesss CMPivot, highlight a Collection containing the machines you wish to query against and then click Start CMPivot from the ribbon or right-click context menu.

The CMPivot query below returns all VMware devices in the collection used to run CMPivot against:

device | where ManuFacturer == ‘VMware, Inc.’ and (Device like ‘%CM%’ and Model like ‘%VM%’)


  • where and like are case sensitve
  • Device, manuFacturer, ‘VMware, Inc.’,, ‘%CM%’, ‘%svR%’ and ‘%VM%’ are not case sensitive.
  • Need to use a double == for equals.
  • Use % as a wildcard.
  • Text in single snicketts.
  • Use of () is for nesting but not required if nesting not used.

Count the number if devices per manufacturer.
Device | summarize dcount (Device) by Manufacturer

Below shown to indicate presentation – does not need to be all one line.
| where (Manufacturer == ‘VMware, Inc.’)
| where (Device == ‘CM01’)

  • AD Security Groups that are members of local admins groups.

Administrators | where (PrincipalSource == ‘ActiveDirectory’ and ObjectClass == ‘User’)

  • Collection members where the SMS Executive service is running.

Service | where Name == ‘SMS_EXECUTIVE’ | where (State == ‘Running’)

  • Count the different OS version of the devices in the colllection and then display (render) as a bar chart.

OperatingSystem | summarize dcount (Device) by Caption | render barchart

  • Disk space with limited result set using the project operator to define the displayed columns.

| where (Description == ‘Local Fixed Disk’)
| project DeviceID, FreeSpace
| order by FreeSpace desc
| where (DeviceID == ‘C:’)

  • Installed software on all devices.  In the resultset note you can click on the underlined number to drill down.

InstalledSoftware | summarize dcount(Device) by ProductName

  • More specific – specifc application on a device.

| where (ProductName == ‘Microsoft Monitoring Agent’)
| where (Device == ‘OM01’)

  • Count all installed software that does not have Microsoft in the product name using !like as the not like operator.

| where ProductName !like ‘%Microsoft%’
| summarize dcount(Device) by ProductName


Using the SCCM CB Service Connection Point in Offline Mode

Here is a basic description and some notes made during the upgrade of SCCM CB from version 1511 to 1602 using the Service Connection Point (SCP) site system role in Offline Mode.


The SCP role has been installed and configured in Offline Mode.

Before Starting

  • The procedures below use the serviceconnectiontool.exe tool.
  • This tool can be found in a folder on the SCCM Site Server here: ..\Microsoft Configuration Manager\cd.latest\SMSSETUP\TOOLS\ServiceConnectionTool.
  • All commands documented should be run from an admin command prompt at the folder where the serviceconnectiontool.exe resides, in this case C:\Temp\ServiceConnectionTool.
  • If the SCP is installed on a remote server, all the contents of the ..\Microsoft Configuration Manager\cd.latest\SMSSETUP\TOOLS\ServiceConnectionTool folder need to be copied to a folder on the SCP server, e.g. C:\Temp\ServiceConnectionTool.
  • In this case the SCP is on a remote server, with Internet access.  Once the contents of the ServiceConnectionTool folder are copied to the remote server, two subfolders called Data and Packages were created.

The Data folder is for the output files that are to be uploaded.

The Packages folder is for the data downloaded – this folder must be empty for the download to even start.

Main Procdure

Microsoft describe this as a three-step process.

Prepare – run this command to prepare the usage data
  • serviceconnectiontool.exe -prepare -usagedatadest C:\Temp\ServiceConnectionTool\Data\DataUsageData.cab.
Connect – run this command to upload the usage data and download the updates
  • serviceconnectiontool.exe -connect -usagedatasrc C:\Temp\ServiceConnectionTool\Data\DataUsageData.cab -updatepackdest C:\Temp\ServiceConnectionTool\Packages.
Import – run this command to import the updates into SCCM

serviceconnectiontool.exe -import -updatepacksrc C:\Temp\ServiceConnectionTool\Packages.

Installing the Updates
  • Once the updates have been imported they can be seen in the SCCM admin console by navigating to \Administration\Cloud Services\Updates and Servicing.
  • The menu options offer the choices to run the Prerequisite check and Install Update Pack.
  • The Show Status link navigates to \Monitoring\Overview\Site Servicing Status where the Show Status menu option provides progress information.


  • The output of the DataUsageData.cab output file can be viewed by running this command:

serviceconnectiontool.exe -export -dest C:\Temp\ServiceConnectionTool\Data\UsageDataExport.csv.

  • Once the update has been successfully installed a prompt to upgrade the admin console appears if the Show Status option is selected in the \Monitoring\Overview\Site Servicing Status pane.
  • Clients can be upgraded using Client Push or the automatically if the Upgrade all clients in the hierarch using production client check box is selected under \Administration\Overview\Site Configuration\Sites and selecting the Hierarchy Settings menu option, then the Client Upgrade tab.



As always thanks to Niall Brady for this much more detailed description and screenshots https://www.niallbrady.com/2016/01/08/how-can-i-use-updates-and-servicing-in-offline-mode-in-system-center-configuration-manager-current-branch.