SCCM Collection Queries by Server Role


Rather than spew forth a bunch of sample queries, I’ll just hand you a virtual fishing rod, a case of imaginary beer, and point you to the make-believe boat.  This little procedure came in handy today with a customer I was helping.  I hope it helps you as well…

  • Device Collections
    • Create Device Collection
      • Name: Servers – WDS Servers (example)
        • Limiting collection: (whatever has servers with clients)
        • Use incremental updates for this collection (check)
        • Add Rule > Query-Rule
          • Name: 1 (or whatever you want, I’m lazy)
          • Edit Query Statement:
            • Omit duplicate rows (check)
            • Criteria tab
              • “Select” button (click it)
              • Class = Server Feature
              • Attribute = Name (click OK)
                • Is Equal To (leave as-is)
              • Click the “Value” button
                • Select an appropriate Feature Name
                • Enjoy a cold one!

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 2016.12.29

Bug Fixes

  • Task Sequences report had a query strung out on meth.
  • Task Sequence detail view was on crack, and meth.
  • Task Sequence History was passed out drunk.
  • Client Summary report couldn’t sort it’s way out of a wet paper bag.
  • The CMWT_DB_TableGridFilter function was shooting Drano in an alley but you probably didn’t even know it existed, which is probably why it was feeling so down that it felt the need to escape reality, but Drano was all it could find in a dumpster.

New Features

  • Task Sequence detail view has a “History” and “Detailed” report of execution results.
  • Component Status Summary report was kidnapped in a white van, bound with zip ties, duct tape and snuffed with a rag soaked in something my dog couldn’t keep down.  A red van pulled up behind it, and dropped off a new report with a better haircut that answers questions as “yes, I’m the same guy.  even though I don’t look anything like the other guy, but it’s okay”.
  • Ola Hallengren’s SQL Server Maintenance Solution (as wonderfully demonstrated by Steve Thompson) is now supported for the CommandLog table via the Site Hierarchy page “CM Monitor Commands” (this requires a new line in the _config.txt file – see details below)

Change to _Config.txt

  • If you intend to use the SQL Maintenance Solution (and you should), and would like to use the web report to see the command log details, you will need to copy the line that starts with “DSN_CMDB~” and make two changes to the new line:
    • Rename the new “DSN_CMDB” to “DSN_CMM”
    • Change “Database=CM_xxx” to “Database=CMMonitor”
    • Be careful NOT to modify your existing DSN_CMDB line
    • You may need to recycle the IIS application pool


  • Go here and download
  • New Installations:
    • Follow the Installation Guide (in the Docs subfolder within the ZIP file)
  • Existing Installations / Upgrade:
    • Back up your _config.txt file
    • Extract ZIP into CMWT folder (overwrite files)
    • Restore your _config.txt file
    • Done!


New CMMonitor Command Log view


New Component Status Summary view

Latest CMWT Build Updates


CMWT 2016.12.13.01 includes the following updates:

Thank you to all who have submitted feedback!  Keep it coming! 

  • A new SQL query / report builder
    • Select Form options and Build – or –
    • Paste SQL code from SQL Management Studio
    • Save, Run, Edit, Delete
  • Task Sequence browsing and log history viewing
  • Updated home page (dashboard) with site and component status counts
  • Collection Tools: Members Copy / Move
  • Filtering Updates by Severity, BulletinID, and ArticleID
  • Improved Site Status and Component Status viewing
  • Improved Site Log Browsing and Log Viewing
  • Improved Site Server (Windows Services) status viewing

Still in Development

  • Client Actions
  • Client Tools
  • Collection Actions
  • Collection Tools
  • Expanded Universal Search
  • Expanded Note Attachments


  • Go Here and grab “”

New Installation

  • Extract the contents
  • Read the installation guide (PDF) under “docs” for more


  • Back up your “_config.txt” file
  • Extract the ZIP contents into the CMWT folder
  • Restore your “_config.txt” file
  • Drink up!


  • How much does it cost?  Nothing
  • Do I have a life? No
  • Does this provide some weird twisted kind of therapeutic value to you?  Sadly, yes.

CMWT 2016.12.10 Released

