ConfigMgr CMPivot Tool SCCM Subset of the Azure Log Analytics

Let us learn about the 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 use “Fast Channel” architecture. This post will see more details about the SCCM CMPivot tool.

What is the CMPivot SCCM in-console Tool?

CMPivot is a new in-console utility that provides access to the 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 filters out records from its input according to some per-record predicate):

Device | where Manufacturers like ‘%Microsoft%.’

Video Tutorial CMPivot Tool

ConfigMgr CMPivot Tool SCCM Subset of the Azure Log Analytics 1

How to Launch CMPivot Tool

You can see more details explanations in the video tutorial.

  1. Go to the Assets and Compliance workspace in the SCCM CB 1805 or later console 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 Operators, Aggregation Functions, and Scalar Functions are available in 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. 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 particular 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 permission to run scripts. I recommend reading the following documentation for more details on hands’ security roles.
SCCM 1805 CMPivot Tool Queries
ConfigMgr CMPivot Tool SCCM Subset of the Azure Log Analytics 2

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 a site server’s SQL Site System details. 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’)

Corresponding REGISTRY Entry – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\DP

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’)

Corresponding REGISTRY Entry – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\MP

Monitoring CMPivot Tool Auditing Options

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

SCCM Console Path – \Monitoring\Overview\Script Status

CMPivot Tool SCCM 1805
ConfigMgr CMPivot Tool SCCM Subset of the Azure Log Analytics 3

Sample Script used by CMPivot tool

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

Path –> \Monitoring\Overview\Script Status– Select one of the client operation ID entries 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 = ""

Try
{
$fileHash = (get-filehash -ErrorAction SilentlyContinue -Path $file).Hash 
}
Catch
{
}

$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
}

$result.Add($hash)

}

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()
}

$result.Add($hash)
}
}

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

else
{
$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.