SCCM CMPivot and Run Scripts
I recently attended MMS 2019 and took a great session from Jason Sandys and Tom Degreef on CMPivot and Run Scripts. They dove deep into the technical aspects of both of these somewhat new "fast channel" features of SCCM.
Full disclaimer: I do not know everything and I may occasionally not know as much as you. Test everything you see here on a LAB environment and make sure you fully understand it. If you have any suggestions for improving this post, please feel free to drop a comment and I'll give you some kudos for your edit.
Over simplified, CMPivot is a near real-time (not milliseconds, but usually seconds, and definitely no more than a few minutes) way to query information about your systems. You can find out literally anything about any system within your reach.
Scripts pairs nicely with CMPivot because it is a real-time way to run scripts against your query results, or just another old collection for that matter. The Scripts feature allows you to store a library of your commonly used PowerShell scripts in SCCM, with an optional "peer approval" mechanism, and then run these scripts against Device Collections, CMPivot query results, or even a single device.
One key thing about CMPivot and Scripts is that they are running real-time against systems that are ONLINE only. The workload from CMPivot and Scripts is handled on the SCCM server, meaning all queries and scripts are executed between the SCCM server and the devices you are querying. There is not any traffic directly between the technicians device and the target devices of your queries or scripts.
There are lots of great articles out there on both CMPivot and Scripts, so I'm going to spare all the techno babble on them and just show a couple of quick demos.
How to find all Administrators on a device using CMPivot
Open your SCCM Console, go to Device Collections, and pick a Device Collection you want to query. Right click on the Device Collection and click Start CMPivot.
| where Name !like '%LocalAdminAccountName%'
| where Name != 'Domain\SecurityGroup1'
| where Name != 'Domain\SecurityGroup2'
| where Name !like 'Domain\admin-%'
Click Next at the Summary page and Close the dialog when it completes.
Have your trusty coworker go to the Scripts library, right click on the script you just made, and click on Approve/Deny. The first wizard screen that pops up shows your code. Once it's reviewed and tested, click Next. Click the Approve button and click Next. Click Next on the Summary page and close the dialog when it completes.
How do you use this great little Script we just made?
Option 1: Go to a Device Collection or Device, right click on it, and click on Run Script. The Run Script Wizard will give you a list of your scripts to pick from. Highlight the Script, click Next, and then after carefully reading the Summary click Next again. You'll see the Script status in the next window with a list that contains the Device Name, Status code of the script execution, Exit code, and any outputs that your script does.
Option 2: From a CMPivot window with results from a query, Right Click on a Device and click the Run Script option. Then follow the steps from Option 1 just above to select and execute your script.
What if I closed the Wizard and want to see my script results?
In your SCCM console, go to Monitoring\Overview\Script Status and you'll see the Script Status. If you've ran the same script multiple times, go to the Last Update Time or Client Operation ID columns to see which one is later / higher. Double click the script and it will bring up a window with Script Details, a Summary of results, and Run Details.
How can I tell if this worked on my Device that I ran this script against?
On a Device you ran this Script against, browse to C:\Windows\CCM\ScriptStore and you should see a new PS1 file show up here whenever a Script is sent from SCCM. For this example, you could also run a PowerShell window on the device and use Get-DNSClientCache to see what is showing up in the DNS cache. Execute the Script from SCCM. Then run the Get-DNSClientCache on the device after the Script from SCCM has executed and you should notice an empty or almost empty DNS cache.
For a more in depth step by step on how to use Scripts in SCCM, go to Microsoft Docs.
What if my @#$%ing security products are blocking these great features?
You need to allow PS1 files to run from C:\Windows\CCM\ScriptStore. SCCM will always use this folder as its running directory.