Managing Distribution Group via Powershell Script RRS feed

  • Question

  • I have a request to automate management of a Distribution list. The idea is that corporate will send my organizational unit a CSV file monthly and all users listed in that file must be members of a specific Exchange Distribution group.

    If the user account does not exist I need the script to create it. If the user is not in the CSV file it needs to be removed from the Distribution list but does not need to delete the account.

    What I'd like the final procedure to look like is as follows:

    The first time we receive the csv file I run the script and it adds the users listed into the appropriate distribution group. I suspect about 20% of the accounts won't exist so the script parses the user name information into our standard and creates them into a specified OU and adds those new accounts into the Distribution group during creation. Then every month we re-run the script with the latest csv. It verifies that all users listed in the csv are members of the specified group. If a user is dropped from the csv then the script will remove that user account from the distribution group.

    The test csv I received has three columns, a company ID number, the User's Name in this format (LastName,Firstname MI), and their company email address which is FirstName.LastName@domain.com.

    I am not the greatest with scripting so I'm hoping someone can either help me create one or possibly has a script similar to this that I could modify as needed.

    Vincent Sprague

    • Moved by Bill_Stewart Monday, December 17, 2018 6:45 PM This is not "scripts on demand"
    Monday, October 8, 2018 1:37 PM

All replies

  • Hi,

    I can't give you the whole solution because it is not the purpose of the this forum but I can give you a start. Remark : It is far away from a very optimised and dynamic way. It is just to have the idea how you could do that.

    It will be faster and easier to read/modify your script if you make two/three loops, cause you said you are not a scripting guy. The first one will create new users, the second one clear the groups, the third will update the distribution lists.

    The reason to clear members is because it is faster than making comparison.

    The logical idea is :

    - Step 1 : Create missing users

    - Step 2 : Clear group members

    - Step 3 : Foreach group listed in groups.csv, add the user if he has the value true in the column of the same name in the users.csv. So, if you have a group called GROUPA in groups.csv, you must have a column GROUPA in users.csv and a value true/false for each user.

    You need 2 files : users.csv -> contains users, groups.csv -> contains distribution lists

    users columns are : Firstname Lastname Group1 Group2 ...

    groups colums are : Name

    The idea is :

    $excelusers = Import-Csv -Path <path>\users.csv -Delimiter ',' #To be modified
    $excelgroups = Import-Csv -Path <path>\groups.csv -Delimiter ',' #To be modified
    $domain = 'mydomain.tld' #To be modified
    foreach($user in $excelusers)
      $upn = "$($user.Firstname.ToLower()).$($user.Lastname.ToLower())@$domain" #Create UPN
      $user = Get-ADUser -Filter { UserPrincipalName -eq $upn }
      if($user -eq $null) #If no user found
        #Create User via Create-ADUser -...
    #Clean groups
    foreach($group in $excelgroups)
      foreach($member in (Get-ADGroupMember -Identity $group.Name))
        #Remove members via Remove-ADGroupMember -... 
    #Update groups
    foreach($user in $excelusers)
      #if flag equal true in column "group1" => Add-ADGroupMember -...

    The key of learning is practice.

    Monday, October 8, 2018 3:59 PM
  • What you are requesting is not easy, and the solution would be specific to your organization. The first step for anyone scripting this is how to identify the users. None of the three csv fields you describe corresponds to Active Directory attributes that uniquely identifies users (or is even required). The -Identity parameter of the Get-ADUser cmdlet requires either the objectSID, objectGUID, distinguishedName, or sAMAccountName (labeled "pre-Windows 2000 logon name" in ADUC).

    I believe the best field in your csv to find users is company ID number, since most companies assign unique values. But this assumes that these values have been assigned to either the employeeID or employeeNumber attribute of the users (neither of which is displayed in ADUC). Next best would be the email address, but this requires that your company assigns unique values to either the mail, proxyAddresses, or userPrincipalName attributes. Least useful would be the name you describe. The Name (Relative Distinguished Name, or RDN) is only required to be unique in the parent OU or container. The givenName (first name), sn (last name), and initial (middle initial) might be used, if the combination is unique. None of these attributes is even required.

    A script would need to use the -Filter parameter of Get-ADUser to retrieve the user. For example, to get the user with company ID "c4163" you could use (in part):

    Get-ADUser -Filter {employeeID -eq "c4163"}
    But I suggest the script should deal with the situation when either no user is found, or more than one is found. If more than one user is found, it should be skipped. If you insist that the user should be created if none is found using the filter, the next question is how to specify the mandatory attributes. These are the Name (RDN), the parent OU, and the sAMAccountName. The sAMAccountName must be unique in the domain, The RDN must be unique in the OU. The sAMAccountName is limited to 20 characters and a few characters, like the comma, are not allowed. You can hard code the OU, and hopefully use your name field for the Name (RDN). But you would need some function to parse the name for a valid sAMAcountName, and some way to alter it if the value calculated is not unique.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Monday, October 8, 2018 4:40 PM