CMHealthCheck is now a PowerShell Module

What is it

CMHealthCheck is a PowerShell module filled with bubble wrap, Styrofoam peanuts, and 2 possibly useful functions, aimed at collecting a bunch of data from a System Center Configuration Manager site server, and making a pretty report using Microsoft Word.  But doing so without needing to manually download and store a bunch of script files and so on.

It’s still based on the foundations laid by Rafael Perez, with quite a bit of modification, prognostication, prestidigitation, and some coffee.  Special thanks to Kevin (@thenextdotnet) for helping point me in the right direction to move it all from scripts into a module.

Why is it

I get asked (okay, told) to help customers find out why their site servers are running slow, showing red or yellow stuff, or just to get them ready to upgrade from whatever they’re on to whatever is the latest and greatest version of ConfigMgr.  They also like a pretty Word document with a spiffy cover page.

How to use it

  1. Install the module on the ConfigMgr CAS or Primary server you wish to audit –> Import-Module CMHealthCheck
  2. Run the Get-CMHealthCheck function (see documentation on Github – linked below)
  3. Install the module on a Windows computer which has Office 2013 or 2016 installed (hopefully NOT the same computer which was audited)
  4. Run the Export-CMHealthCheck function (see documentation on Github – linked below)
  5. Save the Word document and mark it up.

Where to Get it

How to Complain About it

Because I know some of you will, but that’s okay.  Without complaints, we have no way of identifying targets.  Just kidding.  I need feedback and suggestions (or winning lottery numbers and free food coupons).  Please use the “issues” link on the GitHub repository to submit your thoughts, gripes and so on.

Advertisements

Invoke CM_Build over the Web

Updated 10/15/2017 – Added -Override example

118057481

First off: WTF is CM_BUILD?

CM_BUILD is a PowerShell script that configures a “vanilla” Windows Server machine into having Configuration Manager Current Branch installed.  This includes ADK, MDT, Server Roles and Features (WSUS, BITS, etc.), SQL Server, ConfigMgr itself, and a few goodies like Right-Click Tools, ConfigMgr Toolkit.  The GitHub repo has a cute readme markdown page filled with overcaffeinated gibberish on how to use it.  CM_SiteConfig is the “part 2” to cm_build, which configures ConfigMgr into a semi-functional site.

Short answer: https://github.com/Skatterbrainz/CM_Build

Okay, why CM_BUILD?

I don’t know.  Why do we do anything?  For the thrills? I could have taken up robbing banks, raising a crocodile farm, or breaking world records of swilling down cans of Four Loco while working on electrical equipment.  But I chose the boring life.  And while I’m bored, I hate clicking buttons repeatedly, so …

I got inspired by Johan and Mikael’s ConfigMgr Hydration Kits and Deployment Fundamentals Vol. 6 book examples, and Niall’s noob scripts, (I know it’s not actually called that, but it sounds cool to say “Niall’s noob scripts“), and after 45 cups of terrible coffee I said “I can shove all that into an XML file and call my JSON friends up and laugh hysterically at them, saying things like ‘You and your snotty little JSON drivel!  Always mocking poor, starving little XML.  Well, I’ll have you know I can still write XML, and probably even a little COBOL! So what do you think of that?!  Hello?  Hello?  Did you just hang up on me?!! WTF!

Anyhow…. Hold on, I need to get my dog outside before she has an accident….

okay, I’m back.

Why Invoke it over the Web?

There are several potential reasons for wanting to do this:

  • I was really bored and it’s been raining all freakin day, and…
  • It’s 3am and I can’t sleep, and…
  • I saw this, and …
  • I wanted to pull this off within Azure, using a VM extension, without having to import any actual files, and it would be cool to tie all this together with a runbook so I can send a text message “new lab configmgr p01“, to fire off a lab build in Azure and have it text me back “your stupid lab is ready, now leave me alone!” then I can forget it’s still running and it runs all my MSDN credits down to $0 until the next monthly cycle, and…
  • I scrolled through Dan Bilzerian’s twitter feed just long enough to hate my boring life, and needed a distraction, and…
  • It seemed like something cool to try

Example

