Powershell Create Exchange Mailgroups from CSV

[pcstru]

Given as example code only, you will need to modify to make this work :

 

# Import our list of items
$infile = "c:\tmp\staffgroups.csv"
$list = import-csv $infile

$dept_own=@{ "PSHE"="[email protected]";
            "BS"="[email protected]";
            "MA"="[email protected]";
            "EH"="[email protected]";             
            "LA"="[email protected]" }

#----------------------------------------------------------------------------
#
#
#----------------------------------------------------------------------------
function AddToHash {
   
   Param ($TheHash, $Keys, $level=0)
   
   $level=$level+1
       
   $key=$Keys[0]
   $sle=$Keys.length - 1
       
   if( ! $TheHash.ContainsKey($key) ) {        
       if ($Keys.length -gt 1) {
          $tmpHash = @{}
          $tmpHash = AddToHash -TheHash $tmpHash -Keys $Keys[1..$sle] -level $level
          $Thehash.Add($key,$tmpHash)
       } else {
          $TheHash.Add($key,1)
       }
   } else {
       if ($Keys.length -gt 1) {        
          $tmpHash = $TheHash.Get_Item($key)                      
          $tmpHash = AddToHash -TheHash $tmpHash -Keys $Keys[1..$sle] -level $level
       } else {
          $TheHash.Set_Item($Key,$TheHash.Get_Item($key) + 1 )
       }
   }
   
   $TheHash
              
}
#----------------------------------------------------------------------------




#----------------------------------------------------------------------------
#
#
#----------------------------------------------------------------------------
Function GroupsFromHash {

   Param ($TheHash)

   # We have the hashes of hashes
   foreach($key in $TheHash.keys) {
       
       # The group members are in the hash for the dept key              
       $own=$dept_own.Get_Item($key);
       $Grp=$key

       Remove-DistributionGroup -Identity $Grp -Confirm:$False
       New-DistributionGroup -Name $Grp -OrganizationalUnit "ourdomain.org.uk/GroupsContainer" -SamAccountName $Grp -Type "Distribution" -ManagedBy $own
       Set-DistributionGroup -AcceptMessagesOnlyFromSendersOrMembers "[email protected]" -Identity $Grp
       
       foreach($email in $TheHash.Get_Item($key).keys ) {
           Add-DistributionGroupMember -Identity $Grp -Member $email
       }
   }     
   
}    
#----------------------------------------------------------------------------




#----------------------------------------------------------------------------
#
#
#----------------------------------------------------------------------------
function CreateFacultyStaffGroups {

   # Clear Hash Table
   $dept_grp=@{}

   cls

   # Process the List and build hash of hashes
   foreach($item in $list) {
       $keys=@($item.Dept,$item.StaffEmail)
       $dept_grp = AddToHash -TheHash $dept_grp -Keys $keys
   }
   
   GroupsFromHash -TheHash $dept_grp

}
#----------------------------------------------------------------------------



CreateFacultyStaffGroups

 

The example code presumes a CSV file with two columns, a department and an email address for the staff member.

 

The main process is driven by CreateFacultyStaffGroups which loops through the records in the file and calls AddToHash. The hash is actually a hash of hashes, and AddToHash is a little mind-bender of a recursive function which given an array of attributes, builds this structure. The leaf's of the structure are just a count of instances - essentially unused, it's the keys we are interested in. Having built a hash, GroupsFromHash will parse that structure and create distribution groups. There is a hack with an additional hard coded hash to deal with the group owners.

 

It's pretty easy to take this and feed it a list of teaching group codes with the student email address and set the owner to the teacher of the group.One problem with the code is the destruction of the group in order to update it. This simplifies the processing significantly but can cause issues for people having out of date global address book.

[vikpaw]

Cool, but the ghostly remnants would be a pain.

If the group name is unique, instead of a delete, can you get it to empty itself, and re-add members.

[pcstru]

Cool, but the ghostly remnants would be a pain.

If the group name is unique, instead of a delete, can you get it to empty itself, and re-add members.

I'm not sure what you mean by "ghostly remnants". AFAIK group names in exchange or AD must be unique. The only (end-user) disadvantage of removing the group and re-creating is that the "All groups" on the global list needs to update for changes to take effect.

 

I do have in mind to do a two pass process, where the group membership is examined and individual users added or removed as needed. Afraid I don't have the time to code it up at the moment but I'm happy to help anyone who wants to give it a go!

[vikpaw]

Ah I see what you mean. The group will appear as new in the list.

 

I was thinking of the local cache, whenever the unique id changes you have people start to type and it comes up with the old cached version and then bounces. At least that's what we get now when an email has had to be recreated.

 

