CMPivot, Run Script, & Client Notification (Again!)
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.
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.
Important Note: All of these columns should hold data accurate to within the last 15 minutes or less!
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.
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.
where (CompanyName !contains 'Microsoft') |
where (CompanyName !contains 'Bluebeam') |
where (CompanyName !contains 'Cisco') |
where (CompanyName !contains 'PhishMe') |
where (CompanyName !contains 'Adobe') |
where (FileName !contains 'umoutlookaddin.dll') |
where (CompanyName !contains 'Bentley')|
where (FriendlyName !contains 'Grammarly')
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.
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.
| 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.
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.
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.
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.