Time to put on a poker face and act serious now.  The example below calls the cm_build.ps1 script from the GitHub master branch URL, converts it into a -ScriptBlock object, and passes in the -XmlFile parameter value using the Github Gist raw URL (you can make your own by copying the cm_build.xml into your own “secret” Gist, so you don’t openly share sensitive information to the whole world)

$ps1file = 'https://raw.githubusercontent.com/Skatterbrainz/CM_Build/master/cm_build.ps1'
$xmlfile = '<your-gist-raw-url>'

$script = Invoke-WebRequest $ps1file
$scriptBlock = [ScriptBlock]::Create($script.Content)
Invoke-Command -ScriptBlock $scriptBlock -ArgumentList @($xmlfile, $True, $True, $True)

But you can also invoke the interactive gridview menu using the -Override parameter, by simply appending one more $True into the -ArgumentList array.

Invoke-Command -ScriptBlock $scriptBlock -ArgumentList @($xmlfile, $True, $True, $True)

Then you get this budget-sized, corner-cutting, hack of a menu to choose from…override-gui

You may see a red warning about “Split-Path : Cannot bind argument to parameter ‘Path’ because it is null.”  I’ll fix that soon.  It only impacts the log output, but nobody reads log files anyway, right?

Anyhow, it’s 3:33 am, and I’m still typing, which is probably bad for my health, but if two people read this and it actually provide useful information one of you, mission accomplished.  Actually, I know for a fact this is bad for my health.  Regardless, I ran the above snippet (with a real URL in the $xmlfile assignment) in my Hyper-V duct-tape and chewing gum lab at home, and it worked like a charm.  Now I can log into the server, open the ConfigMgr console and proceed with CM_SiteConfig, or apply real world tactics and break the ConfigMgr site entirely and start over.

zzzz

Cm_SiteConfig 1.3 – Importing Queries and Collections

Updated:  Added missing graphic of menu

This may be the only blog post I do before I head down to Microsoft Ignite this coming weekend. I’m also trying desperately to keep up with younger colleagues on exam studying, damn it. Getting old sucks. Anyhow. I wanted to discuss some things about cm_siteconfig 1.3 that I may have glossed over (or snored through). I need a break from labs right now anyway.

What is Cm_SiteConfig?

I’ve been working on a pair of PowerShell scripts to automate the building of a System Center Configuration Manager site server.  This could be a CAS or a Primary, but basically, each of the two (2) scripts uses a dedicated XML configuration file to control what it does.  Cm_Build.ps1 installs all of the prerequisite items to make a vanilla Windows Server machine into a Configuration Manager site server.  It does not configure the Configuration Manager site itself however.  So, things like Discovery Methods, Boundary Groups, Client Settings, Site Settings, Collections, Queries, OSD items, Applications, etc. need to be created.  This is where Cm_SiteConfig comes in.

Cm_SiteConfig.ps1 uses Cm_SiteConfig.xml to configure an existing Configuration Manager site server with things like Discovery Methods, Active Directory Forest Connection and Publishing, Boundary Groups, Site System Roles (multiple), Client Settings, Client Push settings, Distribution Point Groups, Operating System Images, Operating System Upgrade Installers, Collections, Queries, Applications, Maintenance Tasks, System Accounts, Enpoint Protection Policies and even Console Folders in which to organize these things.

The Override Feature

Normally, when each script is run, it processes the XML configuration data in sequential order by reading the “use” property of each item (if use=”1″ it’s processed, otherwise it’s ignored).  When using the -Override parameter, you are presented with a graphical menu, from which you can choose individual tasks to execute, while skipping over the others entirely.

For example, if you wanted to just import custom queries and collections, you can do the following:

  1. Edit the cm_siteconfig.xml file to suit your site configuration (within the [cmsite] tag: sitecode, forest, etc.)
  2. Edit the queries and collections entries (if needed)
  3. Open a PowerShell console on the Site Server using “Run as Administrator”
  4. Run the cm_siteconfig.ps1 script as follows (see example below also):
    cm_siteconfig.ps1 -XmlFile cm_siteconfig.xml -Detailed -Override
  5. Select the features you wish to process from the Gridview menu, and click OK (lower right of menu)
  6. Watch the output and refresh your console

You can do the same with other sections of the XML file.

Important Note!

Be sure to run the Folders section at least once, to insure the folders are created prior to other sections being processed which may rely on those folders to be present.

Where to Get it – Here

Feedback / Suggestions / Bug Reports – Here (please!!)

Back to studying.  I hope to run into some of you during Ignite!  I’m easy to spot:  I look like every other white guy roaming the expo floor with too much swag falling out of every pocket and trying to hold a drink and a cellphone at the same time.

cm_siteconfig 1.2

I’ve been busy this Labor Day weekend. Besides vacuuming up water from a broken water heater at 4am, mowing a ridiculously big lawn with a ridiculously small lawnmower, and avoiding the oceanfront tourist freakshow (or almost), I spent a fair amount of time on cm_build and cm_siteconfig.  Needless to say, I’m not feeling very funny right now, but I assure you that I will return to my usual tasteless, dry, ill-timed humor after a word from these sponsors.  Even though I don’t have any yet.  Actually, if you blink, you might miss some hidden jestering below.

I’ve already discussed these two PowerShell scripts in a previous blog post, but to update things: cm_build is still at 1.2.02 from 9/2/2017, and cm_siteconfig is now at 1.2.22 from 9/2/2017.  That’s a lot of 2’s.  Anyhow, here’s what each does as of the latest versions:

Note: The version numbers are in parenthesis to indicate lab configuration.  The XML allows *YOU* to configure the installation ANY WAY YOU DESIRE, and to reference ANY VERSION you desire.  The versions below are just what I used to test this thing (so far) about 84 times.

LAB TEST NOTE: Prior to running this script, build the server, assign a name, static IP address, and join to an Active Directory domain.  Then take a snapshot (VMware) or checkpoint (Hyper-V) to roll back if anything spews chunks along the way.

Prep Work

cm_build.ps1 / cm_build.xml

  • Install Windows Server roles and features (except for WSUS)
  • Install Windows 10 ADK (1703)
  • Install Microsoft Deployment Toolkit (MDT) (8443)
  • Install SQL Server (2016)
  • Install SQL Server Management Studio (2017)
  • Configure SQL Server maximum memory allocation
  • Configure SQL Server ReportServer DB recovery model
  • Install WSUS server role
  • Run the WSUS post-install configuration
  • Install System Center Configuration Manager 1702
  • Install ConfigMgr Toolkit 2012 R2
  • Install Recast Right-Click Tools

GENERAL NOTES:  The cm_build.xml structure starts with the “packages” section, which dictates what gets executed and in what order.  The “name” element establishes the package code link used by all of the other sections, such as payloads, detections, files, and features. Note that the files section only requires the pkg key value for SQLSERVER and CONFIGMGR.  Other files can be created without using a matching pkg key if desired.

LAB TEST NOTE: I strongly recommend taking another snapshot (VMware) or checkpoint (Hyper-V) at this point, prior to running cm_siteconfig.ps1.  This will help avoid angst and loss of temper while making iterative changes to cm_siteconfig.xml and retesting.

