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
}

Package Software While You Sleep Using the SCCM Application Packager Script – Part 2

, , , , , , , , ,

Welcome to Part 2 of the 2-part series about the SCCM Application Packager Script! If you have not yet read Part 1, I highly recommend doing so before continuing.

The SCCM Packager Tool works by parsing and processing XML files known as “recipes”. Recipes provide the instructions to the SCCM Application Packager Script that allow it to create applications in SCCM. Recipes are broken down into 5 main nodes. This post will cover each of the nodes, many of the tags and properties found in each node, and their purpose. Once a general understanding of the recipes and their properties are established, we will dissect the 7-Zip recipe for further insights. Please note that there are more properties available than the ones explained here. All available properties and options are documented in the Template.xml file located in the “Recipes” folder of the SCCM Application Packager Script.

Application Node

The Application Node contains information for the application that will be visible to the end user when the it is deployed. Essentially, the Application Node defines what will be seen in Software Center for each application. The Application Node does not have many options overall, so they are all broken down below:

  • Name – The name of the application as shown in SCCM and the Software Center
  • Description – The description of the application in Software Center. Also sets the “Administrative Comments”
  • Publisher – The application publisher as shown in Software Center
  • AutoInstall – Whether to allow the application to install during a task sequence or not
  • UserDocumentation – Link for the application to provide the end user with more information
  • Icon – The filename of the application icon in the icon repository folder

Downloads Node

The Downloads Node is responsible for downloading and copying the application installers and any associated files to the application share. Multiple downloads can be specified (for things like 32 and 64-bit downloads), and each download should be named to correspond with a “DeploymentType” in the Deployment Types Node. Different destinations can also be specified for each download (see the Template.xml file for more information). The Downloads Node also has just a few options, the most common are as follows:

  • PrefetchScript – The prefetch script can be used to download the software or find the URL of the download for the application if a direct link to the application download isn’t available.
  • URL – The link to download the latest version of the application.
  • DownloadFileName – Specify the name of the file when it is downloaded
  • Version and FullVersion – These can be blank, but MUST be present in each download definition
  • DownloadVersionCheck – This can be used to check the version of the application that was downloaded. If the version can’t be checked directly, additional processing can be done here as well
  • AppRepoFolder – Allows different folders to be specified on the application share per download
  • ExtraCopyFunctions – If additional files like configurations, installation scripts, or “.mst” files are needed, they can be copied to the content share here

Deployment Types Node

The Deployment Types Node is where all the magic really happens. In the Deployment Types Node, multiple deployment types can be specified for a single application. At this time, Script and MSI deployment types are supported. Each deployment type supports things like: Custom installation and uninstall commands, Custom and multiple detection methods including script detection methods, Installation behavior, and dependencies. The number of options available are too great to list for the deployment types, so it is recommended to check out the Template.xml to see the options available.

Distribution Node

The Distribution Node is responsible for distributing the content to SCCM distribution points. Generally, only one option will need to be specified here, and that is whether or not to distribute the content. If the “DistributeContent” property is set to “True”, the content for the application will automatically be distributed to the distribution point group specified in SCCMPackager.prefs. The following options are also available in the Distribution Node:

  • DistributeContent – Set to True to distribute content, Set to false to skip distributing content
  • DistributeToGroup – Distribution Point group to distribute content to
  • DistributeToDPs – Distribution Point to distribute content to

Deployment Node

The Deployment Node is similar to the Distribution Node. It has very few options and, generally, the only option that needs to be set is “DeploySoftware”. If the “DeploySoftware” property is set to “True”, the software will automatically be deployed as available to the default collection specified in SCCMPackager.prefs. Additionally, a collection other than the default can be specified for deployment. Other options available in the Deployment Node are as follows:

  • DeploySoftware – Set to True to deploy the software, Set to false to skip deploying software
  • DeploymentCollection – Specify the name of a collection to deploy to other than the default

 

Recipe Breakdown – 7-Zip.xml

Now that we have a general idea of the purpose of recipes and the features available to them, let’s break down a simple one to demonstrate a real life example. The recipe for 7-Zip is relatively simple, containing two downloads and two MSI deployment types. One of these deployment types also contains a requirements rule. Below, each node of the 7-Zip recipe will be dissected and discussed.

7-Zip Application Node

