+ Post New Thread
Results 1 to 5 of 5
Coding Thread, Powershell Create Exchange Mailgroups from CSV in Coding and Web Development; Given as example code only, you will need to modify to make this work : Code: # Import our list ...
  1. #1


    Join Date
    May 2009
    Posts
    3,279
    Thank Post
    290
    Thanked 884 Times in 662 Posts
    Rep Power
    340

    Powershell Create Exchange Mailgroups from CSV

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

    Code:
    # Import our list of items
    $infile = "c:\tmp\staffgroups.csv"
    $list = import-csv $infile
    
    $dept_own=@{ "PSHE"="MrWhite@ourdomain.org.uk";
                 "BS"="MrBlack@ourdomain.org.uk";
                 "MA"="MrsPink@ourdomain.org.uk";
                 "EH"="MrGrey@ourdomain.org.uk";             
                 "LA"="MsGreen@ourdomain.org.uk" }
    
    #----------------------------------------------------------------------------
    #
    #
    #----------------------------------------------------------------------------
    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 "allstaff@ourdomain.org.uk" -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.

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,785
    Thank Post
    715
    Thanked 1,439 Times in 1,192 Posts
    Rep Power
    359
    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.

  3. #3


    Join Date
    May 2009
    Posts
    3,279
    Thank Post
    290
    Thanked 884 Times in 662 Posts
    Rep Power
    340
    Quote Originally Posted by vikpaw View Post
    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!

  4. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,785
    Thank Post
    715
    Thanked 1,439 Times in 1,192 Posts
    Rep Power
    359
    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.

  5. #5


    Join Date
    May 2009
    Posts
    3,279
    Thank Post
    290
    Thanked 884 Times in 662 Posts
    Rep Power
    340
    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*" )

    Code:
    #============================================================================
    # 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,"no-one@nowhere.org") # 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.
    Last edited by pcstru; 12th March 2014 at 03:10 PM.

SHARE:
+ Post New Thread

Similar Threads

  1. PowerShell Bulk create Users from csv
    By Chris_Jones in forum Scripts
    Replies: 1
    Last Post: 10th February 2014, 06:49 PM
  2. Creating multiple files from CSV
    By glennda in forum Coding
    Replies: 1
    Last Post: 2nd March 2012, 10:36 AM
  3. Create exchange mailboxes from csv
    By bart21 in forum Enterprise Software
    Replies: 3
    Last Post: 10th November 2011, 12:49 AM
  4. Replies: 1
    Last Post: 11th May 2010, 09:34 AM
  5. Exchange 2007 Global Address book from CSV
    By benIT in forum Windows
    Replies: 2
    Last Post: 22nd June 2008, 10:35 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •