CMPivot, Run Script, & Client Notification (Again!)

What is Client Notification?

Client Notification in SCCM is one of the most powerful features of SCCM that most people never know they use on a regular basis. In SCCM 2012 SP1, Client Notification was introduced to help get rid of the Slow Moving Software name and help get SCCM into the business of real-time actions. Client Notification consists of a Notification Manager, a Notification Server, and a Notification Agent which is part of CCMEXEC on the client.

The Notification Agent on the client attempts to initiate communication with the Notification Server (your Management Point aka MP) via TCP on port 10123 and it keeps this “conversation” alive checking in every 15 minutes. If TCP on port 10123 isn’t available, the client fails over to using HTTP/HTTPS on port 80/443 and this communication occurs every 5 minutes.

DNS Resolution from the MP to the Client is NOT required, because the client is initiating the communication with the MP. As long as the client can resolve the MP, the communication will be successful. This is especially helpful when you have laptops that bounce between a wired connection and wireless connection, where the MP might see several different DNS entries for a client. And since the client initiates the connection, Windows Firewall allows the traffic by default unless you specifically block outbound traffic.

Check out this article from the Configuration Manager Archives for a great deep dive into Client Notification.
https://techcommunity.microsoft.com/t5/Configuration-Manager-Archive/Fast-Channel-for-System-Management-Client-Notification-in-System/ba-p/273157

Examples of Client Notification, the “Fast Channel” you use every day

Client Notification actions initiated by a technician will attempt to run for 60 minutes. Actions initiated from the technician’s console are ran between the MP and the Client, and these are ran on up to 42 devices at once in parallel.

Go to Assets and Compliance > Devices and you’ll see several columns (outlined in green in the below screenshot) that are populated by Client Notification. This isn’t an exclusive list of columns populated by Client Notification, and every release of SCCM CB seems to add to these columns.
Important Note: All of these columns should hold data accurate to within the last 15 minutes or less!

Go to Assets and Compliance > Devices. Right click on any Device. All the green boxes in the below screenshot indicate an action that can be performed against a Device using the Client Notification “Fast Channel” in SCCM.

Go to Assets and Compliance > Device Collections. Right click on any Device Collection. All the green boxes in the below screenshot indicate an action that can be performed against a Device Collection using the Client Notification “Fast Channel” in SCCM.

Go to Monitoring > Overview > Client Operations to see the status of any Client Notification actions in your environment. Right click on an active operation and you can “Cancel” it to stop it from running… although this is pretty useless as Client Notification actions run almost instantly.

Let’s get to CMPivot already!!!

CMPivot is a real-time way to query information from your online systems, and as of SCCM 1810 you can query Hardware Inventory data for clients that are offline. CMPivot uses Client Notification, so all the querying is done between the MP and the clients you are querying. CMPivot queries execute a PowerShell script on the client itself to gather the required information and the Client Notification Client Agent then reports the results back to the Notification Server (aka the MP). These results are then displayed in your CMPivot query results, with results obtained from online clients showing in DARK and results obtained from offline clients (for data that can be obtained from Hardware Inventory) showing in a LIGHT color.

SECURITY TIP: CMPivot and Run Scripts both execute PowerShell scripts on clients from C:\Windows\CCM\ScriptStore. Create an exception rule for your security software to allow PS1 files to execute from this directory.

CMPivot requires PowerShell 4 or higher, but you may not be able to query all entities without PowerShell 5. In order to access CMPivot you need permissions for Read on SMS Scripts, Run Scripts on the Collection object (in 1906+ you can use the Run CMPivot permission instead), Read on Inventory Reports, and you must use an account with access to the Default scope.

If you have SCCM 1906+, you can start CMPivot in a standalone mode. Just because you can doesn’t mean you want to, so for the best experience open your SCCM Console. Go to Assets and Compliance > Device Collections, right click on a Device Collection, and click on Start CMPivot. You can also highlight a Device Collection and click on Start CMPivot from the Collection ribbon.

CMPivot opens in a new screen and the first thing you see is DOCUMENTATION! The list of Entities contains items which are underlined / clickable links and items that are not underlined / not clickable. Underlined items indicate that you can only query for that Entity on online devices in real-time. Non-underlined / not clickable Entities indicate that you can query against Hardware Inventory even for offline devices (query results are light gray text), but for online devices (results are black text) you will get real-time query results as well. For a quick demo, right click on the Administrator Entity (from the left navigation pane) and click “Query All” which will put you in the Query tab, or from the Entities / Description list just click on the “link” for Administrators which will also put you in the Query tab.