<Application>
    <Name>7-Zip</Name>
    <Description>7-Zip is a file archiver with a high compression ratio. Free and Open Source Software</Description>
    <Publisher>Igor Pavlov</Publisher>
    <AutoInstall>True</AutoInstall>
    <UserDocumentation>http://www.7-zip.org/</UserDocumentation>
    <Icon>7Zip.ico</Icon>
</Application>

As described earlier, the Application Node is fairly simple overall. The 7-Zip application will be packaged with the name “7-Zip <Version>”, the Description as shown in Software Center will be: “7-Zip is a file archiver with a high compression ratio. Free and Open Source Software”. The Publisher and Link provided in Software Center will be “Igor Pavlov” and “http://www.7-zip.org/” respectively. The SCCM Application Packager Script will also mark the option to allow the packaged application to be installed during a Task Sequence, and will choose the icon file “7Zip.ico” from the icon repository specified in SCCMPackager.prefs.

7-Zip Downloads Node

<Downloads>
    <Download DeploymentType="DeploymentType1">
        <PrefetchScript>$URL = "https://www.7-zip.org/$((Invoke-WebRequest https://www.7-zip.org/download.html |Select -ExpandProperty Links |where -Property href -like "*-x64.msi")[0].href)"</PrefetchScript>
        <URL></URL>
        <DownloadFileName>7Zipx64.msi</DownloadFileName>
        <Version></Version>
        <FullVersion></FullVersion>
        <DownloadVersionCheck>[String]$Version = ([String](Get-MSIInfo -Path $DownloadFile -Property ProductVersion)).TrimStart().TrimEnd()
        $Version = ($version.Split('.'))[0..1] -join "."</DownloadVersionCheck>
        <ExtraCopyFunctions></ExtraCopyFunctions>
    </Download>
    <Download DeploymentType="DeploymentType2">
        <PrefetchScript>$URL = "https://www.7-zip.org/$((Invoke-WebRequest https://www.7-zip.org/download.html |Select -ExpandProperty Links |where -Property href -like "*.msi")[0].href)"</PrefetchScript>
        <URL></URL>
        <DownloadFileName>7Zipx86.msi</DownloadFileName>
        <Version></Version>
        <FullVersion></FullVersion>
        <DownloadVersionCheck>[String]$Version = ([String](Get-MSIInfo -Path $DownloadFile -Property ProductVersion)).TrimStart().TrimEnd()
        $Version = ($version.Split('.'))[0..1] -join "."</DownloadVersionCheck>
        <ExtraCopyFunctions></ExtraCopyFunctions>
    </Download>
</Downloads>

The Downloads Node for 7-Zip has a bit more going on than the Application Node. Upon initial inspection, we can see that there are 2 “DeploymentType(s)” specified, one is tied to “DeploymentType1”, and the other is tied to “DeploymentType2”. We can also tell pretty quickly based on the “DownloadFileName(s)” properties of these downloads that they download the 64-bit and 32-bit msi installers of 7-Zip respectively.

You will notice that a URL is not provided in either of the downloads, however, each download has a “PrefetchScript”, that sets a variable called “$URL”. Let’s look at what the “PrefetchScript” in the “DeploymentType1” download does:

$URL = "https://www.7-zip.org/$((Invoke-WebRequest https://www.7-zip.org/download.html |Select -ExpandProperty Links |where -Property href -like "*-x64.msi")[0].href)"

We can see that this script is setting a $URL variable, it just so happens that the $URL variable is tied to the URL tag in the recipe, which is what the SCCM Application Packager Script will attempt to download. That means that whatever we set $URL to in the prefetch script will be downloaded by the Packager Script. If we break down the command a bit more, and run the command found in the inner-most parenthesis in PowerShell:

