System Center, Technology

Support Requests – 2017 Flashback

I was just talking with someone about how “times have changed” just since 2017. Then I found an old email which had a list of cases I was working on around Q1-17 (former employer). Compared to then, 2019 has been much more calm.

  1. ConfigMgr client push account not having permissions on the remote devices.
  2. Over-zealous Antivirus settings getting in the way (McAfee) of ConfigMgr client installations.
  3. Network admins added/changed subnets without telling SCCM admins (site boundary updates)
  4. Using separate accounts in trusted AD forests, rather than a central trusted account, and the passwords were out of sync.
  5. Another team installed a 3rd party help desk product on the SQL host as SCCM uses, and didn’t tell them it hogs most of the available memory and violates the terms of the ConfigMgr/SQL license.
  6. After suggesting the use of an isolated IP subnet and dedicated DP for a central imaging workbench, the server admin team instead added a 2nd NIC to both the SCCM primary site server and a different DP, on different subnets, one without a gateway, and didn’t tell the SCCM anyone else.
  7. IT staff enrolled several Surface Book’s with EMS/Intune, and then removed the Intune client and installed the SCCM client and then opened a support ticket about why the client no longer shows as “managed” in Intune.  Microsoft investigated, explained and closed the request (as they should have).  The customer argued to keep the request open.  I was brought in to help explain why it should be closed (that alone took 2 days).
  8. Primary site server has CrowdStrike, Symantec EP, and Malware Bytes agents installed, all are active, and none have ConfigMgr exclusions. Long day.
  9. Client Push installation has custom settings which set the default MP to one that was removed from the environment years ago.
  10. Network team re-assigned subnets during an office relocation.  No one was notified to update AD sites and subnets or ConfigMgr (site boundaries).
  11. DNS scavenging was turned off, with DHCP lease duration of 3 days and 50% of devices roam around the campus every day or two.

databases, Scripting, System Center, Technology

What Not to Do With ConfigMgr, 1.0.1

[note: this post has been sitting in my drafts folder for over a year, but recent events reminded me to dust it off and post it]

One of my colleagues, the infamous @chadstech, sent a link to our team, to the slide deck from the Channel9 session (MS04) “30 Things you should never do with System Center Configuration Manager” by @orinthomas and @maccaoz. If you haven’t seen (or read) it already, I strongly recommend doing so first.

It’s from 2016, so even though it’s a few years old now, it still holds up very well in mid 2019. However, everyone who’s ever worked with that product knows that the list could become a Netflix series.

This blog post is not going to repeat the above; instead, append the list with some things I still see in a variety of environments today. Things which really should be nipped in the bud, so to speak. Baby steps.

Using a Site Server like a Desktop

Don’t do it. Install the console on your crappy little desktop or laptop and use that. Leave your poor server alone. Avoid logging into servers (in general) unless you REALLY need to perform local tasks, and that’s it. Anything you CAN do remotely, should be done remotely.

If installing/maintaining the ConfigMgr console is your concern: forget that. The days of having to build and deploy console packages are gone. Install it once, and let it update itself when new versions are available. Kind of like Notepad++. Nice and easy.

Why? Because…

  • Using a server as a daily desktop workspace not only drags on resources and performance.
  • It creates a greater security and stability risk to the environment.
  • The more casual you are with your servers, the sloppier you’ll get and eventually you’ll do something you’ll regret

Whatever your excuse has been thus far, stop it.

Anti-Virus Over-Protection

Even in 2019, with so many tools floating about like Symantec, McAfee, Sophos, CrowdStrike, and so on, when I ask if the “exclusions” are configured to support Configuration Manager, I often get a confused look or an embarrassing chuckle. Gah!!! Chalkboard scratch!

There are several lists of things to exclude from “real-time” or “on-demand” scanning, like this one, and this one. Pick one. Failing to do this VERY often leads to breaks in processes like application deployments, software updates deployments, and policy updates.

Also important: with each new release of Configuration Manager, read the release notes and look for new folders, log files, services or processes that may be introduced. Be sure to adjust your exclusions to suit.

Ignoring Group Policy Conflicts

Whatever you’re doing with regards to GPO settings, make damned sure you’re not also doing the same things with Configuration Manager. The two “can” be combined (in rare cases) to address a configuration control requirement, and you can sew two heads on a cow, but that doesn’t mean it’s the best approach.

Pick one, or the other, only. If you have WSUS settings deployed by GPO, and are getting ready to roll out Software Updates Management via Configuration Manager, stop and carefully review what the GPO’s are doing and make adjustments to remove any possible conflicts.

And, for the sake of caffeine: DOCUMENT your settings wherever they live. GPO’s, CI’s or CB’s in ConfigMgr, scheduled tasks, whatever. DOCUMENT THEM! Use the “Comments” or “Description” fields to your advantage. They can be mined and analyzed easily (take a look at PowerShell module GPODOC for example / shameless plug).

One-Site-Fits-All Deployments

