Notes taken by Andre Dupre (thanks!)

Presentation by Ryan Ephgrave

Presentation Link: https://www.ephingadmin.com/cmscripts-presentation/#1

CMScripts

What and Why?

CMScripts are great! And powerful (and a bit dangerous). One advantage is you never need to be local admin on these machines, you can just remotely run scripts and CM will run it for you (as SYSTEM).

Scripts run very quickly, uses fast channel to send the command and get the results. There is a rate limit on the number of clients it runs on simultaneously (42 at once per DP). So still quite quick but in larger environments might be slow-ish.

Scripts only run on online machines (cached for ~1 hour on DP). This isn't for Config changes that need to stick (for those you should use Configuration Items).

Think of this as a way for you create your own Right Click Tools within the Console.

https://docs.microsoft.com/en-us/sccm/apps/deploy-use/create-deploy-scripts

Logs for Troubleshooting

Server side log:

BGBServer.log

Use this to see that your script got sent

Client side log:

CCMNotificationAgent

Scripts.log

Actually named correctly!

Does use Base64 encoding for the script which needs to be converted if you want to see what it ran.

Maintenance Tasks - "Delete Aged Client operations" will remove script run data. You may want to up this to longer if you're using Scripts to make import config changes that need to be tracked.

Security

If you have more than one CM admin you should leave ticked the box (hierarchy settings) to require script approval. Its not perfect but better to have some approval rather than none.

You probably should remove the Default Scope, and change the security scope to a specific role. You can run any script you see on any computers you see (so if collections are scoped you have some control there).

Roles:

• Runner
• Approver
• Author

There are PowerShell cmdlets to Start a script (without parameters), but not to do things like Create the script.

Script Creation Notes:

Gather Logs Script: https://github.com/Ryan2065/Presentations/blob/master/CMScripts/GatherLogs.ps1

Start Stop Service Script: https://github.com/Ryan2065/Presentations/blob/master/CMScripts/StartStopService.ps1

Be careful when writing your scripts, write for the lowest common denominator. If you still have Win7 with PS v2 then you can't use many cmdlets.

Scripts run as SYSTEM so if you want it to interact with network resources then you need to make sure that Domain Computer object has access to read and/or write to that location.

For now, you need to re-create scripts if you want to Edit. The Edit button is in latest TP but not out yet in CB.

Make sure you do regex validation on your string parameters to prevent things like double quotes, since this can break.

Scripts on the SQL Side

Views are very unified, filter on dbo.vSMS_Scripts.

There's not great reporting yet on Scripts. If you ever want to see execution history you'll have to dig in the DB for now.

Dbo.vSMS_ScriptsExecutionTasks

The Scripts are stored in an XML string, in SQL… and it is Base64 encoded.

To view the parameters that were run you need to decode it in SQL, cast it as XML, then rerun your SQL query. This will show you the decoded XML data.

Now you can copy this value out and view it in something like VSCode. Now you can see ParameterValue, to see what was run and when.

In the console you do have Script Status (monitoring tab) but this doesn't show you much other than success/fail.

SQL Query to decode base64 parameters: https://github.com/Ryan2065/Presentations/blob/master/CMScripts/ScriptExecution.sql

Community Hub

In TP only right now. Not sure if you can get to it from outside of the console (it is a git repo (VSTS)somewhere).

https://docs.microsoft.com/en-us/sccm/core/get-started/capabilities-in-technical-preview-1807#bkmk_hub

MS wants the community to post stuff here so everyone can share and benefit.

Right now you can share Scripts and Reports. Eventually you should be able to share 3rd party modules and Configuration Items.

This is very new. Group immediately voiced concerns about who is allowed to upload/download stuff, what if I accidentally publish stuff…

