6 Things to Avoid when Building an SCCM Site System


These are based on actual, real, true events, which I’ve been asked to help resolve in some capacity over the past three weeks:

  1. Do not let someone create your VM using an unknown template which contains leftover remnants of a previous SCCM site installation, and dozens of unknown changes for which the site admin has no knowledge what happened.
  2. Do not let someone create your VM and join it to an AD domain under an OU with a bunch of linked GPO’s which are undocumented.
  3. Do not let your boss approve another department’s request to take ownership of your SCCM SQL Server instance without prior discussion or them being advised as to what SCCM is.
  4. Do not let another engineer start building the site before you’ve provided him/her with the design document.  Especially when it includes Intune integration and they go ahead and set Intune as it’s own MDM authority, without discussing anything with you in advance.
  5. Do not recommend an SCCM site installation to a customer after a sales person insisted it was the “perfect fit” for their 10 desktop computers, when all they wanted was to manage software updates.
  6. Do not recommend to a customer that they’re fine with allowing their Primary site server VM, running on a Hyper-V failover cluster, to fail over another node, on another cluster, on another continent.

SCCM Query: Devices by IP Gateway


While working on a project involving IP subnet reassignments and SCCM site boundaries, and factoring in the HW inventory scan cycles, with the nature of roaming vs. fixed device types, along with the phase of the moon, current stock market index, the daylight saving time offset, the menstrual cycles of our local male politicians, and my dog’s sleep patterns, I found this to be somewhat useful in tracking down stray devices.

WHERE [IPEnabled0]=1
GROUP BY DefaultIPGateway0

Notes from the Field: Windows Server 2016


So far, I’ve completed quite a few in-place upgrades to System Center Configuration Manager “current branch”.  However, more recently, I’ve stepped into quite a few upgrades to SQL Server 2016 and Windows Server 2016.  Some of those included SCCM hosts, AD Domain Controllers, Hyper-V hosts, and SQL Server hosts. Here’s some quick notes:

  • Hyper-V hosts: shutdown all guests.  If you don’t have backups, make them now or use the Export feature (which can be slow as hell).
  • If you get an error trying to invoke the setup.exe from a mounted ISO, restart the host and try it again.  In fact, prior to any upgrade, shut down all applications cleanly and do a standard shutdown/restart and wait a few minutes before starting the upgrade process.
  • NIC teams have to be removed/broken prior to the upgrade from 2012 or 2012 R2.
  • SCEP clients have to be uninstalled (if installed already).  If you’re using another AV product, shut it off and uninstall it prior to the upgrade.  Then reinstall later if necessary.
  • When upgrading an AD Domain Controller or member server which has RSAT ADDS tools installed, you will need to remove the “Identity Management for UNIX components” role service prior to the upgrade.
  • I recommend transferring FSMO roles to other DC’s while each is being upgraded, but you can skip this if you have a high testosterone level and a fat bank account.
  • If you’re using a vanilla ISO to perform the upgrade, and it’s now January 2017, and you haven’t obtained a patched version, expect 40-60 minutes of updates and several restarts along the way.
  • After the upgrade is done, if you had NIC teams and either continue without them, or recreate them, some Hyper-V guests may display an error when trying to start them which indicates a lack of permissions on the network interface.  To resolve, open the settings, (or use PowerShell), change the NIC association, click Apply, then change it back and click Apply.  The guest should start fine afterwards.
  • SQL Server hosts, double check the service configurations (accounts, start modes, etc.)  Verify them after the upgrade as well.

So far, so good.

CMWT Notes / Updates

Thank you for all the kind words and responses!  I’m really surprised and humbled at the same time.  I realize that there are still some rough edges in the current build, which I’m working on and will post as soon as I can.

I really do want to thank Chris DeCarlo for helping me beat this thing with a baseball bat to see if it could still walk away.  He helped me quickly nail down some problems that needed fixing before it was ready.


I’d like to take a minute to provide some answers and responses to questions and comments I’ve received thus far…

Known Issues

  • The SQL connection error upon starting CMWT is due to a bug in the global.asa file.  There’s a line near the top that shows Const ConfigFile = “F:\CMWT\_config.txt” which should be replaced with ConfigFile = Server.MapPath(“_config.txt”)
  • The CMWT log features linked from Administration, may not show much yet.  The next build turns that feature on, which is explained below.
  • Nested AD domains will require modifying the _config.txt key assignments for LDAP paths.  For example, the default shows dc=<<DOMAIN>>,dc=<<DOMSUFFIX>>, but if your domain is something like sales.contoso.com, then you’ll need to add/insert another dc=<<DOMAIN>>, or “dc=sales,dc=contoso,dc=com”.

Upcoming Features

  • CMWT Event Logging, is intended to capture activity such as enabling or disabling AD user accounts, modifying AD user groups, adding and removing SCCM collection members, and so on.
  • Database Index Fragmentation report will be in the next build.  I also have a defrag option (manual, not scheduled), but that needs more testing.  Right now it’s kind of a shotgun blast and may not be efficient enough for real benefit.  The SQL statement I’m using is:
    “EXEC sp_MSforeachtable @command1=””print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)””;”  The downsides so far are the excessive processing time (when the overall fragmentation aspect is more than 50% above 60%, which I admit is my own fault, but not at all uncommon at customer sites I visit), and the results may not bring the aspect down to 0%, but still improves to around 15%.
  • Collection Tools – which was in a much older version, was removed.  I will be working on putting it back, hopefully soon.

Nature Eats the Stupid. Mankind Promotes Them


Case 1

Customer wants to move entire datacenter into Azure.  All of it.  Nothing “on premises” whatsoever.  Actually, not “wants”, but “insists, emphatically”.  This includes file and print services, DHCP, DNS and WINS, and Active Directory.  When informed about limitations with regards to network infrastructure (DHCP in particular), and shown proof-of-concept for print services, etc. they ask what can be done.  The recommendation is to put some on-prem services to improve responsiveness, provide failover, etc. etc..  “But, we don’t want on-prem anything?   You need to tell us how to make this work!”

Case 2

Customer installs SCCM 2012 R2 on a physical server with undersized resources (memory, CPU, disk space, disk isolation, networking, yada yada yada) and installs SQL Server on a separate machine, where it coexists with about six (6) other instances.  Performance sucks mud through a straw.  We explain to customer the limitations on the SCCM/SQL licensing use-cases, and how it helps to collocate SCCM and SQL on the same host, AND that it can be virtualized for greater flexibility, etc.

Customer response “I know for certain that Microsoft DOES NOT virtualize SCCM!”

Case 3

Customer sets SQL Server file auto-growth to 1 MB on all databases.  Enough said.  But they also have a maintenance plan that runs a DB shrink on all of the databases on the SCCM instance as well.

Case 4

Contractor has to hire subcontractor to handle surge gap in staffing for an SCCM installation.  Customer sets up work area in a conference room.  Customer comes back a few hours later and finds subcontractor asleep with both feet on table, and two more laptops logged into other customer environments.   Subcontractor is shown the door.  However, not before having installed an entire CAS hierarchy using anti-best-practices methods which end up requiring an entire site rebuild months later. (Phil – I know you know who I’m talking about, but I can’t give any other hints)

Case 5

Customer connects a third-party “asset management and help desk” application to SCCM by using a direct SQL connection.  The app is configured to write back to the SCCM site through SQL.  Shit starts imploding on day 1.

Case 6

Customer needs to upgrade site with a remote Secondary site.  Contractor remotes in and does the upgrade.  After all is settled out, everything works fine.  Next day, customer says all indicators have turned red in the console.  Contractor remotes in and finds a ton of errors in site system logs on both ends, as well as Windows event logs on the Secondary.  Most point to domain authentication issues.

  • Contractor,  “What changed since the day before?”
  • Customer, “Nothing at all.”
  • Contractor, “Are you sure?”
  • Customer, long pause “Well, nothing besides the recovery”
  • Contractor, “Recovery?”
  • Customer, “The local tech said the CPU was running high so he reverted the VM to an earlier snapshot to fix it.”
  • Contractor, long pause, “Ummm, ok. Well. Reverted to when?”
  • Customer, “A snapshot from February.”
  • Contractor, “It’s now September.” (this was when it occurred)
  • Customer, “Is that a problem?”
  • Silence followed by a very long inhale and slow exhale “Let’s start over, shall we?” …