I’ve seen places that only use packages, or only use Task Sequences, or only use script wrapping, or only repackage with AdminStudio (or some alternative). That’s like doing every repair job in your house or apartment with a crowbar.

There’s nothing wrong with ANY means of deploying software as long as it’s the most efficient and reliable option for the situation. Just don’t knee-jerk into using one hammer for every nail, screw, and bolt you come across.

Pick the right tool or method for each situation/application. Doing everything “only” one way is ridiculously inefficient and time-wasting.

Sharing SQL Instances

The SQL licensing that comes with a System Center license does not permit hosting third-party products. Not even your own in-house projects, technically speaking. You “can” do it, but you’re not supposed to.

What that means is, when you run into a problem with the SQL Server side of things, and you call Microsoft, and they look at it and see you have added a bunch of unsupported things to it, you’ll likely get the polite scripted response, “Thank you for being a customer. You appear to be running in an unsupported configuration. Unfortunately, we can’t provide assistance unless you are running in a supported configuration. Please address this first and re-open your case, if needed, for us to help? Thank you. Have a nice day. Bye bye now.

And, now, you’re facing an extended duration of what could have been a simple problem (or no problem at all, since your third-party app might be the problem).

Configuration Manager is extremely demanding of it’s SQL resources. Careful tuning and maintenance is VERY VERY VERY often the difference between a smooth-running site, and an absolute piece of shit site. I can’t stress that enough.

Leeching SQL Resources

Some 3rd party products, who I’m advised not to name for various legal reasons, provide “connection” services into the Configuration Manager database (or SMS provider). Attaching things to any system incurs a performance cost.

Before you consider installing a “trial” copy of one of those in your production environment, do it in a test environment first. Benchmark your environment before installing it, then again after. Pay particularly close attention to what controls that product provides over connection tuning (polling frequency, types of batch operations, etc.).

And, for God’s sake (if you’re an atheist, just replace that with whatever cheeseburger or vegan deity you prefer), if you did install some connected product, do some diagnostic checking to see what it’s really doing under the hood.

And just as important: if you let go of the trial (or didn’t renew a purchased license) – UNINSTALL that product and make sure it’s sticky little tentacles are also removed.

Ignoring Backups

Make sure backups are configured and working properly. If you haven’t done a site restore/recovery before, or it’s been a while, try it out in an isolated test environment. Make sure you understand how it works, and how it behaves (duration, results, options, etc. )

Ignoring the Logs

Every single time I get a question from a customer or colleague about some “problem” or “issue” with anything ConfigMgr (or Windows/Office) related, I usually ask “what do the logs show?” I’d say, on average, that around 80% of the time, I get silence or “hold on, I’ll check”.

If you ask me for help with any Microsoft product or technology, the first thing I will do is ask questions. The second thing I will do is look at the appropriate logs (or the Windows Event Logs).

So, when the log says “unable to connect to <insert URL here>” and I read that, and try to connect to same URL and can’t, I will say “Looks like the site isn’t responding. Here’s my invoice for $40,000 and an Amazon gift card”. And then you say “but I could’ve done that for free?!” I will just smile, and hold out my greedy little hand.

Keep in mind that the server and client logs may change with new releases. New features often add new log files to look at.

Check the logs first.

Ignoring AD: Cleanups

Managers: “How accurate is Configuration Manager?”

Answer: “How clean is your environment?”

Managers: (confused look)

If you don’t have a process in place to insure your environment is maintained to remove invalid objects and data, any system that depends on that will also be inaccurate. It’s just a basic law of nature.

Step 1 – Clean up Active Directory. Remove accounts that no longer exist. Move unconfirmed accounts to a designated OU until verified or removed. This process is EASY to automate, by the way.

Step 2 – Adjust ConfigMgr discovery method settings to suit your environment. Don’t poll for changes every hour if things really only change monthly. And don’t poll once a month if things really changes weekly. You get the idea. Just don’t be stupid. Drink more coffee and think it through.

Step 3 – I don’t have a step 3, but the fact that you actually read to this point brings a tear to my eyes. Thank you!

Ignoring AD: Structural Changes

But wait – there’s more! Don’t forget to pay attention to these sneaky little turds:

  • Additions and changes to subnets, but forgetting to update Sites and Services
  • Changes to domain controllers, but not updating DNS, Sites and Services or DHCP
  • Changes to OUs, but forgetting to update GPO links
  • All the above + forgetting to adjust ConfigMgr discovery methods to suit.

Ignoring DNS and DHCP

It’s never DNS!“, is really not that funny, because it’s very often DNS. Or the refusal to admit there might be a problem with DNS. For whatever reason, many admins treat DNS like it’s their child. If you suggest there might be something wrong with it, it’s like a teacher suggesting their child might be a brat, or stupid, or worse: a politician. The other source of weirdness is DHCP and its interaction with DNS.

Take some time to review your environment and see if you should make adjustments to DHCP lease durations, DNS scavenging, and so on. Sometimes a little tweak here and there (with CAREFUL planning) can clean things up and remove a lot of client issues as well.