cm_siteconfig.ps1 / cm_siteconfig.xml

  • Create SCCM accounts
  • Configure the AD Forest connection
  • Configure Server Settings
    • Software Deployment Network Access Account
  • Configure Discovery Methods
    • The template AD User Discovery Method adds AD attributes: department, division, title.
    • The template AD User and System Discovery Methods add filtering for password set and login periods of 90 days each
  • Configure Boundary Groups
    • The template creates 4 sample boundary groups
    • You can enable creating Site Boundaries as well, but the default is to allow the AD Discovery to create subnet/IP range boundaries
  • Configure Site Roles
    • Management Point
    • Service Connection Point
    • Distribution Point (with PXE)
    • Cloud Management Gateway (still in development)
    • Software Update Point
    • Reporting Services Point
    • Application Catalog Web Service Point
    • Application Catalog Website Point
    • Asset Intelligence Synchronization Point
    • (more to come)
  • Client Settings
    • Still in development, but…
    • The template creates two (2) sample device policies: Servers and Workstations
  • Client Push Installation Settings
    • Still in development
  • Create DP groups
    • The template creates 4 sample DP groups
  • Create console Folders
    • The template creates sample folders beneath: Applications, Device Collections, User Collections, Boot Images, Task Sequences, and Driver Packages
  • Create Custom Queries
    • The template creates two (2) sample device queries
  • Create Custom Device Collections
    • The template creates three (3) sample user query-rule collections, and 15 sample device query-rule collections
  • Create Custom User Collections
  • Import OS Images
    • The template imports two (2) OS images: Windows 10 1703 and Windows Server 2016
    • Source media is not included.  Batteries not included.  Just add hot water and stir.
  • Import OS Upgrade Installers
    • The template imports two (2) OS upgrade packages
  • Configure Site Maintenance Tasks
    • Excludes Site Backup, and Database Reindex tasks for now.  I plan to have these enabled soon.
  • Create Application Categories
    • The template includes (5) sample categories: IT, Developer, Engineering, Finance, General and Sales
    • For now, the detection rules are implemented using a chunk of freshly-cut, carefully seasoned, and slow-roasted PowerShell code, because it’s easier to shoe-horn into this process and provide flexibility and adaptability.  And besides, all those syllables sound kind of impressive after a few mixed drinks.
  • Create Applications
    • The template includes examples for 7-Zip, Notepad++, VLC Player, and Microsoft RDC Manager

What’s Next?

  • I’m still working on this, so more changes/improvements will be coming.

Q & A

  • Is it really “open source”?
    • Yes! Go ahead and pinch yourself.
  • Did you write all this code yourself?
    • Yes, sort of.  Some of the pieces were adapted from, or inspired by, the outstanding work done by other amazing people like Niall Brady, Nickolaj Andersen, Johan Arwidmark, Mikael Nystrom, Maurice Daly, Stephen Owen, Anders Rodland, Raphael Perez, Chrissie LeMaire, Jason Sandys, Sherry Kissinger, and many others I can’t think of right now.  Thanks to Kevin B. and Chris D. for helping me find better ways to solve key areas of the overall project.  The XML constructs and process model are my own hallucinatory work.
  • Can I Make Suggestions / Request Changes?
    • ABSOLUTELY!
    • Please use the “Issues” feature in Github to submit bugs, feature changes and enhancements, etc.  I will make every effort to review, assess, feebly attempt, fail to satisfy, cry over insecurities of self-doubt, angrily assign blame, throw objects across room while swearing like a drunk sailor, solemnly accept defeat, and ultimately: try to make it work as requested.
    • Note that creating a Github account is required for submitting Issues.  Github accounts are free and they make you feel warm and fuzzy inside.
  • Does cm_build also download required installation media?
    • No.  I’m too lazy.
    • 99.999999% of my customer engagements involve a ‘kick-off’ call in which we discuss prerequisite action-items prior to beginning work. This typically includes requesting the customer to have all installation media and licensing information ready to go.  Which they typically do, so I didn’t feel the need to bother with that aspect (not to mention, try to keep up with version changes and new URL’s over time)
  • Can cm_build be used to install a Central Administration Site?
    • Yes.
  • Can cm_build be used to install a Secondary Site?
    • Yes.
  • Can cm_build be used to destroy alien civilizations?
    • Probably not.
  • How was this thing Tested?
    • In a small dungeon beneath a floating castle in a lake atop a tall mountain.  Okay, in my home lab, next to the dog’s sofa.
    • It’s been tested about 84 times as of 9/4/2017.  That’s about 55 times for cm_build and 29 times for cm_siteconfig.  But by the time you’ve read this, it’ll have increased again.
  • What was/is your Test Environment like?
    • Windows Server 2016 (Dell R710) server with Hyper-V
    • 3 virtual machines: DC1 (domain controller), FS1 (file server) and CM01 (configuration manager server)
    • CM01:
      • 16 GB memory, 4 disks (C: for OS, E: for apps, F: for content, G: for logs, etc.), 2 vCPUs
      • Windows Server 2016 Standard
    • Me:
      • Coffee cups falling off the desk, on ever flat surface, in the trash can, on top of one of my dogs, and a few more in the kitchen sink
      • Empty snack bar wrappers strewn across the room
      • A tattered doggie toy-squirrel hanging on a door knob for some strange reason.
  • What’s the point?
    • It’s been challenging, and fun, to work on.  It saves me time and headache at work and in my home lab.
    • It opens up potential secondary capabilities, like automating installation documentation and building an extract/build process to close the circle of life, open a wormhole, fill it with black holes and jump in for a ride.  I really need to stop listening to so many podcasts.
  • Why should I care?
    • You shouldn’t.  You can go do something fun now instead.
  • Why XML?
    • Because I &^%$ing hate JSON, and INI is too limited.  I thought about YAML, which looks a little bit like JSON, but not like it was punched in the face with a meat tenderizer mallet, but then I had to mow the lawn, and completely forgot why.
  • What have been (or continued to be) the biggest Challenges?
    • Time
    • Sleep
    • Deciding where to draw logical boundaries between automating and leaving out for manual work later
    • Refactoring, refactoring, re-refactoring, and re-re-refactoring before refactoring some more
    • More refactoring
    • Incomplete Microsoft ConfigMgr PowerShell cmdlet reference documentation*
    • Incomplete/inconsistent Microsoft ConfigMgr PowerShell cmdlet features*
    • Incomplete/inconsistent mental capacity (mine)
    • Occasional power and Internet service outages and lack of a power backup system (budget, weather, drunk drivers)
  • Does humor belong in IT?
    • Yeah.  It has to. Over 35 years in this field of work, I’ve seen what happens to people who forget that. It doesn’t end well.

