Category: SCCM Automation

SCCM Collection Query for groups

SCCM Collections can have Devices and Users added directly to them, but this doesn’t scale and means that the person adding the Devices or Users needs access to SCCM. It makes much more sense to create a Collection Query that queries an AD group. The following query is used for Devices.

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemGroupName = "<Domain>\\<AD Group>"

And this one is used for Users.

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.UserGroupName = "<Domain>\\<AD Group>"

Don’t forget to replace the <Domain> and <AD Group> entries with your own details!

Add a User or Computer to multiple groups using PowerShell

During many deployment or transformation projects, no matter how organised, there is often a requirement to add Users or Computers to application deployment groups during the rollout phase. Often this has to be done at the last minute due to unexpected requirements (or users simply not having responded to requiests for information.

I wrote the following Powershell script to allow Users or Computers to be added to multiple groups. All the groups are expected to be in one OU, and this is set when the script is first run. It will then use that location when opened subsequently. Multiple groups can be selected.

This script requires that the Microsoft RSAT tools are installed on the machine where the script is run.

####################################################################################
# This application lists all available software groups and allows multiple groups to
# be added to a Computer or User
#
# Created by Graham Higginson 13/12/2017
# V1.0
#
####################################################################################

#region  ScriptForm  Designer

#region  Constructor


[void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")

#endregion

#region Post-Constructor Custom Code

#endregion

#region Form Creation
#Warning: It is recommended that changes inside this region be handled using the ScriptForm Designer.
#When working with the ScriptForm designer this region and any changes within may be overwritten.
#~~< Form1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Form1 = New-Object System.Windows.Forms.Form
$Form1.ClientSize = New-Object System.Drawing.Size(904, 704)
#$Form.AutoScroll = $true
#~~< Label4 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Label4 = New-Object System.Windows.Forms.Label
$Label4.Location = New-Object System.Drawing.Point(24, 642)
$Label4.Size = New-Object System.Drawing.Size(604, 15)
$Label4.TabIndex = 10
$Label4.Text = "Enter AD group location here, e.g. OU=App Groups, OU=Groups, OU=Client, DC=Company, DC=Local "
#~~< TextBox3 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$TextBox3 = New-Object System.Windows.Forms.TextBox
$TextBox3.Location = New-Object System.Drawing.Point(24, 660)
$TextBox3.Size = New-Object System.Drawing.Size(540, 21)
$TextBox3.TabIndex = 9
$TextBox3.Text = ""
#~~< TextBox2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$TextBox2 = New-Object System.Windows.Forms.TextBox
$TextBox2.Location = New-Object System.Drawing.Point(24, 475)
$TextBox2.Multiline = $true
$TextBox2.ScrollBars = "Vertical"
$TextBox2.WordWrap = $false
$TextBox2.Size = New-Object System.Drawing.Size(540, 141)
$TextBox2.TabIndex = 8
$TextBox2.Text = ""
#~~< Label3 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Label3 = New-Object System.Windows.Forms.Label
$Label3.Font = New-Object System.Drawing.Font("Tahoma", 12.0, [System.Drawing.FontStyle]::Bold, [System.Drawing.GraphicsUnit]::Point, ([System.Byte](0)))
$Label3.Location = New-Object System.Drawing.Point(114, 479)
$Label3.Size = New-Object System.Drawing.Size(277, 23)
$Label3.TabIndex = 8
$Label3.Text = ""
$Label3.TextAlign = [System.Drawing.ContentAlignment]::MiddleCenter
#~~< ListView1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$ListView1 = New-Object System.Windows.Forms.ListView
$ListView1.Location = New-Object System.Drawing.Point(24, 81)
$ListView1.Size = New-Object System.Drawing.Size(540, 374)
$ListView1.TabIndex = 7
#$ListView1.CheckBoxes = $true
$ListView1.FullRowSelect = $true
$ListView1.Text = "ListView1"
$ListView1.UseCompatibleStateImageBehavior = $false
$ListView1.MultiSelect = $true
$ListView1.View = [System.Windows.Forms.View]::Details
#~~< ColumnHeader1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$ColumnHeader1 = New-Object System.Windows.Forms.ColumnHeader
$ColumnHeader1.Text = "Group Name"
$ColumnHeader1.Width = 400
#~~< ColumnHeader2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#$ColumnHeader2 = New-Object System.Windows.Forms.ColumnHeader
#$ColumnHeader2.Text = "Install State"
#$ColumnHeader2.Width = 100
$ListView1.Columns.AddRange([System.Windows.Forms.ColumnHeader[]](@($ColumnHeader1)))
#~~< Button4 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Button4 = New-Object System.Windows.Forms.Button
$Button4.Font = New-Object System.Drawing.Font("Tahoma", 12.0, [System.Drawing.FontStyle]::Regular, [System.Drawing.GraphicsUnit]::Point, ([System.Byte](0)))
$Button4.Location = New-Object System.Drawing.Point(678, 408)
$Button4.Size = New-Object System.Drawing.Size(120, 47)
$Button4.TabIndex = 6
$Button4.Text = "Close"
$Button4.UseVisualStyleBackColor = $true
$Button4.add_MouseClick({Button4MouseClick($Button4)})
#~~< Button3 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Button3 = New-Object System.Windows.Forms.Button
$Button3.Font = New-Object System.Drawing.Font("Tahoma", 12.0, [System.Drawing.FontStyle]::Regular, [System.Drawing.GraphicsUnit]::Point, ([System.Byte](0)))
$Button3.Location = New-Object System.Drawing.Point(678, 341)
$Button3.Size = New-Object System.Drawing.Size(120, 47)
$Button3.TabIndex = 5
$Button3.Text = "Clear"
$Button3.UseVisualStyleBackColor = $true
$Button3.add_MouseClick({Button3MouseClick($Button3)})
#~~< Button2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Button2 = New-Object System.Windows.Forms.Button
$Button2.Font = New-Object System.Drawing.Font("Tahoma", 12.0, [System.Drawing.FontStyle]::Regular, [System.Drawing.GraphicsUnit]::Point, ([System.Byte](0)))
$Button2.Location = New-Object System.Drawing.Point(678, 642)
$Button2.Size = New-Object System.Drawing.Size(120, 47)
$Button2.TabIndex = 4
$Button2.Text = "Update"
$Button2.UseVisualStyleBackColor = $true
$Button2.add_MouseClick({Button2MouseClick($Button2)})
#~~< Button1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Button1 = New-Object System.Windows.Forms.Button
$Button1.Font = New-Object System.Drawing.Font("Tahoma", 12.0, [System.Drawing.FontStyle]::Regular, [System.Drawing.GraphicsUnit]::Point, ([System.Byte](0)))
$Button1.Location = New-Object System.Drawing.Point(678, 196)
$Button1.Size = New-Object System.Drawing.Size(120, 47)
$Button1.TabIndex = 3
$Button1.Text = "Add"
$Button1.UseVisualStyleBackColor = $true
$Button1.add_MouseClick({Button1MouseClick($Button1)})
#~~< Label2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Label2 = New-Object System.Windows.Forms.Label
$Label2.Font = New-Object System.Drawing.Font("Tahoma", 9.5, [System.Drawing.FontStyle]::Bold, [System.Drawing.GraphicsUnit]::Point, ([System.Byte](0)))
$Label2.Location = New-Object System.Drawing.Point(663, 81)
$Label2.Size = New-Object System.Drawing.Size(201, 19)
$Label2.TabIndex = 2
$Label2.Text = "Enter computer or User name"
#~~< Label1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$Label1 = New-Object System.Windows.Forms.Label
$Label1.Font = New-Object System.Drawing.Font("Tahoma", 16.0, [System.Drawing.FontStyle]::Regular, [System.Drawing.GraphicsUnit]::Point, ([System.Byte](0)))
$Label1.Location = New-Object System.Drawing.Point(214, 9)
$Label1.Size = New-Object System.Drawing.Size(446, 32)
$Label1.TabIndex = 1
$Label1.Text = "Multi-group add Tool"
$Label1.add_Click({Label1Click($Label1)})
#~~< TextBox1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$TextBox1 = New-Object System.Windows.Forms.TextBox
$TextBox1.Location = New-Object System.Drawing.Point(663, 103)
$TextBox1.Size = New-Object System.Drawing.Size(135, 20)
$TextBox1.TabIndex = 0
$TextBox1.Text = ""
$Form1.Controls.Add($Label4)
$Form1.Controls.Add($TextBox3)
$Form1.Controls.Add($TextBox2)
$Form1.Controls.Add($Label3)
$Form1.Controls.Add($ListView1)
$Form1.Controls.Add($Button4)
$Form1.Controls.Add($Button3)
$Form1.Controls.Add($Button2)
$Form1.Controls.Add($Button1)
$Form1.Controls.Add($Label2)
$Form1.Controls.Add($Label1)
$Form1.Controls.Add($TextBox1)

#endregion

#region Custom Code

#endregion

#region Event Loop

function Main{
	[System.Windows.Forms.Application]::EnableVisualStyles()
    #$Form1.AcceptButton = $Button1
	
    $listView1.Items.Clear()
    
    foreach ($Group in $Groups)
       {
           $Line = New-Object System.Windows.Forms.ListViewItem($Group.Name)
           $ListView1.Items.Add($Line)
       }

    [System.Windows.Forms.Application]::Run($Form1)

}


#endregion

#endregion

#region Event Handlers

function Button1MouseClick( $object )
{
$CompName = "" 
$SelectedGroups = $listView1.SelectedItems.Text 
$Error.Clear()

Try {$Name = Get-ADComputer $TextBox1.Text}
    Catch {}

Try {if ($Error[0] -ne "") {$Name = Get-ADUser $TextBox1.Text -EA}}
    Catch {}

    


if ($Name -ne "")
   {

   foreach ($SelectedGroup in $SelectedGroups)
    {
        $Error.Clear()
        #Write-Host $SelectedGroup 
        $Members = Get-ADGroupMember -identity $SelectedGroup
        $Identity = get-ADGroup $SelectedGroup
        
        if ($Members.name -notcontains $TextBox1.Text -and $Members.SamAccountName -notcontains $TextBox1.Text)
            {        
        
            $Result = Add-ADGroupMember -identity $Identity.DistinguishedName -members $Name.DistinguishedName 
            if ($error[0] -gt "")
               {
                    $TextBox2.AppendText("Unable to add " + $TextBox1.Text + " to " + $SelectedGroup + "`r`n")
                    $TextBox2.AppendText("Error message " + $Error[0] + "`r`n")
                    $error.Clear()
               }
            else
               {
                    $TextBox2.AppendText("Successfully added " + $TextBox1.Text + " to " + $SelectedGroup + "`r`n")
               }
    }
    else
    {
    $TextBox2.AppendText($TextBox1.Text + " is already a member of " + $SelectedGroup + "`r`n")
    }

   }

   }
   else
   {
   $TextBox2.AppendText("Unable to find device " + $Name + "!!!`r`n")
   } 



}

function Button2MouseClick($object)
{
    Set-ItemProperty -path HKCU:\Software\MultiGroupAddTool -Name SearchBase -Value $TextBox3.text
    $SearchBase= $TextBox3.Text 
    Try {$Groups = get-adgroup -filter * -searchbase $SearchBase | sort Name}
        Catch
            {
                $TextBox2.AppendText("Unable to get AD group list" + "`r`n")
                $TextBox2.AppendText("Error message " + $Error[0] + "`r`n")
                $error.Clear()
            }
    
    $listView1.Items.Clear()
    
    foreach ($Group in $Groups)
       {
           $Line = New-Object System.Windows.Forms.ListViewItem($Group.Name)
           $ListView1.Items.Add($Line)
       }          


}

function Button3MouseClick($object)
{
    #$listView1.Items.Clear()
    $TextBox1.Clear()
    $Label3.Text = ""		
}

function Button4MouseClick($object)
{
   $Groups = ""
   $Form1.Dispose()  
}


function Label1Click( $object ){

}

    #$TextBox2.AppendText("Preparing AD group list..." + "`r`n")

        
    if ((Test-Path -Path HKCU:\Software\MultiGroupAddTool) -eq $false)
        {
        New-Item -path HKCU:\Software\MultiGroupAddTool
        New-ItemProperty -path HKCU:\Software\MultiGroupAddTool -Name SearchBase
        $SearchBase = ""
        }
    else
    {
                $Reg = Get-ItemProperty -path HKCU:\Software\MultiGroupAddTool -Name SearchBase
                $TextBox3.text = $Reg.SearchBase
                $SearchBase = $Reg.SearchBase
    }

    if ($SearchBase -eq "") 
        {
        [System.Windows.MessageBox]::Show("Please add AD group location")
         #Main
        }

    # **** Change the -searchbase entry below to point at the Active Directory OU containing Application Deployment Groups ****
    #$Groups = get-adgroup -filter * -searchbase "OU=Application Catalogue, OU=Software, OU=Groups, OU=Client, DC=Arqiva, DC=Local" | sort Name
    if ($SearchBase -ne "")
    {
    $Groups = get-adgroup -filter * -searchbase $SearchBase | sort Name
        if ($error[0] -gt "")
            {
                $TextBox2.AppendText("Unable to get AD group list" + "`r`n")
                $TextBox2.AppendText("Error message " + $Error[0] + "`r`n")
                $error.Clear()
            }
        else
            {
                #$TextBox2.AppendText("Successfully added " + $CompName + " to " + $SelectedGroup + "`r`n") 
            }
    }


Main #This call must remain below all other event functions

#endregion