Still looks useful though, especially if you can get the update working.

We rely on running a report to get the names live and it is doing the trick for MIS groups.

[pcstru]

So code to update...

 

This is fairly generic for managing groups from a CSV file. Previously I've built into the powershell code to identify things like year groups and departments and then create a distribution group from that (so you get YR7_students or even Staff_MA etc), but that can all be fed in via the CSV and probably processed more easily by crafting a few bit's of SQL to drive the creation of a CSV.

 

There are a couple of things you will need to adjust. Most are documented at the top, but also in the CSVGroups function the Prefixes array will need attention. That caters for limiting the scope to groups either by constructing a list (including wildcards) which will be used to select groups in AD. So my example would be we are dealing with teaching groups which always start with the year group (and no other group in the target OU does), so we construct an array :

 

$Prefixes=@("7*","8*","9*","10*","11*","12*","13*")

 

#============================================================================
# Create groups from data sourced from MIS via CSV file. The CSV must contain
# three columns (names can easily be changed) :
#
#                group : The name for the Group 
#                email : The Students Email address
#                LectEmail : The Teachers Email address
#
# Groups will be created where they don't exist in AD and members added. Where
# groups already exist, they will be checked for correct membership and students
# added or deleted as appropriate.
#============================================================================

$infile = "C:\tmp\stugroups.csv"    # Name of the CSV file

$OrgFQDN = "somewhere.org.uk"     # The FQDN of the organisation
$GrpOU="$OrgFQDN/TestGroups"  # The OU in which the Groups live
$AcceptFrom="allstaff@$OrgFQDN"     # Limit senders group will accept mail from - leave blank if not required

$CSVGroup="group"                   # Column in the CSV containing the group name
$CSVStudent="Email"                 # Column in the CSV containing the student email
$CSVGrpOwn="LectEmail"              # Column in the CSV containing the group manager/owner email

$ACT_DelGroups=$false               # Delete Groups not present in incomming file (be brave!)
$ACT_Simulate=$false                # Do nothing to AD - just report


# Grab the data into a list
$list = import-csv $infile        



#----------------------------------------------------------------------------
# Generic Multi level hash builder. Pass hash and array of node keys. Leaf
# nodes are created as simple markers (integer = 1). All array elements are
# created as hash keys. 
#----------------------------------------------------------------------------
function AddToHash {
   
   Param ($TheHash, $Keys, $level=0)
   
   $level=$level+1
       
   $key=$Keys[0]
   $sle=$Keys.length - 1
       
   if( ! $TheHash.ContainsKey($key) ) {        
       if ($Keys.length -gt 1) {
          $tmpHash = @{}
          $tmpHash = AddToHash -TheHash $tmpHash -Keys $Keys[1..$sle] -level $level
          $Thehash.Add($key,$tmpHash)
       } else {
          $TheHash.Add($key,1)
       }
   } else {
       if ($Keys.length -gt 1) {        
          $tmpHash = $TheHash.Get_Item($key)                      
          $tmpHash = AddToHash -TheHash $tmpHash -Keys $Keys[1..$sle] -level $level
       } else {
          $TheHash.Set_Item($Key,$TheHash.Get_Item($key) + 1 )
       }
   }
   
   $TheHash
              
}
#----------------------------------------------------------------------------