*  I’m not going to beat them up on this, since they are already making Herculean efforts towards modernizing and cleaning up ConfigMgr, so the gravy should have a few lumps.

What is this, cm_build and cm_siteconfig stuff?

UPDATE 8/29/17 – Corrected a mistaken attribution for Driver Automation Tool to Maurice Daly.

I’ve been kind of quiet lately.  Quiet, as compared to how normally chatty I tend to be online, that is.  It’s been a mix of personal life, work life and hobby life.  Personal aside, work life has been a little quiet this week, which is fantastic for catching up on things, and spending a little time with my hobby life: wordworking and coding.  I don’t have a Yankee Home Workshop space or anything, but I get by okay.  If I ever win a massive PowerBall lottery payout, my neighbors will have to relocate, as I will need their spaces for expanded hobby capabilities.

Anyhow, on to the meat and potatoes:  I’ve been toying with this for quite a few years, but could never align the right perception, time allocation and initiative, to make it gel.  This past week and weekend they just happened to align at the right place and the right time.

Why?

I wanted to devise a different approach to specifying the configuration rules in advance and then executing them with limited (or zero) human interaction.  The rules should be the design document.  Or at least the basis of such.  It shouldn’t be difficult to derive the XML data into a readable document, such as Microsoft Word or HTML.  And the code should be reusable and resilient.  Run it as many times as desired (or needed) on a given server and it should ignore what is already done, and only work on what hasn’t been done yet.

The Bundle

cm_build and cm_siteconfig are two (2) pieces of a bundle of scripts aimed at simplifying and streamlining the building and configuration (respectively) of a System Center Configuration Manager site server.  Actually, either a Central Administration or a Standalone Primary, site server.  It’s flexible enough, in theory, to contend with other roles and purposes.

cm_build

cm_build.ps1 is a PowerShell script that reads from cm_build.xml, which contains the logic (configuration data) to take a “vanilla” Windows Server host (virtual or physical) up to having ADK, MDT, WSUS, SQL Server, SQL Server Management Studio, Configuration Manager, ConfigMgr Toolkit and Right-Click Tools (optionally) all ready to go.  The baseline server only needs to be prepared for general purpose server capability (assigned name, static IP address, domain-joined, etc.) and have the proper resources allocated (memory, processors, disks, etc.).  The only other requirement is having the installation media accessible.

The default configuration of the cm_build.xml file refers to a set of UNC shares on a common network, but you could reference a local disk, removable media, or any other source which can be read from using PowerShell.

cm_build is farther along in development and testing than cm_siteconfig.

cm_siteconfig

