Automating User/Computer SQL Based Collection Membership with Powershell

, , , , , ,

Here is yet another “hyper specific to my job” kind of scripts that I hope will make everyone else’s life a little easier.   To start off, I am not a fan of Direct Memberships with collections.  Sometimes, you have to remove an object from the CM database, and when you do, all of those direct memberships are lost.  In addition, a well named SQL query based membership can give you additional information about its members, such as a date stamped name when the query is created.     This also ties in with the second function in this script, which is to turn a randomly provided list of names (such as a list a boss would provide containing a mix of naming formats, email addresses, and actual usernames) into usernames that SQL can understand and query.

Our first function is called AddQueryRule and does exactly what the name suggests.  You provide it with the name of a collection, what type of collection it is, and a file containing a line-delineated list of usernames. The function then creates a query rule for your collection with a date and time stamped name so you can easily go back and see when someone was added to a collection (very useful when doing pilot roll-outs of software packages).  In the case of this function, assume that every username is provided as Domain\Username.  The next function will get us that list of correctly formatted usernames, so don’t worry if your boss just sent you the least standardized list of names known to IT folk everywhere.

Start to finish, what this does is first generate the name of the query so we get our date stamp. Nothing special here. To ensure our script doesn’t accidentally create an array instead of a single, massive string, we’re going to create a string variable containing empty space for now. Next, we’re going to do a basic ForEach loop and do some basic string parsing to surround each name with quotes, double up on the \ (which SQL views as an escape character), and stick a comma on the end of each name. Once the string is constructed, it adds it as a CollectionQueryMembershipRule to your desired collection, and you’re done.

The second function, TurnListofNamesIntoUsernames, is what does the heavy lifting of turning names into usable data.  You could definitely turn the input and output files into arguments passed to the function, but since I only ever used the same two text files and pasted in names as they came in, I just left them hard-coded.   The IF statements are there to check for different formatting, and they’re largely self-explanatory.  Are there “< >” marks: it’s probably copied from an email header.  Does it have an “@” sign: it’s probably an email address.  A “,” in between two words: probably Lastname,Firstname.   Once it has enough information to search, it hands things over to a Get-ADUser query, filters through the results, weeds out our admin account naming scheme (yours will be different, so change that filter according to your needs), and outputs those names to the text file.   I have it give me a count of how many users it found compared to how many were in the list of names so I get a quick look of what may have been missed, and every time it can’t find something, it will let you know so you can check for nicknames (Jake instead of Jacob, etc) and typos.

Hopefully, this helps some of you with automating a largely unpleasant part of collection management. Thanks for reading!