#----------------------------------------------------------------------------
#
#
#----------------------------------------------------------------------------
function CSVGroups {

   $AD_Grps=@{}    # A hash of the groups in AD
   $MIS_Grps=@{}   # A Hash of the groups in the MIS

   $AD_GrpOwn=@{}  # A hash of group owners (in AD)
   $MIS_OWn=@{}    # Hash of group ownders (managed by) in MIS export
   
   # We want to limit the groups (in AD) to teaching groups which always start with a number (Years 7-13)
   # $Prefixes=@("7*","8*","9*","10*","11*","12*","13*")
   
   # Hack for development, limit scope to few groups
   $Prefixes=@("Test*")

   write-host "Hash AD Groups"
   # Make a hash of the distribution group membership in AD, limit to valid teaching group prefixes
   foreach( $Prefix in $Prefixes ) {
       # Process groups within that prefix
       foreach( $group in Get-DistributionGroup -Identity "$prefix" -OrganizationalUnit $GrpOU -ResultSize Unlimited) {
           # Who owns teh group? - make a not of that.
           $owner=Get-User -Identity $($group.ManagedBy)
           #$own_email=$($owner.WindowsEmailAddress)

           #$keys=@($group.Name,$own_email)            
           $AD_GrpOwn=AddToHash -TheHash $AD_GrpOwn -Keys @($group.Name,$($owner.WindowsEmailAddress))            
           
           # Process members for the group
           foreach( $member in Get-DistributionGroupMember -Identity "$group" ) {
               $email = ($member.primarysmtpaddress )
               $keys=@($group.Name,$email)
               $AD_Grps=AddToHash -TheHash $AD_Grps -Keys $keys
               
               #write-host "$group $member $email"
               #write-host $AD_Grps.ContainsKey($group)
           }       
       }
   }
   
   # $AD_Grps now contains the teaching groups and members present in AD/Exchange
   
   # Make a hash of the teaching groups from the incomming MIS data.
   write-host "Read MIS List"
   foreach($item in $list) {
       $MIS_Grps = AddToHash -TheHash $MIS_Grps -Keys @($item.$CSVGroup,$item.$CSVStudent)
       $MIS_Own = AddToHash -TheHash $MIS_Own -Keys @($item.Group,$item.LectEmail)
   }
   

   # So we now have hashes with the state of AD and the state of the MIS.
   # Process the incomming MIS list and see what doesn't exist in AD

   write-host "MIS Pass - Add missing"    
   do {
       $done = $True
       $MIS_Grps.Keys | ForEach-Object {
           $group = $_
           # Does the group exist?
           if ( ! $AD_Grps.ContainsKey($group) ) {
               # Entire Group is missing, Add group
               Write-Host "Add $group"

               if (!$ACT_Simulate) {
                   $owner = $MIS_own.Get_Item($group)
                   New-DistributionGroup -Name $group -OrganizationalUnit $GrpOU -SamAccountName $group -Type "Distribution" -ManagedBy $owner.keys
                   
                   if ( $AcceptFrom -ne "") {
                       Set-DistributionGroup -AcceptMessagesOnlyFromSendersOrMembers $AcceptFrom -Identity $group
                   }
               }
                               
               # Triggering a second pass will deal with members but we need to 
               # find it in the hash and it isnt there. (this is kind of lazy but hey ho)
               $keys=@($group,"[email protected]") # will moan on 2nd pass when it tries to remove the user.
               $AD_Grps=AddToHash -TheHash $AD_Grps -Keys $keys
               $done=$False
               
           } else {
               # Check group owner and adjust
               if( $AD_GrpOwn.Get_Item($group).Key -ne $MIS_own.Get_Item($group).Key ) {
                   # Update Managed by
                   Write-Host "$group Was Managed by : $AD_GrpOwn.Get_Item($group).Key Change to Managed by: $MIS_own.Get_Item($group).Key "
                   if (!$ACT_Simulate) {
                       Set-DistributionGroup -ManagedBy $($MIS_own.Get_Item($group).Key) -Identity $group
                   }
               }
               
               # Check membership of the group
               $AD_GMemb = $AD_Grps.Get_Item($group)
               $MIS_Grps.Get_Item($group).Keys | ForEach-Object {
                   $student=$_
                   if(!$AD_GMemb.ContainsKey($student)) {
                       # Missing, student needs to be added to group
                       Write-Host "Add $student to $group"
                       if (!$ACT_Simulate) {
                           Add-DistributionGroupMember -Identity $group -Member $student
                       }

                   } 
               }
           }
       }        
   } until ($done)
   
   write-host "AD Pass - Remove Surplus"    
   # Process the AD list and remove what doesn't exist in the MIS
   $AD_Grps.Keys | ForEach-Object {
       $group = $_
       if ( ! $MIS_Grps.ContainsKey($group) ) {
           # MIS does not know of this group. Remove if really brave flag set.
           if ($ACT_DelGroups) {
               Write-Host "Remove $group from AD"
               if (!$ACT_Simulate) {
                   Remove-DistributionGroup -Identity $group -Confirm:$False
               }
           }
       } else {
           # MIS knows of the group, check membership
           $MIS_GMemb = $MIS_Grps.Get_Item($group)
           $AD_Grps.Get_Item($group).Keys | ForEach-Object {
               if (!$MIS_GMemb.ContainsKey($_) ) {
                   # Student is missing from MIS, remove from AD Group
                   Write-Host "Remove $_ from $group"
                   if (!$ACT_Simulate) {
                       Remove-DistributionGroupMember -Identity $group -Member $_ -Confirm:$False
                   }
               }
           }            
       }
   }
   
   Write-Host "Update Complete"
   
}
#----------------------------------------------------------------------------

Clear
CSVGroups

 

I've done some testing on this but it's provided as is, and you use at your own risk.

 

Feedback always welcome.

Edited March 12, 2014 by pcstru