From the Query tab, if you just leave your query with “Administrators” and hit Run Query, you’ll get a list of every administrator on every computer you have, including the built-in administrator account. For this reason, you’ll want to narrow your query down using Table Operators and Scaler Operators, all of which are documented back on that Home tab if you scroll down. I’m not going to deep dive into the different operators but take a look at them on your CMPivot Home tab in the built-in documentation.

For my first CMPivot example, I’m going to look up all Administrators on my systems that are not from a pre-defined list I already know about. Similar to other languages/codes, you can use the “pipe” | to get a “flow of data” that you’re used to. The exclamation point generally indicates NOT such as != meaning NOT EQUAL, !LIKE meaning NOT LIKE, etc. The query Administrators | where Name !like '%AdminDude%' | where Name != 'CONTOSO\TECH' | where Name != 'CONTOSO\Domain Admins' | where Name !like 'CONTOSO\admin%' will return all accounts that don’t have “AdminDude” in their name, not the CONTOSO\TECH account, not the CONTOSO\Domain Admins group, and not an account on CONTOSO that starts with “admin”. These are all accounts that I would expect, so the remaining accounts will be accounts that shouldn’t be there. Click Run Query and you’ll get a list of your results. Want to save that query to use later? Click the folder icon and then click the big green + button on the right side. To use it again, just open CMPivot and pick it from the list of your favorite queries.

Were those some great query results? You can click on Create Collection to save those Devices to work on later, or you can click on Export to make either a CSV or copy to your clipboard. The exported data will show all cells that display in your results.

Want to perform an action on a specific device? Right click on it in the results to pick from Run Script, Remote Control, or Resource Explorer.

Want to see any Device with an administrator that is a certain user or group? Right click on the user or group and click on Show Devices With or Filter By. Want to see all Devices that don’t have a user or group as an administrator? Right click on that user or group and click on Show Devices Without. Don’t recognize that user or group? Try Bing It and pray that Bing doesn’t recognize them either. In all seriousness say you had a query that returned a list of programs and you wanted to “Bing” a program to see what it was… this would open a browser and show you Bing results for that program.

My second example of CMPivot is how to lookup Office Add-ins that are on my computers. I’m doing an Office 2016 to Office 365 migration soon, so I need to find out what Add-ins are in my environment. Yes, there are other tools but CMPivot is already here and ready to go. I’m using the “OfficeAddin” entity and I’m already aware of add-ins made by certain publishers (CompanyName), with a certain FileName, and with a certain FriendlyName. The below query will show me all add-ins that don’t contain those values. I’m not going to screenshot the results but the same principles apply that you can manipulate the results by right clicking on them, you can save the query by hitting that big green + button, and you can make collections or export the data.

Third example is Hey expert, give me a list of computers with Chrome installed on them… I need it for my meeting that starts in 2 minutes. You can do a quick query against InstalledSoftware and wildcard it if you don’t know the exact full ProductName. Have a Run Script action that uninstalls that program? Right click on one of the Devices in your results and click on Run Script to run that script on the Device. Want to instantly uninstall that program from all Devices? Click Create Collection to make a collection with all the Devices from your results and then use Run Script against that collection.

InstalledSoftware | where ProductName like '%chrome%'

Fourth example is Hey expert, it seems like Computer1 is taking a really long to boot up and I think it’s your group policy messing it up. This query is a straight copy/paste from docs.microsoft.com on their CMPivot article. Copy/paste this into your CMPivot query, change the Device name in the query to yours, and click Run Query. We can see here that my slow group policy takes less time than the blink of an eye and with the exception of the September/October 2019 update SNAFUs our boot times have actually been really good. Click on one of the bars to see exact timing for that boot.

SystemBootData
| where Device == 'Computer1'
| project SystemStartTime, BootDuration, OSStart=EventLogStart, GPDuration, UpdateDuration
| order by SystemStartTime desc
| render barchart with (kind=stacked, title='Boot times for MyDevice', ytitle='Time (ms)')

When you click on the bar for one of the boot times, you get specific information about that boot time. Note that it shows you the query that it ran to get that specific info to… this was auto generated from you clicking on the boot time bar. You can see that the whole boot was roughly 30 seconds, including less than one second for group policy.

Do IT!!! All about Run Scripts.

The Run Scripts feature is a real-time way to perform highly customizable actions. Want to build a custom Right Click Tool? Use Run Scripts. There are several cool things about Run Scripts. First, nearly any PowerShell script you have, or you find on Google can be adopted into SCCM for quick wholesale use. Second, you can have an approval chain setup to prevent a rogue admin from destroying the universe on his own. Third, you can monitor everything that you’re running from SCCM’s Monitoring – Script Status page. Check out Microsoft’s Docs on the Run Scripts feature to see the Run Scripts permissions required for your security roles. You need at least PowerShell 3 to take advantage of Run Scripts, but if your script contains a feature that is only available in a higher version of PowerShell, you’ll need that higher version on all clients to take advantage of your script.

