ConfigMgr CMPivot Tool SCCM Subset of the Azure Log Analytics

Who said SMS (slow-moving software)? SCCM is anymore a¬†slow-moving software. SCCM moves as fast as Azure. Now, SCCM has better speed than Microsoft Intune (my SCCM love takes precedence here). Reporting and live status of managed machines. Most of these fast-moving parts of SCCM are using “Fast Channel” architecture. In this post, we will see more details about the SCCM CMPivot tool.

What is CMPivot SCCM in-console Tool?

CMPivot is a new in-console utility that provides access to real-time state of devices in your environment. It immediately runs a query on all currently connected devices in the target collection and returns the results.

You can then filter and group this data in the tool. By providing real-time data from online clients, you can more quickly answer business questions, troubleshoot issues, and respond to security incidents.

Patch My PC

CMPivot Query Language – WQL/SQL/Azure Log Analytics?

CMPivot uses a subset of the Azure Log Analytics data flow model. Azure Log Analytics queries are Case Sensitive. This query language is optimized to perform and handle free-text data at cloud-scale

In the following example, the entity is Device (a reference to the current state of all devices in the collection), and the operator is where (which filter out records from its input according to some per-record predicate):

Device | where Manufacturer like ‘%Microsoft%’

1E Nomad

Video Tutorial CMPivot Tool

Watch this video on YouTube.

How to Launch CMPivot Tool

You can see more details explanation in the video tutorial.

  1. In the SCCM CB 1805 or later console, go to the Assets and Compliance workspace, and select Device Collections. Select a target collection, and click Start CMPivot in the ribbon to launch the tool.
  2. The interface provides further information about using the tool.
    • You can manually enter query strings at the top, or click the links in the in-line documentation. And sample CMPivot queries are given in the following section of this blog.
    • Click one of the Entities to add it to the query string.
    • The links for Table OperatorsAggregation Functions, and Scalar Functions open language reference documentation in the web browser. CMPivot uses the same query language as Azure Log Analytics.

CMPivot Right Click Options

Following are the CMPivot right click options. I think in the future SCCM console right click tool features will get integrated with CMPivot.  You can click on the device count to drill down to see the specific devices. When displaying devices in CMPivot, you can right-click a device and select any of the following:-

  • Run Script
  • Remote Control
  • Resource Explorer

When right-clicking on a specific device, you can also pivot the view of the specific device to one of the following attributes:

  • Autostart Commands
  • Installed Products
  • Processes
  • Services
  • Users
  • Active Connections
  • Missing Updates

Sample CMPivot Azure Log Analytics Queries

To run this CMPivot Azure Log Analytics Query:-

  • SCCM Client version should be the latest one (1805 or later)
  • SCCM Client machine should be ONLINE
  • Necessary ports should be opened (Fast Channel)
  • The SCCM administrator needs permissions to run scripts. I would recommend reading following documentation for more details Security roles for scripts
SCCM 1805 CMPivot Tool Queries

1. CMPivot Azure Log Analytics Query to find out the registered applications of an SCCM client.  SCCMTP1 is my client machine.

Registry(‘hklm:\\Software\\RegisteredApplications’) | where (Device == ‘SCCMTP1’) 

Corresponding REGISTRY Entry – HKEY_LOCAL_MACHINE\SOFTWARE\RegisteredApplications

2. CMPivot Azure Log Analytics Query to find out the SQL Site System details of a site server. SCCMTP1 is my client machine and SCCM primary server.

Registry(‘hklm:\\Software\\Microsoft\\SMS\\SQL Server\\Site System SQL Account’) | where (Device == ‘SCCMTP1’)

Corresponding REGISTRY Entry –   KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\SQL Server\Site System SQL Account

3. CMPivot Azure Log Analytics Query to find out the SCCM DP details of a site server. SCCMTP1 is my client machine and SCCM primary server.

Registry(‘hklm:\\Software\\Microsoft\\SMS\\DP’) | where (Device == ‘SCCMTP1’)


4. CMPivot Azure Log Analytics Query to find out the SCCM MP details of a site server. SCCMTP1 is my client machine and SCCM primary server.

Registry(‘hklm:\\Software\\Microsoft\\SMS\\MP’) | where (Device == ‘SCCMTP1’)


Monitoring CMPivot Tool Auditing Options