Check DHCP lease settings and DNS scavenging to make sure they are closely aligned to how often clients move around the environment (physically). This is especially relevant with multi-building campus environments with wi-fi and roaming devices.

Task Sequence Repetition

A few releases back, Microsoft added child Task Sequence features to ConfigMgr. If you’re unaware of this, read on.

Basically, you can insert steps which call other Task Sequences. In Orchestrator or Azure Automation parlance this is very much like Runbooks calling other Runbooks. Why is this important? Because it allows you to refactor your task sequences to make things simpler and easier to manage.

How so?

Let’s say you have a dozen Task Sequences, and many (or all) of them contain identical steps, like bundles of applications, configuration tasks, or driver installations. And each time something needs updating, like a new application version, or a new device driver, you have to edit each Task Sequence where you “recall” it being used. Eventually, you’ll miss one.

That’s how 737 Max planes fall out of the sky.

At the very least, it’s time wasted which could be better spent on other things, like drinking, gambling and shooting guns at things.

Create a new Task Sequence for each redundant step (or group of steps) used in other Task Sequences. Then replace those chunks of goo with a link to the new “child” Task Sequence. Now you can easily update things in one place and be done with it. Easy. Efficient.

Ignoring Staffing

Last, but certainly not least is staffing. Typically, this refers to not having enough of it. In a few cases, it’s too many. If your organization expects you to cover Configuration Manager, and it’s SQL Server aspects, along with clients, deployments, imaging, updates, and configuration policies, AND maintain other systems or processes, it’s time for some discussion, or a new job.

If you are an IT manager, and allow your organization to end up with one person being critical to a critical business operation, that’s foolish. You are one drunk driver away from a massive problem.

An over-burdened employee won’t have time to create or maintain accurate documentation, so forget the crazy idea of finding a quick replacement and zero downtime.

In team situations, it’s important to encourage everyone to do their own learning, rather than depend on the lead “guru” all the time. This is another single point of failure situation you can avoid.

If there’s anyone who knows every single feature, process and quirk within Configuration Manager, I haven’t met them yet. I’ve been on calls with PFE’s and senior support folks and heard them say “Oh, I didn’t know that” at times. It doesn’t make sense to expect all of your knowledge to flow out of one person. Twitter, blogs, user groups, books, video tutorials, and more can help you gain a huge amount of awareness of features and best practices.

That’s all for now. Happy configuring! 🙂

System Center, Technology

7 SCCM Task Sequence Tips

I purposely left out “OSD” in the title, because I see a significant increase in non-OSD tasks being performed with Task Sequences. This includes application deployments, complex configuration sequences, and so on. Whether those could be done more efficiently/effectively using other tools is a topic for another beer-infused, knife-slinging, baseball bat-swinging discussion. Just let me know early-on, so I can sneak out the back door.

Anyhow, this is just a short list of “tips” I find to be useful when it comes to planning, designing, building, testing, deploying and maintaining Task Sequences in a production environment. Why 7? Because it’s supposed to be lucky.


Are you sitting down? Good. This might be a big shock to you, but I am *not* the world’s foremost expert on Task Sequences, or Configuration Manager. And some (maybe all) of these “tips” may be eye-rolling old news to you. But hopefully, some of this will be helpful to you.

Start Simple!

So often, I see someone jump in and start piling everything into a new Task Sequence at once, and THEN trying it out. This can make the troubleshooting process much more painful and time-consuming than it needs to be. Start with what developers call a “scaffold”, and gradually build on that.

I usually start with the primary task at hand: such as “install Windows 10 bare metal“, test that with only the absolute bare minimum steps required to get a successful deployment. Then add the next-most-important steps in layers and continue on.

However you decide to start, just be sure to test each change before adding the next. It might feel tedious and time-wasting, but it can save you 10 times the hassle later on.

Divide and Conquer

Don’t forget that the latest few builds of ConfigMgr (and MDT btw) support “child”, or nested, Task Sequences. In situations where you have multiple Task Sequences which share common steps, or groups of steps, consider pulling those out to a dedicated Task Sequence and link it where needed. Much MUCH easier to maintain when changes are needed.

Some common examples where this has been effective (there are many more I assure you) include Application Installations, Drivers, Conditional blocks of steps (group has a condition, which controls sub-level steps within it, etc.), and setup steps (detection steps with task sequence variable assignments at the very top of the sequence, etc.)

I’m also surprised how many people are not aware that you can open two Task Sequence editors at the same time, side-by-side, and copy/paste between them. No need to re-create things, when you can simply copy them.

Organize and Label

If you are going to have multiple phases for build/test/deploy for your Task Sequences, it may help to do one (or both) of the following:

  • Use console folders to organize them by phase (e.g. Dev, Test, Prod, and so on)
  • Use a consistent naming convention which clearly identifies the state of the Task Sequence (e.g. “… – Prod – 1.2”)