cm_siteconfig.ps1 works very much like cm_build, in that it reads from cm_siteconfig.xml in order to configure a “vanilla” Configuration Manager Primary or Central Admin site server to whatever you prefer.  This will include:

  • AD Forest
  • Discovery Methods
  • Boundary Groups
  • Client Settings
  • Client Push Installation
  • Queries
  • Collections
  • Applications and Deployment Methods
  • Site Roles: Asset Intelligence, Software Updates, etc.
  • Distribution Point Groups
  • Operating System Images
  • Operating System Installers (upgrades)
  • Console Folders
  • Maintenance Tasks

Of these, the items in blue are ready to test.  The others are in development and moving as quickly as my coffee intake can allow.

What about Other Features?

Software Updates, Endpoint Protection, Windows 10 Servicing, Task Sequences, and so on, are all up for consideration.  However, each of these has some unique aspects that may change whether I try to incorporate them sooner, later or never.  For example, Task Sequences can be exported and imported, and with each new ConfigMgr build, the PowerShell interfaces get more robust, which delves into version impacts and compatibility aspects.  I need to start small and work upward, and start with the more mature stack of tools, which helps insure the broadest platform support.

I’m getting ahead of myself here.  Much of this is obviously predicated on time, initiative, and my own, limited abilities.  Some might call this “pie in the sky” thinking, and it may very well be just that.  Whatever, it’s a nice hobby for me that doesn’t cost a lot of money and I can do it indoors.

What about Drivers?

I’m leaving drivers out of this for now, because Maurice Daly basically blew the doors off of that with this, and I don’t even want to think about reinventing that wheel.  Kudos for that one.  One of many amazing tools he has produced.

Is it done?

No.  But cm_build is fairly stable and tested as of now.  cm_siteconfig is still in development, but there’s enough to kick the tires and see if it leaks transmission fluid on your rug.  It’s free, and it’s open, so that makes it sort of freeopen.  But that could likely be an offensive word in another language.

Where is it?

[Here]

What’s Next?

Who knows.  That’s what makes it fun to mess with.  Kind of like a blind, drunk guy poking at a rattle snake with a pencil.

Master Plan – Automating an SCCM Site Installation

I was thinking, “Man, (or woman), it would sure be coolio if I could push one button and *presto!* I have a fully-built SCCM site server, without having to install anything but Windows itself“.  There’s some really shiny stuff out there already, like hydration kits and prerequisite installers.  But then, I thought “That’s not enough!  I need it to be ‘real’.  I need more!!” I want to build the site itself, and configure EVERYTHING to be JUST LIKE A REAL site out in the real world.  So, here’s how I sketched it out…

  1. Install Windows Server
    1. Do NOT patch it
  2. Create all the folders in all the right places
  3. Install Windows Server roles and features
  4. Install ADK for Windows 10
  5. Install MDT
  6. Install SQL Server with file auto-growth settings, Domain Service accounts, and register the SPNs
  7. Configure SQL Server memory limits
  8. Install the WSUS role
  9. Run the WSUS post-install configuration step
  10. Create file NO_SMS_ON_DRIVE.SMS on C:\
  11. Install Configuration Manager 1511 (not the latest baseline) with a Site Code, Site Name, Roles
  12. Configure Discovery Methods, including Network Discovery, where each one runs every day
  13. Initiate Discovery Methods
  14. Create Site Boundaries and Boundary Groups
  15. Configure Client Settings
  16. Configure Client Push Installation
    1. Configure automatic client installation and client upgrades
  17. Create and Configure Query-based Collections
  18. Configure all Query Collections to update every hour
  19. Create and Configure a set of Applications
  20. Deploy Applications to Collections
  21. Add everyone in IT to the “Full Administrator” RBAC role in SCCM
  22. Add everyone in IT to the SQL Server server admins role
  23. Install Symantec Antivirus, McAfee Antivirus, Cylance Agent and anything else on the same host
  24. Turn on the Domain Firewall
  25. Create an OSD configuration
    1. Windows 10 Image captured from a 5-year old machine with EVERY conceivable application installed.  If it’s not at least 10 GB, we have to repeat until it does.
    2. Boot Images – add every component and every driver for every model we have
    3. Drivers – everything.  all of it.
    4. Applications – only those product names which begin with 0 – 9 or A – Z.
    5. Task Sequences – create a dozen with names that mean nothing to anyone unless they’re on drugs
    6. Deploy each TS  to “All Systems” via PXE without a password
  26. Set a scheduled task to reboot the site server every night at the same time the backups and SQL jobs are supposed to run
  27. Add “Domain Users” to the local Administrators group
  28. Turn off Site Maintenance tasks: backups, reindex.
  29. Do not install Ola’s tools – or Steve’s guidelines for SQL – they just make it run too well.
  30. Install Google Chrome, MS Office, Adobe Reader and any freeware applications I can find on the primary site server
  31. Change the NIC to use DHCP
  32. Install additional Web Site applications within IIS
  33. Verify no PKI exists and then configure all clients to use PKI only.
  34. Take a snapshot/checkpoint every day and once a month revert to a previous snapshot from a week earlier.
  35. Post job openings to all the job search sites insisting the candidate ONLY know Ghost or Acronis and has never touched or even heard of SCCM or MDT, ever.
  36. Configure Azure with O365 and and EMS tenant
  37. Integrate SCCM and EMS
  38. Enroll devices in EMS/Intune
  39. Deploy SCCM clients to the Intune devices
  40. Automatically open support tickets with Microsoft on why the clients stop working in EMS/Intune