PowerShell: Export-CmReports.ps1

Another I glued together today for a lab project.  No error trapping, weeeee!

 Export-CmReports exports SCCM SQL RSP reports to RDL files in
 a specified location

 3-character site code

 NetBios hostname of SCCM RSP host

.PARAMETER ReportFolder
 Name of logical SSRS report folder, or "ALL" to export all folders
 Default is "/" which is not recursive

.PARAMETER OutputFolder
 Path location where RDL files will be exported
 Default is $USERPROFILE\Documents

 Written by: David Stein
 Date Create: 10/19/2016

 Export-CmReports -SiteCode "ABC" -HostName "CM1"
 Export-CmReports -SiteCode "ABC" -HostName "CM1" -ReportFolder "My Custom Reports" -OutputFolder "C:\Temp"
 Export-CmReports -SiteCode "ABC" -HostName "CM1" -ReportFolder "ALL" -OutputFolder "C:\Temp"

function Export-CmReports {
  param (
    [parameter(Mandatory=$True)] [string] $SiteCode,
    [parameter(Mandatory=$True)] [string] $HostName,
    [parameter(Mandatory=$False)] [string] $ReportFolder = "/",
    [parameter(Mandatory=$False)] [string] $OutputFolder = "$($env:USERPROFILE)\Documents"
  $url = "http://$HostName/ReportServer/ReportService2010.asmx?WSDL"
  Write-Host "connecting to SSRS web service..." -ForegroundColor Cyan
  $ssrs = New-WebServiceProxy -Uri $url -UseDefaultCredential -Namespace "ReportingWebService"
  if ($ReportFolder -eq "ALL") {
    $folders = $ssrs.ListChildren("/ConfigMgr_$SiteCode", $False) | ?{$_.TypeName -eq 'Folder'}
  else {
    $folders = $ssrs.ListChildren("/ConfigMgr_$SiteCode", $False) | ?{$_.Name -eq "$ReportFolder"}

  if ($folders.Length -gt 0) {
    foreach ($folder in $folders) {
      $fname = $folder.Name
      $fpath = $folder.Path
      $reports = $ssrs.ListChildren("$fPath" , $False)
      if ($reports.Length -gt 0) {
        Write-Host "Folder: $fName : $($reports.Length) reports" -ForegroundColor Cyan
        $OutPath = "$OutputFolder\$fName"

        if (!(Test-Path $OutPath)) {
          md $OutPath -Force
        foreach ($r in $reports) {
          $reportName = $r.Name 
          if ($r.Hidden -eq $True) {
            Write-Host "skipping hidden report: $reportName" -ForegroundColor Gray
          else {
            Write-Host "reading: $reportName..." -ForegroundColor Green
            $def = $ssrs.GetItemDefinition($r.Path)
            $stream = [System.IO.File]::OpenWrite("$OutPath\$reportName.rdl")
            $stream.Write($def, 0, $def.Length)
            Write-Host "exported: $reportName successfully!" -ForegroundColor Green
      else {
        Write-Host "no reports were found" -ForegroundColor Cyan
    } # foreach
  Write-Host "done!"


Another SCCM SQL Query: SQL Server Hosts

Find Windows Server computers which have some version of SQL Server installed…

  dbo.v_R_System.Name0 AS [Name], 
  dbo.v_R_System.ResourceID AS ResourceID,
  dbo.v_R_System.SMS_Unique_Identifier0 AS [GUID],
  dbo.v_R_System.Resource_Domain_OR_Workgr0 AS Domain,
  dbo.v_R_System.AD_Site_Name0 AS ADSiteName,
  dbo.v_R_System.Client0 AS Client 
FROM dbo.v_R_System 
  ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_R_System.ResourceId 
  INNER JOIN dbo.v_GS_System
  ON dbo.v_GS_SYSTEM.ResourceId = dbo.v_R_System.ResourceId 
  (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE 'Microsoft SQL Server%')
  (dbo.v_GS_SYSTEM.SystemRole0 = 'Server')