This is especially helpful with team environments where communications aren’t always optimal (multiple locations, language barriers, time zones, etc.)

Establish a policy and communicate it to everyone, then let the process manage itself. For example: “All you drunken idiots, listen up! From now on, only use Task Sequences with ‘Prod’ in the name, unless you know it’s for internal testing only! Any exceptions to this require you eating a can of bug spray.”


Wherever you can use a comment, description, or note, field in anything, you should. This applies to more than ConfigMgr as well. Group Policy Objects and GP settings are rife with not having any explanation about why the setting exists or who created it. Don’t let this mine field creep into your ConfigMgr environment too.

Shameless plug: For help with identifying GPOs and settings (including preferences) which do or don’t have comments, take a look at the GpoDoc PowerShell module, available in the PowerShell Gallery, and wherever crackheads can be found.

The examples below show some common places that seem to be left blank in many (most) organizations I run across.

Other places where documentation (comments) can be helpful are the “Scripts” items, especially the Approval comment box.

Side note: You can query the SQL database view vSMS_Scripts, and check the “Comment” column values to determine what approval comments have been added to each item (or not). Then use the “Approver” column values to identify who to terminate.

Access Control

This is aimed at larger ConfigMgr teams. I’ve seen environments with a dozen “admins” working in the console, all with Full Administrator rights. If you can’t reign that wild-west show in a bit, at least sit down and agree who will maintain Task Sequences. Everyone else should stay out of them!

This is especially important if the team is not co-located. One customer I know was going through a merger (M&A) and, apparently, one group in another country, didn’t like some of the steps in their Windows 10 task sequence, so they deleted the steps. No notifications were sent. It was discovered when the first group started hearing about things missing from newly-imaged devices.

In that case, the things needed were (A) better communications between the two groups, and (B) proper security controls. After a few meetings it was agreed that the steps in question would get some condition tests to control where and when they were enabled.

Make Backups!!!!

Holy cow, do I see a lot of environments where the Backup site maintenance task isn’t enabled. That’s like walking into a biker bar wearing a “Bikers are all sissies!” t-shirt. You’re just asking for trouble.

Besides a (highly recommended) site backup, however, it often pays dividends to make what I call “tactical backups”. This includes such SUPER-BASIC things as:

  • Make a copy of your production task sequences (in the console) – This is often crucial for reverting a bunch of changes that somehow jacks-up your task sequence and you could spend hours/days figuring out which change caused it. Having a copy makes it really easy (and fast) to recover and avoid lengthy impact to production
  • Export your product task sequences – Whether this is part of a change management process (vaulting, etc.) or just as a CYA step, it can also make it easy to recover a broken Task Sequence quickly.

Either of these are usually much less painful than pulling from a site backup.

As a double-added precaution, I highly/strongly recommend that anytime you intend to make a change to a production task sequence, that you make a copy of it first. Then if your edits don’t work, instead of spending hours troubleshooting why a revert attempt isn’t actually reverting, you can *really* revert back to a working version.

Don’t Overdo It

One finally piece of advice is this: Just because you get comfortable using a particular hammer, don’t let this fool you into thinking everything is a nail. Task Sequences are great, and often incredibly useful, but they’re not always the optimal solution to ever challenge. Sometimes it’s best to stick with a very basic approach, like a Package, Application, or even a Script.

I’ve worked with customers who prefer to do *everything* via a Task Sequence. Even when it was obvious that it wasn’t necessary. The reason given was that it was what they were most familiar with at the time. They have since relaxed that default a bit, and saved themselves quite a bit of time. That said, Task Sequences are nice and should always be on your short list of options to solve a deployment need.


I hope this was helpful. If not, you can also print this out, and use it as a toilet bombing target. Just be sure to load up on a good Mexican lunch before you do. Cheers!

Personal, Projects, Scripting, System Center, windows

And now for another stupid pet project

First, there was project number one. I called it “WWA”, which was a clever short name for “Windows Web Admin”. Even though I kept hearing it sounded like a name for a wrestling tournament.  Anyhow, it fell over and sank into a swamp.

Then there was project number two, or “AppAdmin”, which almost fell over and sank, but it was built inside a big shipyard, and they don’t let things sink there, so it floated for a while (I’m told it’s still afloat somehow).

Then, there was project number three, but I can’t state it’s name for legal reasons, or because I promised it might result in me delivering a flaming box of dog poo to a certain someone’s porch, after they ruined that project just as it was maturing, but that’s for another time and place.

Then there was project four, but I can’t talk about that one either, so I’ll skip to project five, CMWT.  But nobody cares about that one, so number six, was putting a hand-rubbed wax finish on someone else’s PowerShell script, and tossing it up on GitHub and PowerShell Gallery, along with projects seven, eight and nine.  And I’m surprised I still remember how to spell the number 8.  So anyhow…

Announcing SkatterTools

(imagine Morgan Freeman narrating from here on)

What is it?

Skatterbrainz Tools.  A really clever name.