Function AddQueryRule ($CollectionName, $ListOfNames, [switch]$deviceCollection,[switch]$userCollection)
    $RuleName = "$(get-date -Format MM-dd-yy_hh:mm:ss)-Name-Query"
    [String]$namelist = "" 
    Get-Content $ListOfNames | % { $namelist += "`"$($_.replace("\","\\"))`","}
    $namelist = $namelist.TrimEnd(",",1)
    if ($userCollection) 
        [String]$queryString = "select SMS_R_USER.ResourceID,SMS_R_USER.ResourceType,SMS_R_USER.Name,SMS_R_USER.UniqueUserName,SMS_R_USER.WindowsNTDomain from SMS_R_User where SMS_R_User.UniqueUserName in ($namelist)"
        Add-CMUserCollectionQueryMembershipRule -CollectionName $CollectionName -RuleName $RuleName -QueryExpression $queryString
    if ($deviceCollection)
        [String]$queryString = "select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name from SMS_R_System where SMS_R_System.Name in ($namelist)"
        Add-CMDeviceCollectionQueryMembershipRule -CollectionName $CollectionName -RuleName $RuleName -QueryExpression $queryString

function TurnListofNamesIntoUsernames () {
    import-module ActiveDirectory
    $UsernameOutputLocation = "Microsoft.PowerShell.Core\FileSystem::\\fileServer\neo1piv014\ListofUN.txt"
    $InputNameLocation = "Microsoft.PowerShell.Core\FileSystem::\\fileServer\neo1piv014\ListofNames.txt"
    Clear-Content $UsernameOutputLocation
    write-host -ForegroundColor Green "Starting list size: " (get-content $InputNameLocation).count
    cat $InputNameLocation | % {
        $rawInput = $_
        $users = @()  # set our user array as well as our other variables back to clean slates since they aren't all used in every loop iteration and it can make error messages get weird. 
        $firstname = ""
        $lastname = ""
        $email = ""
        $NullLine = $false
        if ($_ -like "*<*>") # did they copy the name from an email header with the name and department intact? 
            $email =  $_.split("<")[1].replace(">","")
            $users += (get-aduser -filter {emailaddress -eq $email} -Properties Emailaddress)
        elseif ($_ -like "*@*") # Is it just an email address? 
            $users += (get-aduser -filter {emailaddress -eq $_} -Properties Emailaddress)
        elseif ($_ -like "*, *")  # or did they go lastname, firstname? 
            $firstname = $_.split(",")[1].trimstart(" ").trimend(" ")  # we have to do this trimming from the start and end instead of using the "Trim()" command in case there's an intended space in the name. 
            $lastname = $_.split(",")[0].trimstart(" ")
            $users += (get-aduser -filter {givenname -eq $firstname -and surname -eq $lastname})
        elseif ($_ -eq "") # did they leave a blank line?
            $NullLine = $true
        else  # or did they perhaps go Firstname Lastname? This one is the hardest because it doesn't allow for names with spaces in them. 
            $firstname = $_.split(" ")[0].trimstart(" ")
            $lastname = $_.Split(" ")[1].trimend(" ")
            $users += (get-aduser -filter {givenname -eq $firstname -and surname -eq $lastname})
        # now we need to filter out the admin accounts. 
        $users = ($users | ? {$_.Samaccountname -notlike "*d" -and $_.Samaccountname -notlike "*m" -and $_.Samaccountname -notlike "*z" -and $_.Samaccountname -notlike "*p" -and $_.Samaccountname -notlike "*t"-and $_.Samaccountname -notlike "*l"})
        if ($users.count -gt 1) # this just spots where multiple people have the same first and last name and warns you that it added everyone with matching first and last names to the list. 
            $users | % {
            write-host -ForegroundColor Yellow $_.Name $_.Samaccountname
            $user = $_
            "DIR\"+$user.Samaccountname | out-file $UsernameOutputLocation -Append -Force  # stick that DIR\ on there for SCCM 
        elseif ($users -eq $NULL) { # this lets you know that it didn't find anyone matching that name combo. Usually, you need to check for nicknames
            if (!$NullLine) {Write-Host -ForegroundColor Red "$rawinput not found."} # we filter out the null lines here because we don't really care about those.  
        else {
            "DIR\"+$users.Samaccountname | out-file $UsernameOutputLocation -Append -Force
    write-host -ForegroundColor Green "Users successfully discovered: " (get-content $UsernameOutputLocation).count

SQL Server Product and Version Reporting with SCCM

, , , , , , , , , ,

SQL Server Product and Version Reporting with SCCM

In this artcile, we will cover SQL Server Product and Version Reporting with SCCM. SQL licensing is always a pain but this report should make it a little easier…

Report release history

Previous report version

Also published on my blog

This is the second iteration of my SQL version report. When I look back on my previous work I always cringe and this was no exception. A while back, I received a request to add the SQL key to the report. So I began examining the old code. Horrified by the things that I found laying dormant there, I scrapped everything and started anew.

The report is brand new with a lot more info, smaller database footprint, and much better coding.


  • Do not modify or revemove the previous version until you verify that this version has all the data you need!
  • Use a test environment for validation!
  • Back-up your configuration.mof file before any changes!
  • Test the configuration.mof using mofcomp.exe on a test machine first!

This version is compatible with the previous version, they can live side by side.

Hardware inventory extension needs to be done on the top of your hierarchy.



  • Test environment
  • Downloads (Right click →Download linked file)
# HWI Extension
HWI EXT SQL Server Products.mof
# HWI Definitions
HWI DEF SQL Server Products.mof
# SSRS Report
SW SQL Server Products.rdl
  • SQL Stored Procedure →‘Create the SQL Stored Procedure’ section.

HWI Extension

The extension needs to be added to the configuration.mof file in \Inboxes\clifiles.src\hinv\

  • Look for the section below at the end of the configuration.mof file. The extension needs to be added between the “Added extensions start/end” headers.
  • Uncomment the “Old SQL extension cleanup” section to remove the old extension classes from the clients repository if needed.
  • Use a test environment for validation as described in the ‘Test and Validation’ header after the Installation section.
// Added extensions start 
// Added extensions end 

Always use a test environment before any changes in production!

Never create any extensions outside of the “Added extensions start/end” headers.
Try to have consistent formatting inside these headers.
Never modify anything outside these headers.
Watch for other previous extensions. And use clear delimitation between them.

  • Apply changes in production
## Compile file on the CAS/PSS
Compiling the configuration.mof file in the hinv folder on the CAS/PSS, will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.
mofcomp.exe \Inboxes\clifiles.src\hinv\Configuration.mof
SQL Server Product and Version Reporting with SCCM

Implement HWI extension in production

HWI Definitions

You need to add the new class definitions to the Default Client Settings

  • Import definitions.
SQL Server Product and Version Reporting with SCCM

Click on Import and select the HWI DEF SQL Server Products.mof file

SQL Server Product and Version Reporting with SCCM

Review the classes and click on Import.

SQL Server Product and Version Reporting with SCCM

Make sure the new extension classes are enabled and click OK.

DO NOT DELETE the old extension definitions if you still want to use the old report!

Test and Validation


Use mofcomp.exe to check. If configuration.mof was correctly modified, and implement the changes.

## Check syntax
mofcomp.exe -check \Configuration.mof
## Compile file 
Compiling the configuration.mof file in the hinv folder on the CAS/PSS. Will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.
mofcomp.exe \Configuration.mof
SQL Server Product and Version Reporting with SCCM

Compling the configuration.mof is done on a test environment here!

Saving and compiling the configuration.mof file in the hinv folder on the CAS/PSS. Will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.


Use PowerShell to check if the new classes have been created in WMI

## Check if the new classes are present in WMI
/* The machine must have at least one version of SQL installed in order for these classes to be created */
#  Get SQL 2017 class
Get-CimClass -ClassName SQL_2017_Property
#  Get SQL 2014 class
Get-CimClass -ClassName SQL_2014_Property
#  Get SQL 2012 class
Get-CimClass -ClassName SQL_2012_Property
#  Get SQL 2008 class
Get-CimClass -ClassName SQL_2008_Property
#  Get SQL Legacy class
Get-CimClass -ClassName SQL_Legacy_Property
#  Get SQL ProductID class
Get-CimClass -ClassName SQL_ProductID


Use SSMS (SQL Server Management Studio) to check if the views created in the CM database

Import the SSRS Report

Upload Report to SSRS

  • Start Internet Explorer and navigate to http:///Reports
  • Choose a path and upload the previously downloaded report file.

Configure Imported Report

Create the SQL Stored Procedure

The usp_PivotWithDynamicColumns is needed in order to maximize code reuse and have a more sane and sanitized data source.

  • Copy-paste the code below in SSMS
  • Change the in the USE statement to match your Site Code.
  • Click Execute to add the usp_PivotWithDynamicColumns stored procedure to your database.

You might need additional DB access to install the support function!
Allow some time for the policy to be downloaded or force a policy refresh.

Allow some time for the data gathered or force a HWI collection.
This report was created with SQL 2017 Reporting Services. You might need to remove some report elements. If you use an older version.


Report preview


HWI Extension

For reference only, you can download the file in the ‘Prerequisites’section.

HWI Definitions

For reference only, you can download the file in the ‘Prerequisites’section.

SQL Query

For reference only, the report includes this query.

VB Support Function

For reference only, the report includes this function.

Credit to Jakob Bindslet and Chrissy LeMaire.

Use Github for 🐛 reporting, or 🌈 and🦄 requests