I’ve been busy on CMWT this weekend.  Fixing bugs and bolting on some missing parts, but I have also spent about half the effort improving features.  In addition to the coding itself, I’ve decided to split the source code and download packages under separate Git repositories to keep things cleaner.  As part of this, for the long-term, I will maintain up to three (3) version downloads including the current/latest.  Older packages will be removed as the next new version is posted.



What’s New / Changed

Bug fixes

  • Collections and Collection properties
  • Devices and Device property reports
  • Software reports
  • Reports
  • Site Hierarchy / Status reports
  • CSS mapping
  • ADO functions
  • ADSI/LDAP functions
  • Table column sorting
  • Device Management Tools (for IE only)
  • AD Tools reports (and secure LDAP ADO connections)

Feature Updates

  • Page layout (UI/UX)
    • Grouping of tiles
    • Link Names (labels)
  • Home page
  • Sidebar
    • Help link
  • Search
  • AD Users
  • AD Groups
  • AD Computers
  • AD OU Browsing
  • Site Logs / Log Viewer
  • Operating Systems Deployment
    • Boot Images

New Features

  • Custom Reporting tools (still in development)
    • The Reports landing page is being deprecated in favor of moving the reports to the landing pages which are most relevant (e.g. software reports linked from the software page)
  • Active Directory
    • Group Policy Object reports**
    • AD User – > Computer Logins by Profile mapping

** requires the GPMC to be installed on the CMWT host


  • To use the new Custom Reports you must first create them using the new Report Builder form (“Direct SQL Query” form)
    • You can also run the cmwt_db_sqlreports.sql script to insert sample reports to get started (recommended!)
  • Make sure users (if they are not Administrators in SCCM and the SCCM SQL database) are granted db_datareader rights (e.g. CM_PS1)
  • Make sure users are granted db_datareader and db_datawriter rights on the CMWT database.
  • Make sure the AD Tools user account (specified in the _config.txt file) has permissions to modify user accounts in AD.  Best practice is to delegate by OU in order to isolate sensitive accounts from be accidentally modified.

Upgrade an Existing CMWT installation

  • Make a copy of _config.txt
  • Unzip the package into the CMWT folder
  • Restore the backup copy of _config.txt
  • Refresh the web page (F5)

New Installation

  • Read the Installation Guide included in the ZIP package.  It’s under the “docs” subfolder.


  • Go HERE – Download

SCCM SQL Queries: Find Computers by User Logins and Login Counts


Find computers where a particular user has logged on at least once.  Find them by user account name or by SID.  I know this violates a cardinal rule about avoiding LIKE with double-ended ‘%’ wildcards, but hey.  YOLO!

Find by User Profile + UserName

  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
  dbo.v_R_System.AD_Site_Name0 AS ADSiteName, 
  dbo.v_GS_COMPUTER_SYSTEM.Domain0 AS Domain 
  dbo.v_R_System INNER JOIN
  ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_USER_PROFILE.ResourceID
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE '%johndoe%')

Find Computers by User Profile SID

  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
  dbo.v_R_System.AD_Site_Name0 AS ADSiteName, 
  dbo.v_GS_COMPUTER_SYSTEM.Domain0 AS Domain 
  dbo.v_R_System INNER JOIN
  ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_USER_PROFILE.ResourceID
  (dbo.v_GS_USER_PROFILE.SID0 = 'S-1-5-21-123456789-1234567890-1234567890-1234')

Bonus – List UserNames by Most Computer Logins / Profiles

Find those promiscuous little users that seem to get their busy little fingers (or Remote Desktop connections) smeared on the most computers.  This one dumps a table of UserName, UserSID and count of Computers on which they have a local profile folder.

  dbo.v_R_User.User_Name0 AS UserName, 
  dbo.v_R_User.SID0 AS UserSID, 
  COUNT(dbo.v_GS_USER_PROFILE.ResourceID) AS Computers
  dbo.v_R_User INNER JOIN
  dbo.v_GS_USER_PROFILE ON dbo.v_R_User.SID0 = dbo.v_GS_USER_PROFILE.SID0
  User_Name0, dbo.v_R_User.SID0
  Computers DESC, UserName

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, 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.