It’s a portable web console app thing, for viewing and modifying things in your Active Directory and Configuration Manager environments, from the comfort of your beer-stained laptop.  Think of it like CMWT if it were (A) trying to copy the concept from Microsoft Windows Admin Center, and (B) didn’t require using a separate “server” or anything special**.  Yes, those are double-asterisks.  That means there’s some hidden footnote down below, but don’t look yet, I have to finish boring the shit out of you with this part first.

Why is it?

Because I needed a break from other things, like family matters during the holidays, a dog that loves chewing on furniture, and a 20 year old cat that wanders the house at 3am making really weird sounds.  And I just wanted to see if it was possible to…

  • Build a 100% web console UX to interface with AD and ConfigMgr using PowerShell
  • Not have to touch IIS or any web hosting mess
  • Make it customize-able, free, and open-source
  • Make it through the holidays once again

Where is it?

  • Like a lot of my stuff, it’s up on GitHub

What can it do?

  • View and cross-link:
    • AD users, computers, groups, sites, sitelinks, domain controllers, OUs
    • ConfigMgr users, devices, collections, applications, packages, boot images, task sequences, updates, and scripts
    • ConfigMgr site status, queries, discovery methods, certificates, Forest publishing, boundary groups and boundaries
    • Software inventory, software files
  • Manage:
    • Add/remove AD group members
    • View computers by AD user profile paths
    • Add/remove ConfigMgr collection members **
    • Those damned double-asterisks again, hmm.

Installation and Setup

  • Download PoSH Server here and install it (don’t worry, I checked it and it seems safe, you can trust me, I worked for the government once, sort of)
  • Download the GitHub repo (big green button – top right – zip option)
  • Extract the “poshserver” folder from the GitHub download into a local path like C:\ThisIsTheDumbestShitEver
  • Open the “config.txt” file and edit the settings to suit your needs
  • It is now ready to blow your mind, almost

Starting it Up

  • Add some gasoline and finely-crushed road flares, oh, wait, wrong stuff…
  • Create a desktop shortcut named “Start SkatterTools”…
Target: powershell.exe Start-PoshServer -HomeDirectory "c:\ThisIsTheDumbestShitEver" -CustomConfig "c:\ThisIsTheDumbestShitEver\sktools.ps1"
  • Create another desktop shortcut, named “Open SkatterTools” or “Coolest Shit Ever!”…
Target: http://localhost:8080/
  • Right-click the first shortcut, select “Run as Administrator”, and wait for it to open and say something like this…
  • Double-click the second shortcut and have your Kleenex box nearby
  • Click on one of the sidebar headings and watch the slick CSS stylings ooze all over your eyeballs and onto the floor.  Compliments of some sample code I found on W3Schools.  What a great site.

If you need to shut it down, just close the browser and close the PowerShell console.  There’s instructions on the PoSH Server site for how to configure it like a service, so it runs as a background job. You don’t have to do that though.

Is there any official support?

  • Are you kidding?
  • You can submit bug reports and enhancement requests using the “Issues” link on the GitHub repo.
  • Work comes first.  I have to keep my customers happy and my bills paid
  • I’m still adding things to it frequently, but work may cause some delays getting around to it
  • You can submit your own changes via GitHub (pull requests, etc.) or just submit Issues if you prefer

Is there a roadmap?  Where is it going next?

  • Real (stupid) men don’t use maps!  The journey is the dream, man.
  • Where are any of us really going?  You ever ask that question?
  • Don’t ask that question, it’s depressing.  Enjoy the now.
  • Seriously, yes, I have a metric butt-ton of things I plan to add or improve

Double Asterisk-o-rama

  • Double-asterisks denote two things here:
    • Features are not yet complete.  Things will change.  Oceans dry up. Mountains wear down. Regimes are toppled.  Keith Richards is forever.
    • This is free stuff, and it comes without any strings attached.  No warranties, or guarantees.  No promises (other than it might possibly entertain you if you’re bored), and poor you gets to assume any and all liability, risk and responsibility for anything bad if you use it improperly or in a production environment of some kind, or any environment where alleged (love that word) damages may have occurred or been coerced by tertiary incidental hereinafters forthwith and notwithstanding, that are void where prohibited, taxed or regulated. Batteries not included.
    • Whatever that means


databases, Scripting, System Center, Technology

Silly Tech Stuff – ADO vs WMI in the Cage of Performance Death

I’ve been in a lot of discussions over the years about different approaches to querying information from Configuration Manager, and why I personally prefer SQL over the SMSProvider (WMI), with or without the ConfigurationManager PowerShell module. Then someone asked me recently “How much slower is WMI than SQL? Mr. Smarty Pants!” So, I put down my tube of model glue, the duct tape and my rubber ducky and did some testing.  And for the record, I’m not wearing any pants right now.

DISCLAIMER:  This stupid stunt was performed by a certifiable idiot in a idiot-proof testing environment.  Never perform tests like this in a production environment.  It could result in you being fired, or worse: make you an idiot.  (pssst: Please do not steal my logo graphic.  That’s the one I created with PowerPoint, a few beers and some egg rolls one night, for the CMHealthCheck module, up on the PowerShell Gallery).

