SCCM CMPivot and Run Scripts
What is CMPivot? What are Scripts? Why do I care about either of them?
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.
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.
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.
Administrators
| where Name !like '%LocalAdminAccountName%'
| where Name != 'Domain\SecurityGroup1'
| where Name != 'Domain\SecurityGroup2'
| where Name !like 'Domain\admin-%'
*** Save your queries by clicking the Folder icon on the right side and clicking the + button. Give it a name, click OK, and it will always be on your console.
The results that return for this query have a column for Device, ObjectClass (User or Group), Name (of the User or Group), and the PrincipalSource (either AD or Local). You can see how many devices the query has completed on and failed on at the very bottom of your CMPivot window.
What do I do with all these query results?
Option 1: In the top right corner of your CMPivot window, you can click Create Collection (self explanatory) or you can click on Export to take the results to a CSV or your clipboard.
Option 2: Right click on a Device and you can pick from Run Script, Remote Control, or Resource Explorer. You can also Pivot To, but that is more detailed than I'll go with this post.
Option 3: Right click on any column except device and you can Show Devices With, Show Devices Without, or Filter By. Any of these options will make a new query at the top. These are good options for designing a query if you're not comfortable with building one yourself, but you want more detailed results.
The Table Operators (Where is used in my example), Scalar Operators (!like and != are used in my example), Aggregation Functions, Scalar Functions, and Entities are all listed in the Home tab of CMPivot. There is also a lot more info at Microsoft Docs on CMPivot and the context for using all of the above.
What can I do with this Run Scripts button in SCCM?
For my example, our company had a bad DNS entry for something. Our administrators cleared the bad entry from our DNS servers, but several clients still have the bad record. I'm going to send a simple command to those clients with the Run Scripts option in SCCM to clear out the client DNS cache.
Open your SCCM console and go to Software Library, right click on Scripts, and click Create Script.
For the Script Name, input something that makes sense to you. For this example, we'll do the script name Clear-DNSClientCache because that is all the script will do.
For the script that gets ran you can either click on the Import button and pick an existing PowerShell script from your arsenal, or you can simply type in some quick PowerShell magic. For this example, type in the following one liner and click Next.
Clear-DNSClientCache
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.
Thanks for this. You might find the below link which has a RunScript I have put together and the comments useful.
ReplyDeletehttps://social.technet.microsoft.com/Forums/en-US/e183288b-4fdc-4103-babd-64210074b40b/sccm-runscript-to-remotely-collect-and-email-the-gpresults-report-for-the-machine-and-the-logged-in?forum=configmanagergeneral
Feel free to publish it if you wish.