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
}

PowerShell – Archive all Task Sequences to XML with Index

, , , , ,

We’ve amassed a very large number of task sequences since migrating to Configuration Manager 2012 and it got me thinking about ways to archive off older sequences so that we can clean house.  So I came up with this script.

The script will first collect all of the task sequences in your site.  Next it will enumerate through them, write the sequence to an XML named after the task sequence ID and finally create a CSV index of the TSID, task sequence name, the last modified date of the sequence and when the sequence was backed up.

Read more

Nested Task Sequence Mapper

, , , , ,

I’ve been looking for a simple way to identify and map out the nested task sequences.  This is the first part of a project that I’m working on.  The goal of the overall project is to duplicate an entire task sequence “suite” of the parent and all nested sequences, and then update all the nested references.

Read more