The cool thing about Scripts is that most of the time your existing PowerShell scripts work from SCCM with little to no modifications. I can’t emphasize it enough, but if you have a library of PowerShell scripts that you run on your devices you should add all of them into SCCM. They are centrally stored, you can tell who built them and who ran them, and they are always ran from C:\Windows\CCM\ScriptStore so you can use any number of security measures to only allow scripts to run from that directory.

SECURITY TIP (Again): CMPivot and Run Scripts both execute PowerShell scripts on clients from C:\Windows\CCM\ScriptStore. Create an exception rule for your security software to allow PS1 files to execute from this directory.

Let’s jump right in to where you create and manage your scripts. Go to Software Library > Overview > Scripts. You’ll see any scripts that you’ve already created here, or if you haven’t created a script yet you can start out from here by clicking on Create Script which opens the wizard to create a new script.

For my first example of scripts, I’m going to create a script in SCCM that will add the RSAT (Remote Server Administration Tools) onto someone’s machine… the capability to add RSAT via PowerShell was introduced in Win 10 1809. We all have that one “IT” person we work with that must have ADUC (AD Users & Computers) but has no idea how to install it… this script is for them. This script flips you off your WSUS server if you use one, installs RSAT (from the clouds), and flips you back to your previous WSUS settings. When you click on Create Script, it brings up the wizard to either copy/paste a script or you can also import an actual PS1 file. You even have a dropdown to select the script language and the great thing is you can select any language as long as it is PowerShell. Give it a name, copy your script in (or hit import and select a PS1 you already have), and click on Next.

# Check to see if Windows is set to use a WSUS server and store the value that controls this as $OriginalWUS

$OriginalWUS = Get-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU" -Name "UseWUServer" | select -ExpandProperty UseWUServer

# Set Windows to not use a WSUS server, so it can add the feature from Microsoft directly

Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU" -Name "UseWUServer" -Value 0

# Restart the Windows Update service to make the above setting apply

Restart-Service wuauserv

# Add the RSAT features

Get-WindowsCapability -Name RSAT* -Online | Add-WindowsCapability –Online

# Set Windows to use or not use a WSUS server, based on the value that was stored from what you previously used

Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU" -Name "UseWUServer" -Value $OriginalWUS

# Restart the Windows Update service to make the above setting apply

Restart-Service wuauserv

The next screen is just your standard summary view. Click on Next and when it’s done click Close. Your script will show up with an “Approval State” of Waiting for approval. By default, you’ll need another administrator to click on the Approve/Deny button. If you want to take the safeguards off and make it so you can approve your own scripts, go to Administration > Site Configuration > Sites, right click your site and click on Hierarchy Settings, go to the General tab, and uncheck the box for Script authors require additional script approver. One thing that I would also strongly recommend is that you set a security scope on your script to restrict who can use it. Allow me to scare you into using Security Scopes… the Run Script action works against any Windows Device in SCCM including any servers you have such as Domain Controllers, DHCP servers, DNS servers, etc. You should have Security Scopes already setup to keep your average helpdesk guy from seeing servers but setting a Security Scope on your scripts is just another layer of security.

Back on topic, you made a script and now it’s time for someone (or you) to approve it. Highlight the script and click on Approve/Deny in your ribbon or when you right click on your script. This opens the Approve or Deny Script wizard and the first screen shows you basic info like the script name, the script language (is it PowerShell or PowerShell or PowerShell), and the script itself. Click on Next and then on the next screen select Approve and click Next / Next / Close.

You can now run your script from one of several different ways. You can run the script on one Device by highlighting the Device and clicking Run Script on the ribbon, or by right clicking on the Device and clicking on Run Script. The Run Script wizard will pop up. Highlight the script you just made and click Next, then confirm the summary of what you’re running and click Next. You’ll see the “Script Status Monitoring” part of the wizard which shows you the status of your script and at this point you can either keep this open or click Close.

Want to see the status of that script you just ran? Go to Monitoring > Overview > Script Status and you can see any scripts that were ran, their overall status, and counts by total/completed/failed/office. Highlight the one you just ran and click on Show Status in the ribbon, or right click on the one you just ran and click on Show Status in the ribbon.

Detailed statistics on the script will be listed in the Script Status window that pops up. If you ran the script against a collection, you’ll see results for each system the script ran on.

Want to use the Run Script action on a Device Collection? Highlight the Device Collection and click Run Script from the ribbon or right click on the Device Collection and click on Run Script. Follow the wizard to run the script, and check on it’s status from the monitoring tab.

Don’t forget that you can also use the Run Script action from CMPivot as well. Right click on any Device that is a result in your CMPivot query and click on Run Script.