That should just about cover it.

SCCM, SQL, DBATools, and Coffee

Warning:  This article is predicated on (A) basic reader familiarity with System Center Configuration Manager and the SQL Server aspects, and (B) nothing better to do with your time.

Caveat/Disclaimer:  As with most of my blog meanderings, I post from the hip.  I fully understand that it exposes my ignorance at times, and that can be painful at times, but adds another avenue for me to learn and grow.

marie-wilson-cooking

I don’t recall exactly when I was turned onto Ola Hallengren, or Steve Thompson, but it’s been a few years, at least.  The same could be said for Kent Agerlund, Johan Arwidmark, Mike Niehaus, and others.  None of whom I’ve yet to meet in person, but maybe some day.  However, that point in time is when my Stevie Wonder approach to SQL “optimization” went from poking at crocodiles with a pair of chopsticks, to saying “A-Ha!  THAT’s how it’s supposed to work!

As a small testament to this, while at Ignite 2016, I waited in line for the SQL Server guy at his booth, like an 8 year old girl at a Justin Bieber autograph signing, just to get a chance to ask a question about how to “automate SQL tasks like maintenance plans, and jobs, etc.”.  The guy looked downward in deep thought, then looked back at me and said “Have you heard of Ola Hallengren?”  I said “Yes!” and he replied, “he’s your best bet right now.

Quite a lot has changed.

For some background, I was working on a small project for a customer at that time focusing on automated build-out of an SCCM site using PowerShell and BoxStarter.  I had a cute little gist script that I could invoke from the PowerShell console on the intended target machine (virtual machine), and it would go to work:

  • Install Windows Server roles and features
  • Install ADK 10
  • Install MDT 2013
  • Install SQL Server 2014
  • Adjust SQL memory allocations (min/max)
  • Install WSUS server role and features
  • Install Configuration Manager
  • Install ConfigMgr Toolkit 2012 R2
  • and so on.

Since it was first posted, it went through about a dozen iterative “improvements” (translation: breaking it and fixing and improving and breaking and fixing, and repeat).

The very first iteration included the base build settings as well, such as naming the computer, assigning a static IPv4 address, DNS servers and gateway, join to an AD domain, etc.  But I decided to pull that part out into a separate gist script.

The main thing about this experiment that consumed the most time for me was:

  1. On-the-fly .INI construction for the SQL automated install
  2. On-the-fly .INI construction for the SCCM install
  3. On-the-fly SQL memory allocation configuration

Aside from the hard-coding of content sources (not included on this list), item 2 drove me nuts because I didn’t realize the “SA expiration” date property was required in the .INI file.  The amount of coffee I consumed in that 12 hour window would change my enamel coloring forever.  Chicks dig scars though, right?  Whatever.

Then came item 3.  I settled on the following chunk of code, which works…