I promise, when you’re done reading this, you will not only feel dumber, but regret the precious life moments you missed as a result.  But, someone has to keep productivity down to an acceptable level, and it might as well be me.

The Ingredients

Scenario: Let’s query information about a specific Device within the Configuration Manager hierarchy database.  In this case, to keep the comparison as close to a level playing field as possible, I’m using the following data source endpoints:

And the query for each is unconstrained, meaning it’s “give me everything” rather than specific fields/columns within the logical table structures.  For background, the platform environment is as follows:

  • Configuration Manager 1806 with hotfix 2
  • Windows Server 2016 (latest cumulative updates)
  • SQL Server 2017 CU4
  • PowerShell 5.1

Function Code – Test-WMI

function Test-WMI {
    param (
        [string] $ComputerName = 'W10-001',
        [string] $SiteServer = 'cm02.contoso.local',
        [string] $SiteCode = 'P01'
    $oldLoc = Get-Location
    Set-Location "$SiteCode`:"
    try {
        $Computer = Get-CMDevice -Name $ComputerName
        $WmiFilter = "ResourceID = $($Computer.ResourceId)"
        $WmiNS = "root\SMS\site_$($SiteCode)"
        $result = Get-WmiObject -ComputerName $SiteServer -Namespace $WmiNS -Filter $WmiFilter -Class SMS_CombinedDeviceResources
    catch {
        Write-Error $Error[0].Exception.Message
    finally {
        Set-Location $oldLoc

Function Code – Test-ADO

function Test-ADO {
    param (
        [string] $ComputerName = 'W10-001',
        [string] $SiteServer = 'cm02.contoso.local',
        [string] $SiteCode = 'P01'
    $query = "SELECT * FROM vSMS_CombinedDeviceResources WHERE Name = '$ComputerName'"
    $conn = .\Get-CMAdoConnection.ps1 -SQLServerName $SiteServer -DatabaseName "CM_$SiteCode"
    .\Get-CMSQLQueryData.ps1 -Query $query -AdoConnection $conn -SiteCode $SiteCode


It is important for me to call out a few obvious oddities within the code snippets above, and why they matter.

  • The scripts which are dot-sourced (Get-CMAdoConnection.ps1 and Get-CMSQLQueryData.ps1), are being evaluated each time the function is called, rather than being pre-loaded outside of the function.
  • The ADO connection is not opened before/after the iteration test, but for each execution cycle (very inefficient, and just downright mean)
  • The WMI connection is also executed per iteration implicitly by Get-WmiObject
  • Additional baggage is introduced by the Get-Location / Set-Location mess in Test-WMI.ps1 and with Import-CMModule and the two dot-sourced function invocations in Test-ADO.ps1.   Why make it “right” when you can have it “right now”? 🙂


To compare these two, I chose a simple iteration test against to see how they performed against the clock.  Good old, cranky Mr. Time.  For this I decided to run 100 iterations each and wrap them with Measure-Command…

Measure-Command -Expression { for ($i=0;$i -lt 100;$i++) { Test-WMI } }
Measure-Command -Expression { for ($i=0;$i -lt 100;$i++) { Test-ADO } }


The average results look like this…

Notice the TotalSeconds for each (31.3502006 vs 1.3747373).  Anyhow, what I didn’t expect (and maybe you didn’t either) is that the delta is not linear.  At least not with my tests.  The delta a 10 iterations was around 2:1, but at 100 iterations it’s like 30:1.  The difference appears to increase with the number of successive iterations.  I suspect this is due to overhead incurred with WMI, vs how SQL and ADO work together like a lobbyist and a Congressman.

Warning: The Test-WMI code above may cause the CPU and memory to spike for about 90% of the total execution time, so if you run this while something else is going on (SUP operations, SQL jobs, etc.) you may cause the Earth to melt and fall over.

Anyhow, this is just the casual observation of an ordinary idiot.  Feel free to disagree, call BS on it, laugh hysterically, or send Amazon gift cards.  If you read this far, that’s all that matters.  I appreciate your time and consideration. 🙂



Scripting, System Center, Technology, windows

A Cheap Extensible PowerShell Pipeline for ConfigMgr Queryburgers and a Side of Fries

I’ve been knocked out all day on cold medicine and just woke up.  So, to be honest, I have no idea what year it is.  In fact, Configuration Manager and SQL Server might be long gone.  Microsoft may have been acquired by Walmart, and Kanye West is POTUS.  Who knows.  Anyhow…

Many of the ConfigMgr projects I have worked on over the last few years, I find customers trying to build processes off of information pulled from Configuration Manager.  Most often it’s something like:

  • Execute X on all machines which have Y installed
  • Notify <GROUP> for all machines which have condition Z = True

…and so on.  And no, “X”, “Y” and “Z” are not real things, just variables to replace with real things.  Kind of like how politicians are variables that get replaced with money.

In many cases, this is done in a silo.  Meaning – it’s built as a standalone script.  And then another is built separately for a different purpose, and so on.  But, in many cases, there’s an overlap in the area where data is pulled from Configuration Manager on which to base the scope of the operation or process.  Rather than “hard code” this part, I’ve been using a somewhat “open” approach that returns data from a query and passes it on via the PowerShell pipeline.  This makes it fit nicely into a tool model (credit to Don Jones), and thereby: reusable.

Some common scenarios this needs to adapt to:

  • No guarantee that the ConfigMgr admin console is installed where the script is executed, and therefore, no guarantee of a local .psd1 module to load.
  • No guarantee of SCCM admin rights, via the WMI/SMS provider channel, but….
  • Having SQL database read access (as a minimum)
  • At least PowerShell 3.0 (prefer 5.x or later)
  • Doesn’t matter how it’s invoked (Task Scheduler, SQL Job, Azure Automation, Jenkins, some kid on a bicycle, a Bird scooter, etc.)

I prefer they follow Microsoft guidelines with regards to SQL using Windows authentication for two (2) reasons:  First, it’s compliant with Microsoft recommendations, and Second: It complies with Microsoft guidelines with regards to SQL using Windows authentication for Configuration Manager.

The moving parts consist of:

  • A (PowerShell) script
  • One or more SQL query files (**)
  • An AD user account with read access to the CM_XXX database
  • Coffee (Wine will do)

The general process:

  1. Something kicks it off (manual invocation, scheduled job, event trigger, etc.)
  2. Script imports the desired query from file (**)
  3. Script executes query against CM_XXX database
  4. Results (dataset) returned to script
  5. Results output to PowerShell (pipeline)

(** doesn’t matter how you prefer to store the SQL statement content. I chose files because they’re the simplest and most portable form, and they’re easy to build and export from SSMS)

For those who prefer a visual representation…

Yeah, I know, 5 and 6 could be one thing but whatever.  And coffee is applied between steps 1 and 5.  Okay, so what does this look like?

#requires -version 3.0
This is for sample purposes only.  Actual horrific mess is posted on GitHub here.
Name: Get-CMSqlQueryData.ps1
Real, 100% gluten-free documentation headings are provided in the actual script on GitHub

param (
  [parameter(Mandatory=$False, HelpMessage="SQL Server ADO Connection Object")]
  [parameter(Mandatory=$True, HelpMessage="SQL Query Statement")]
    [string] $Query,
  [parameter(Mandatory=$False, HelpMessage="ConfigMgr SQL Server Host Name")]
    [string] $SQLServerName,
  [parameter(Mandatory=$True, HelpMessage="ConfigMgr Site Code")]
    [string] $SiteCode
$DatabaseName = "CM_$SiteCode"
if (!$AdoConnection) {
  Write-Verbose "opening new connection"
  $AdoConnection = .\Get-CMAdoConnection.ps1 -SQLServerName $SQLServerName -DatabaseName $DatabaseName
  if (!$AdoConnection) {
    Write-Warning "failed to open SQL connection!"
$cmd = New-Object System.Data.SqlClient.SqlCommand($Query,$AdoConnection)
$cmd.CommandTimeout = $QueryTimeout
$ds = New-Object System.Data.DataSet
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
if ($IsOpen) { 
  Write-Verbose "closing connection"
$rows = $($ds.Tables).Rows.Count
Write-Output $($ds.Tables).Rows

The trainwreck above is available on my GitHub trainwreck site here.  The Get-CMAdoConnection.ps1 script referenced above, is also available on my tragic GitHub site here.

A sample query (cm-all-systems.sql):

  v_R_System.Name0 AS ComputerName, 
  v_R_System.AD_Site_Name0 AS ADSite, 
  v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
  v_GS_OPERATING_SYSTEM.BuildNumber0 AS OSBuild, 
    v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID 
    v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID 
    vWorkstationStatus ON 
    v_R_System.ResourceID = vWorkstationStatus.ResourceID

The reason for the optional -AdoConnection parameter is that it allows some control and flexibility around how/when connections are opened against the SQL database.  When running a batch of queries, it’s typically best to open one connection, execute the (multiple) queries, and close the connection at the end, rather than opening an explicit connection for each query.  However, if you only need to run a single query, I didn’t want the user (you) to have to think about an explicit connection (and subsequent connection-close) around the process, so it’s implicit.  See how considerate I can be? Like omg.

That said, let’s see how this looks in action.

For this example, I will assume there’s another script which will be invoked with the results of a query against ConfigMgr (e.g. “Do-Something.ps1”).  In this case, I want to isolate all ConfigMgr devices which are found to be in the Active Directory site named “Seattle”, and send those to a script to do something with their names, hence the genius name: Do-Something.ps1.

Example 1 – Single query

$query = Get-Content -Path "x:\stuff\queries\cm-all-systems.sql"
$result = .\Get-CMSqlQueryData.ps1 -SQLServerName "" -SiteCode "P01" -Query $query |
  Where-Object {$_.ADSite -eq 'Seattle'} | Sort-Object ComputerName | 
    Select-Object -ExpandProperty ComputerName
if ($result.Count -gt 0) { .\Do-Something.ps1 -ComputerName $result }

In this example, I don’t use the -AdoConnection parameter, so the Get-CMSqlQueryData.ps1 script explicitly opens a new connection by calling out to Get-CMAdoConnection.ps1, and then closes the connection at the end.  The last line simply checks if any rows were returned and then passes them to the Do-Something.ps1 script.

Example 2 – Batch queries

$SqlHost = ""
$SiteCode = "P01"
$DBname = "CM_$SiteCode"
$ReportPath = "y:\reports"
$queryFiles = Get-ChildItem -path "x:\stuff\queries" -Filter "*.sql"

if ($queryFiles.Count -gt 0) {
  # open a database connection
  $conn = .\Get-AdoConnection -SQLServerName $SqlHost -DatabaseName $DBName
  # iterate the query files and run each query in a loop
  foreach ($qfile in $queryFiles) {
    # import the query statement and define the output .CSV file name
    $query = Get-Content -Path $($qfile.FullName)
    $csvFile = Join-Path -Path $ReportPath -ChildPath "$($qfile.BaseName).csv"
    # run the query and dump it into the .CSV file
    .\Get-CMSqlQueryData.ps1 -AdoConnection $conn -SQLServerName $ServerName -SiteCode $SiteCode -Query $query | 
      Export-Csv -Path $csvFile -NoTypeInformation
  # close the database connection
Write-Host "like, omg! I can't believe I just did all that amazing stuff.  And it must have been amazing because YOU did it!" -ForegroundColor Green

As you can see, the second example gets all of the query files in a given folder path, then opens a SQL connection and iterates the queries and outputs each to its own .CSV file, and then closes the connection.  You can also pass in an explicit list of query filenames, rather than churning through an entire folder.

You could (and probably should) wrap the internals of the foreach() block inside of a try/catch/finally envelop, to insure $conn.Close() gets called if one of the iterations chokes to death on an egg roll or something.  But hopefully this is easy to understand.


So, this let’s me get data from Configuration Manager, from any computer on the network which has PowerShell 3.0 or later, whether or not it has the ConfigMgr admin console installed, and I can post-process the results however I want.  In addition, I don’t have to make any PowerShell code changes in order to add new queries to the library.  I also do not use an explicit username and password, since my SQL Server instance is configured for Windows authentication only.

Thank you for reading!  Please post comments or questions?  Let me know someone is still reading this stuff.  If you read to this point and you’re the first to tweet me the phrase “a correction to a bug in my code example”, you MIGHT win an Amazon gift card.  Just sayin. 🙂

Scripting, System Center, Technology

PowerShell Toys and Tinkering with ConfigMgr

So… I needed a “quick” (easy/lazy) way to pull some common numbers from (almost) any ConfigMgr site, regardless of whether they’re on 2012 R2 up to 1809. It also has to work whether or not they have a RSP role available. In some cases, the SQL platform is locked up under DBA minefields, so SSMS isn’t always a given either.  All I need is “read” access to the CM database, where it resides.

What are these “common numbers” of which I speak?  Some examples…

  • Office 365 ProPlus installations
  • OneDrive sync client versions
  • For each, I needed 2 or 3 distinct variations, such as “Detailed”, “Total Counts” and “By Version”, etc.

The PowerShell script is pretty basic, so try to swallow your drink before you click on the download link (below).

It requires a sub-folder named “queries”, which you dump your .sql files into, like the samples shown below.  But you can put the folder anywhere and use the -QPath parameter to point to the correct location.  It reads in all .sql files and displays them in a gridview to select which to run.

The output options are “Grid” (default), “CSV” and “Pipeline”.

  • Grid – is the default, and just dumps the results into a Gridview panel
  • CSV – dumps the results to a CSV file with the same name as the selected SQL query (first gridview)
  • Pipeline – is the most powerful and sexy of the three amigos.  You can use this to post-process however you like, and is intended for use with the “detailed” flavors of query results.

I chose to put the .SQL files in their own place to allow easier updating, improving, replacing, adding to, etc.  So, the examples I linked above (and below) are only examples.  You may want to use your own, or add to the mix, etc.

If it makes sense, I may make a PS module out of it and post it to PS Gallery. I’ll wait to see if there’s any interest/need for that first.


.\Run-CmCustomQuery.ps1 -Verbose
.\Run-CmCustomQuery.ps1 -ServerName "cm1.fabrikam.local" -SiteCode "PS1"
.\Run-CmCustomQuery.ps1 -Output Pipeline | ?{$_.Installs -gt 50}
.\Run-CmCustomQuery.ps1 -Output Csv


Don’t be bashful!  Let me know if you find this useful or complete garbage.