PS C:\Users\Andrew> (Invoke-WebRequest https://www.7-zip.org/download.html |Select -ExpandProperty Links |where -Property href -like "*-x64.msi")


innerHTML : Download
innerText : Download
outerHTML : <A href="a/7z1900-x64.msi">Download</A>
outerText : Download
tagName   : A
href      : a/7z1900-x64.msi

innerHTML : Download
innerText : Download
outerHTML : <A href="a/7z1604-x64.msi">Download</A>
outerText : Download
tagName   : A
href      : a/7z1604-x64.msi

innerHTML : Download
innerText : Download
outerHTML : <A href="a/7z920-x64.msi">Download</A>
outerText : Download
tagName   : A
href      : a/7z920-x64.msi

we will see that it returns 3 possible download links to 7-Zip MSIs. If we expand a bit and run everything found in the outer-most parenthesis:

PS C:\Users\Andrew> $((Invoke-WebRequest https://www.7-zip.org/download.html |Select -ExpandProperty Links |where -Property href -like "*-x64.msi")[0].href) 
a/7z1900-x64.msi

we are greeted with a file path for the latest 7-Zip MSI file. Finally, if we run the entire “PrefetchScript” script block:

PS C:\Users\Andrew> $URL = "https://www.7-zip.org/$((Invoke-WebRequest https://www.7-zip.org/download.html |Select -ExpandProperty Links |where -Property href -like "*-x64.msi")[0].href)"
PS C:\Users\Andrew> Write-Output $URL
https://www.7-zip.org/a/7z1900-x64.msi

we can see that it returns a full URL. If we go ahead and point our browser to that $URL, we can see that it is a direct download of the latest 7-Zip MSI file. This clarifies the purpose of the “PrefetchScript” Property, it allows the Packager to find the latest download link, even when a hotlink to the latest version is not provided. Crafting a good “PrefetchScript” is one of the harder things to do when creating a recipe, it can take much trial and error. A suggestion for creating a “PrefetchScript” is to build it outside of the packager, and only add it in once it is perfected as it’s own little script.

Moving on, the final thing we will look at in the Downloads Node is the “DownloadVersionCheck”. This property is also just a PowerShell script block. The goal of the “DownloadVersionCheck” is to provide the proper version of the application to the Packager so that it can determine if this download is a new version, and package it if it is new. The “DownloadVersionCheck” script for this recipe is fairly simple:

[String]$Version = ([String](Get-MSIInfo -Path $DownloadFile -Property ProductVersion)).TrimStart().TrimEnd()
$Version = ($version.Split('.'))[0..1] -join "."

First, the “ProductVersion” is grabbed by the “Get-MSIInfo” function, any leading or following white space is trimmed, and the string is saved to the “$Version” variable. Now, this version string will be something like “19.0.0.0” which is great and all, but 7-Zip does not even use the last two 0’s on their version string. So, for the sake of consistency with the vendor, we split the “19.0.0.0” version string by the “.” and only keep the first two array values (“19” and “0”), then we join those back together with a “.” and assign the joined string back to “$Version”.

Now you may notice, just like there is a $URL that corresponds to the URL tag in the XML, there is also a “Version” tag in the XML; if you were thinking that the “$Version” variable ties to the “Version” tag in the XML, you would be exactly right! It is also important to note that having a Version assigned to every application packaged is a very important aspect to the packaging process. The “Version” property plays an important role, not only in the naming of the application, but in the folder structure on the application share.

7-Zip Deployment Types Node

<DeploymentTypes>
    <DeploymentType Name="DeploymentType1">
        <DeploymentTypeName>7Zip Silent Install x64</DeploymentTypeName>
        <InstallationType>MSI</InstallationType>
        <Comments>Silent Installer for 7Zip</Comments>
        <Language>English</Language>
        <CacheContent>False</CacheContent>
        <BranchCache>True</BranchCache>
        <ContentFallback>True</ContentFallback>
        <OnSlowNetwork>Download</OnSlowNetwork>
        <InstallationMSI>7Zipx64.msi</InstallationMSI>
        <UninstallCmd></UninstallCmd>
        <Force32bit>False</Force32bit>
        <InstallationBehaviorType>InstallForSystem</InstallationBehaviorType>
        <LogonReqType>WhetherOrNotUserLoggedOn</LogonReqType>
        <UserInteractionMode>Hidden</UserInteractionMode>
        <ReqUserInteraction>False</ReqUserInteraction>
        <EstRuntimeMins>5</EstRuntimeMins>
        <MaxRuntimeMins>15</MaxRuntimeMins>
        <RebootBehavior>BasedOnExitCode</RebootBehavior>
        <DetectionMethodType>MSI</DetectionMethodType>
        <Requirements>
            <RuleName>Existential of AutoPackage - OSArchitecture x64 Not Equal to 0</RuleName>
        </Requirements>
    </DeploymentType>
    <DeploymentType Name="DeploymentType2">
        <DeploymentTypeName>7Zip Silent Install x86</DeploymentTypeName>
        <InstallationType>MSI</InstallationType>
        <Comments>Silent Installer for 7Zip</Comments>
        <Language>English</Language>
        <CacheContent>False</CacheContent>
        <BranchCache>True</BranchCache>
        <ContentFallback>True</ContentFallback>
        <OnSlowNetwork>Download</OnSlowNetwork>
        <InstallationMSI>7Zipx86.msi</InstallationMSI>
        <UninstallCmd></UninstallCmd>
        <Force32bit>False</Force32bit>
        <InstallationBehaviorType>InstallForSystem</InstallationBehaviorType>
        <LogonReqType>WhetherOrNotUserLoggedOn</LogonReqType>
        <UserInteractionMode>Hidden</UserInteractionMode>
        <ReqUserInteraction>False</ReqUserInteraction>
        <EstRuntimeMins>5</EstRuntimeMins>
        <MaxRuntimeMins>15</MaxRuntimeMins>
        <RebootBehavior>BasedOnExitCode</RebootBehavior>
        <DetectionMethodType>MSI</DetectionMethodType>
    </DeploymentType>
</DeploymentTypes>

Alright here we are, the meat of the Recipe. The Deployment Types Node usually ends up being the largest part of any Recipe (depending of course on how tough it is to scrape the latest download for an application).

Looking over the Deployment Types Node, we can see that there are two “DeploymentType” objects with the names “DeploymentType1” and “DeploymentType2”, which correspond with the downloads we saw earlier with the same names. There is also a “DeploymentTypeName” specified in each deployment type, but don’t let that fool you, the “DeploymentTypeName” tag only specifies the name of the deployment type in SCCM. Looking over the rest of the deployment type properties, most of the options should look familiar if you have ever created an application in SCCM before.

We can also see that the 64-bit deployment type has a “Requirements” tag with an associated “RuleName”. These properties specify that a requirements rule with the name “Existential of AutoPackage – OSArchitecture x64 Not Equal to 0” should be added to the application. By default, on it’s first run, the SCCM Application Packager Script will create a few Global Conditions for use when packaging; one of which is “Existential of AutoPackage – OSArchitecture x64” which returns a “1” on a 64-bit operating system. Therefore, this rule will ensure that the 64-bit version will only install on 64-bit computers, while the 32-bit install will run on anything else.

Some other options of note for this recipe are:

  • InstallationType – This sets the type of installation for the deployment type. The MSI “DeploymentType” is nice because if you provide an MSI, SCCM will automatically provide things like Install/Uninstall commands and a detection method. You will notice that specific install and uninstall commands are not specified in this recipe, however they can be added to MSI deployment types in the Packager
  • InstallationMSI – This is the MSI provided to SCCM for it to generate the Install/Uninstall commands and the detection method
  • DetectionMethodType – This specifies that the MSI file should be used to generate the detection method automatically, instead of using a script, file, or registry key for the detection method

7-Zip Distribution Node

<Distribution>
   <DistributeContent>True</DistributeContent>
</Distribution>

Now that we have covered all of the complex parts of the recipe, we can end on a much more gentle note. The Distribution Node for the 7-Zip application only contains a single property. The “DistributeContent” property for 7-Zip is set to “True” meaning it will be distributed to the default distribution point group specified in the preferences file.

7-Zip Deployment Node

<Deployment>
   <DeploySoftware>True</DeploySoftware>
</Deployment>

Last but not least, the Deployment Node for the 7-Zip recipe also contains just a single Property. The “DeploySoftware” property here is also set to “True” meaning the software will be deployed as available to the default collection specified in the preferences file. I will note that this is the default for all of the recipes provided, but can be changed per recipe depending on your specific needs.

7-Zip Application End Result

This post went a bit long, so I am going to let the results speak for themselves here:

This slideshow requires JavaScript.

The SCCM Application Packager Script is a very powerful tool. Its recipes were designed for ultimate flexibility with the intention of automating the packaging of just about any application in SCCM. Creating your own recipes can be a time consuming process, however, the end result will end up saving a ton of time. In my own production environment, I have saved an estimated 220 hours a year with this tool, and that number will only grow as more recipes are created, and more application updates are released per month.

Thank you for taking the time to read this post and your interest in the SCCM Packager Script! I created this tool with the intention of saving people time and energy, and was able to deepen my knowledge in SCCM and PowerShell as a result. If you are interested in contributing your own recipes to the project, feel free to submit pull requests to the GitHub repository.

Andrew Jimenez

Twitter: @AndrewJimenez_

Configure-DedupeForMe – SCCM

, , , , , , , , , , , ,