$SQLMemMin = 8192
$SQLMemMax = 8192
...
write-output "info: configuring SQL server memory limits..."
write-output "info: minimum = $SQLMemMin"
write-output "info: maximum = $SQLMemMax"
try {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
  $SQLMemory = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ("(local)")
  $SQLMemory.Configuration.MinServerMemory.ConfigValue = $SQLMemMin
  $SQLMemory.Configuration.MaxServerMemory.ConfigValue = $SQLMemMax
  $SQLMemory.Configuration.Alter()
  write-output "info: SQL memory limits have been configured."
}
catch {
  write-output "error: failed to modify SQL memory limits. Continuing..."
}

But there’s a few problems, or potential problems, with this approach…

  1. It’s ugly (to me anyway)
  2. The min and max values are static
  3. If you change this to use a calculated/derived value (reading WMI values) and use the 80% allocation rule, and the VM has dynamic memory, it goes sideways.

Example:

$mem = $(Get-WmiObject -Class Win32_ComputerSystem).TotalPhysicalMemory
$tmem = [math]::Round($mem/1024/1024,0)
...

I know that option 2 assumes a “bad practice” (dynamic memory), but it happens in the real world and I wanted to “cover all bases” with this lab experiment.  The problem that it causes is that the values returned from a WMI query can fluctuate along with the host memory allocation status, so the 80% value can be way off at times.

Regardless, forget all that blabber about static values and dynamic tragedy.  There’s a better way.  A MUCH better way.  Enter DBATools.  DBATools was the brainchild of Chrissy LeMaire, which is another name to add to any list that has Ola’s name on it. (side note: read Chrissy’s creds, pretty f-ing impressive). There are other routes to this as well, but I’ve found this one to be most user friendly for my needs. (Feel free to post better suggestions below, I welcome feedback!)

Install-Module dbatools
$sqlHost = "cm01.contoso.com"
$sqlmem = Test-DbaMaxMemory -SqlServer $sqlHost
if ($sqlmem.SqlMaxMB -gt $sqlmem.RecommendedMB) {
  Set-DbaMaxMemory -SqlServer $sqlHost -MaxMB $sqlmem.RecommendedMB
}

This is ONLY AN EXAMPLE, and contains an obvious flaw: I’m not injecting an explicit 80% derived value for the -MaxMB parameter.  However, this can be accomplished (assuming dynamic memory is not enabled) as follows…

Install-Module dbatools
$sqlHost = "cm01.contoso.com"
$sqlmem = Test-DbaMaxMemory -SqlServer $sqlHost
$totalMem = $sqlmem.TotalMB
$newMax = $totalMem * 0.8
if ($sqlmem.SqlMaxMB -ne $newMax) {
  Set-DbaMaxMemory -SqlServer $sqlHost -MaxMB $newMax
}

Here’s the code execution results from my lab…

sqlmemory2

You might have surmised that this was executed on a machine which has dynamic memory enabled, which is correct.  The Hyper-V guest VM configuration is questionable…

hyperv_setup1.png

This is one of the reasons I opted for static values in the original script.

Thoughts / Conclusions

Some possible workarounds for this mess would be trying to detect dynamic memory (from within the guest machine) which might be difficult, or insist on a declarative static memory assignment.

Another twist to all of this, and one reason I kind of shelved the whole experiment, was a conversation with other engineers regarding the use of other automation/sequencing tools like PowerShell DSC, Ansible, and Terraform.

The final takeaway of this is to try and revisit any projects/code which are still in use, to apply newer approaches when it makes sense.  If that means shorter code, improved security and performance, more capabilities, greater abstraction/generalization (for reuse), or whatever, it’s good to bring newer ideas to bear on older tools.  In this example, it was just replacing a big chunk of raw .NET reflection code with cleaner and more efficient PowerShell module code.  Backing out 10,000 feet, the entire gist could be replaced with something more efficient.

More Information

DBATools – twitterslackyoutubegithub

Ola Hallengren – web  (Ola doesn’t tweet much, yet)

My Twitter list of super awesometacular increditastical techno-uber genius folks – HERE

Back to my coffee.  I hope you enjoyed reading this!  Please post comments, thoughts, criticisms, stupid jokes, or winning lottery numbers below.  If nothing else, please rate this article using the stars above? – Thank you!