This is very new, it would probably have RBAC. The hope is that people who have awesome reports (which the CM Team doesn't create anymore) can publish them and everyone can benefit.

CMPivot

What is CMPivot?

Run queries to get live data back from online clients. Runs against all devices in the collection. It is essentially a script with a fancy UI on top. There's a new query language (based on Azure Log Analytics). It gives you a TON of information to query against, possibly it is overwhelming. So start simple.

Example:

Just start typing File(). Anything on the left of your first pipe operator gets sent to the computer

File('c:\users\*\')

Will pull back all User profile files! Then you can do things like "Show Devices with [a particular file]". You can then use PivotTo, to run a different query on sub-devices.

About CMPivot

This is a script called CMPivot. It is hidden but you can view what this script is with SQL. However, it is encoded so you need to use VARCHAR and there is no view, you have query directly off the table. Then you can get the actual text of this script.

The script is one big IF statement. Don't be worried that the variable is called $wmiquery, that's just a name they kept.

If you tear this apart you can see how Microsoft does some really useful things like how they parse logs files in Powershell.

Shortcomings

CcmLog()

Will give you back the last 50 lines of a log file.

CcmLog('Scripts')

Will pull back 50 lines of the Scripts.Log file on the client. Remember, anything on the left side of the query is send to the client, anything on the right side is not.

CcmLog('scripts') | Where LogText == 'Starting CCMEXEC Service…'

This doesn't send your Where clause to the client. It queries all computers, pulls back the first 50 lines of the log (per computer in the collection) into the database, then runs the Where to filter the results in the view returned to you in the console. Then, when you close the query it deletes those lines from the DB.

This doesn't mean CMPivot is bad, just maybe don’t run your queries against ALL systems and expect it to filter first. It will be a hit on your database, so be careful. Start with a small test collection, get your queries correct and think through possible performance impacts before running them.

Examples of Using CMPivot

Administrators()

Will pull back who is local admins on the machines.

Administrators | where == 'ActiveDirectory'

Pulls back just non-local admin accounts.

Use the PivotTo to easily add and remove query parts. This will actually filter the results from what is in the DB, doesn't re-run the query.

Device | Summarize dcount (Device) by Manufacturer

Will show you all devices by manufacturer! Very fast and very cool.

Service | Where Name == 'RemoteRegistry'

From here you can right click on the device and run a Script! For example, our Start/Stop Service Script. Then re-run the query, and see that it is running.

SoftwareUpdate

Pulls back what software updates are installed or not, group by bulliten ID, etc. No need to run a report you can give an answer based on live data when you boss asks where you are with that patch deployment?

Registry()

Searches the registry of the devices.

Other Notes:

If you query against a class that is inventoried it will show you the Hardware Inventory data (shows data as grey) until the device responds back (then turns black to indicate live data). This is useful if you're querying against collections with offline devices you can at least get the Hardware Inventory data to supplement.

There is a cleanup task that runs every 7 days to clean up old CMPivot data in the database. So it won't just be orphaned.

CMScripts + CMPivot is really nice for real time management. Right Click on the Device and you get "Run Script" "Remote Control", "Resource Explorer".

Queries do not appear to be case sensitive.

You can use the back button to go through old results (same session) without having to re-run the whole query.

You do need Default Scope…

In TP now there's a Favorites Folder, so you can save queries for re-use later. Can't edit once saved atm…

You can create a collection based on the results of your queries but it uses DirectMembershipRules because the results go away as soon as you close the window.

More example CM Pivot queries: https://www.systemcenterdudes.com/sccm-cmpivot-query/

Management Insights

You want something to tell you that your stuff sucks so you know what you need to fix. This kind of does that for you.

MS has all these best practices (enable WSUS cleanup, don't have empty collections, etc.) that MS really really thinks you should be doing. But without changing that setting for you.

Clicking on a rule will show you mgmt insights for those types. For example;

Collections > Collections with query times over 2 seconds.

Then you can see which are slow. Then use Take Action to see them all and fix them.

There's a Take Action button, mostly these actions just bring you to where you need to fix it in the console. Then you need to fix it. Kinda neat.

Great for cleanup tasks, do it when you've got time.

Other maintenance tasks that people use?

WSUS cleanup. Also blog post for WSUS cleanup

https://tcsmug.org/blogs/jeff-carreon

Cm Tools now included on site

Check out SMS_PS1\cd.latest\SMSSETUP\Tools and \ServerTools

CEViewer - collection evaluation viewer, lets you review collection evaluation to find when it is bad. Tells you how long it takes to evaluate the collections. Be advised, it only goes off of LastRun, so if there's a problem with your site or point in time interruption.

SQL Cleanup Script

Ola Hallogren and Steve Thompson have a post to re-index site database. If indexes are bad then SQL has to go row by row to find the data. This script fixes that. Its great.