As I showed in the video tutorial, SCCM CB console gives us a rich auditing capabilities for CMPivot tool.

SCCM Console Path –  \Monitoring\Overview\Script Status

CMPivot Tool SCCM 1805

Sample Script used by CMPivot tool

You can see all the scripts used by CMPivot Tool from SCCM CB console.

Path – > \Monitoring\Overview\Script Status – Select one of the client operation ID entry and go to properties.

param( [string]$wmiquery, [string] $select, [int] $unique = 0 )

$wmiquery = $wmiquery.Replace('#s',' ').Replace('#q','''').Replace('#c',',').Replace('##','#')

#deal with one-offs that don't work well over WMI
if( $wmiquery -eq 'Autostart' )
$processes = New-Object System.Collections.Generic.List[System.String]

foreach($runOnce in (gi 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run').GetValueNames())
$processes.Add( (gi 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run').GetValue($runOnce).ToString() )

return $processes | sort-object -Unique
elseif( $wmiquery -eq 'Users' )
$users = New-Object System.Collections.Generic.List[System.String]

foreach( $user in (get-WmiObject -class Win32_LoggedOnuser | Select Antecedent))
$parts = $user.Antecedent.Split("""")
$users.Add( $parts[1] + "\" + $parts[3])

return $users | sort-object -Unique
elseif( $wmiquery -eq 'Connections' )
$netstat = "$Env:Windir\system32\netstat.exe"
$rawoutput = & $netstat -f
$netstatdata = $rawoutput[3..$rawoutput.count] | ConvertFrom-String | select p2,p3,p4,p5 | where p5 -eq 'established' | select P4 
$connections = New-Object System.Collections.Generic.List[System.String]

foreach( $data in $netstatdata)
$connections.Add( $data.P4.Substring(0,$data.P4.LastIndexOf(":")) )

return $connections | sort-object -Unique
elseif( $wmiquery -eq 'Updates' )
$Session = [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$null))
$Searcher = $Session.CreateUpdateSearcher()
#$Searcher.Online = $True
#$Searcher.ServerSelection = 2

# Search for any uninstalled updates
$MissingUpdates = $Searcher.Search("DeploymentAction=* and IsInstalled=0 and Type='Software'")

$Updates = New-Object System.Collections.Generic.List[System.String]

If ($MissingUpdates.Updates.Count -gt 0) 
foreach( $Update in $MissingUpdates.Updates )
foreach( $KB in $Update.KBArticleIDs )
$Updates.Add( "KB$KB" )

return $Updates | sort-object -Unique
if ($wmiquery.StartsWith("File(") )
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")

$fileSpec = [System.Environment]::ExpandEnvironmentVariables( $wmiquery.Substring($first, $last-$first))

$result = New-Object System.Collections.Generic.List[Object]

foreach( $file in (Get-Item -ErrorAction SilentlyContinue -Path $filespec))
$fileHash = ""

$fileHash = (get-filehash -ErrorAction SilentlyContinue -Path $file).Hash 

$hash = @{
FileName = $file.FullName
Mode = $file.Mode
LastWriteTime = $file.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss")
Size = $file.Length
Version = $file.VersionInfo.ProductVersion
Hash = $fileHash



return $result
elseif ($wmiquery.StartsWith("Registry(") )
$first = $wmiquery.IndexOf("'")+1
$last = $wmiquery.LastIndexOf("'")
$regSpec = $wmiquery.Substring($first, $last-$first)

$result = New-Object System.Collections.Generic.List[Object]

foreach( $regKey in (Get-Item -ErrorAction SilentlyContinue -Path $regSpec) )
foreach( $regValue in $regKey.Property )
$hash = @{
Property = $regValue
Value = $regKey.GetValue($regValue).ToString()


return $result

# Execute the query
$results = (gwmi -query $wmiquery | Select-object -Property $select.Split("#") )

#if Unique flag has been passed
if( $unique -ne 0 )
$results = $results | sort-object -Property $select.Split("#") -Unique 

#If only one select then convert result to string array for easy transport
if( $select.Split("#").Count -eq 1 )
$results = $results | select -ExpandProperty $select


return $results

$results = gwmi -query $wmiquery | Select-object -Property $select.Split("#") 

2 thoughts on “ConfigMgr CMPivot Tool SCCM Subset of the Azure Log Analytics”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.