CMPivot and Run Scripts: real-time queries and remediation in Configuration Manager

Hardware and software inventory tell you what a device looked like at its last cycle. When you need to know what it looks like now, and fix it now, Configuration Manager (ConfigMgr to many) has two tools: CMPivot for real-time queries against online clients, and Run Scripts for approved PowerShell on a device or collection. In this post I'll set up the permissions, run queries that answer everyday questions, and take a fix through approval to a verified result.

Prerequisites#

• Online clients. Both features reach clients through the client notification channel, so only devices that are online when you run them answer.
• PowerShell 3.0 or later on clients for Run Scripts, or whatever later version your script needs.
• Security software exclusions. Both features run scripts from %WinDir%\CCM\ScriptStore. Some anti-malware blocks that folder or raises alerts, so exclude it.
• Permissions. There are no built-in script author, approver or runner roles. Copy an existing role under Administration › Security › Security Roles and keep only the permissions you need.

The three script roles also need Read on Site. Keep authors and approvers as different people: by default nobody can approve a script they wrote.

Step-by-step#

1. Ask a question with CMPivot#

Go to Assets and Compliance › Device Collections, select a collection and choose Start CMPivot on the ribbon. CMPivot uses a subset of the Kusto Query Language: an entity, then operators separated by pipes. A few queries worth saving as favourites:

// Devices that haven't restarted for 7+ days, by boot day
OperatingSystem
| where LastBootUpTime <= ago(7d)
| summarize count() by bin(LastBootUpTime, 1d)

// Version spread of an app
InstalledSoftware
| where ProductName like '%Chrome%'
| summarize count() by ProductVersion

// Is the print spooler running everywhere?
Service
| where Name == 'Spooler'
| summarize count() by State

// Configuration Manager client version per device
Registry('hklm:\SOFTWARE\Microsoft\SMS\Mobile Client')
| where Property == 'ProductVersion'

Other useful entities include LogicalDisk, Administrators, BitLocker, EPStatus (Defender Antivirus status), AadStatus, File() and CcmLog(). Use project, count, take or top to keep results small. Right-click a device in the results to act on it, including Run Script.

2. Create the fix as a script#

Suppose CMPivot shows the print spooler stopped on devices that need to print. In Software Library › Scripts, choose Create Script, give it a clear name, keep PowerShell as the language and paste the script:

param(
    [Parameter(Mandatory = $true)]
    [string]$ServiceName
)
$svc = Get-Service -Name $ServiceName -ErrorAction SilentlyContinue
if (-not $svc) { Write-Output "Service $ServiceName not found"; exit 1 }
if ($svc.Status -ne 'Running') {
    Start-Service -Name $ServiceName
    $svc.Refresh()
}
[pscustomobject]@{ Service = $svc.Name; Status = $svc.Status.ToString() }

Under Script Parameters you can add validation, such as a maximum length or a regular expression. Scripts take up to 10 parameters of type string, integer or list, and parameter values can't contain single quotes. The new script shows Waiting for approval.

3. Approve it#

An approver selects the script and chooses Approve/Deny on the ribbon, ideally with a comment. In a lab you can let authors approve their own scripts by clearing Script authors require additional script approver on the General tab of Hierarchy Settings (select the site under Administration › Site Configuration › Sites, then Hierarchy Settings on the ribbon). Don't do that in production.

4. Run it#

Select the collection or device in Assets and Compliance › Device Collections and choose Run Script. Only approved scripts are listed. Enter the parameter (Spooler) and finish the wizard. The script runs as the local System account, so access to network shares is limited. The run times out after one hour, and devices that were offline in that window need a new run. From version 2309 you can also schedule a run in UTC.

Verify#

• Monitoring › Script Status lists each run. Open it to see per-device results: an exit code of 0 generally means success. When the output can be converted to JSON, you can switch between JSON Output and Raw Output.
• Re-run the CMPivot query. Good looks like every answering device reporting Running.
• If a device doesn't report back, follow the path: Scripts.log and CcmMessaging.log in %WinDir%\CCM\Logs on the client, MP_RelayMsgMgr.log in SMS_CCM\Logs on the management point, and SMS_Message_Processing_Engine.log on the site server.

Tips & gotchas#

• Keep output short. Output is truncated to 4 KB. Return a status object rather than whole logs, and convert enums to strings, as the script above does.
• Never script a restart of the device or the Configuration Manager client with Run Scripts: Microsoft warns it can cause a continuous restart loop.
• Edits reset approval. Editing or copying a script clears its approval state, which is intentional.
• Site boundaries. CMPivot only returns data from clients of the site you're connected to, unless you run it from the central administration site. Only one CMPivot instance can run per console computer.
• Administration service. CMPivot runs through the administration service, which needs enhanced HTTP or HTTPS (see my enhanced HTTP deep dive). If it falls back to the SMS Provider after a 503 error (visible in CMPivot.log), you also need Read on SMS Scripts.
• Automate carefully. The ConfigurationManager module includes New-CMScript, Approve-CMScript, Get-CMScript and Invoke-CMScript.
• Cloud-only devices need the Intune equivalent: Remediations, and your Kusto skills carry over to advanced hunting.

References#

• CMPivot for real-time data in Configuration Manager
• CMPivot sample scripts
• Create and run PowerShell scripts from the Configuration Manager console