SQL, PowerShell and Adding a User to an AD Group

PowerShell Help
1

Hi All:

I’m 95% done on a project but am struggling with the final piece. I only took one coding class as an undergrad and I don’t have a lot of experience in it. Scenario: users fill out a webform on our intranet when a new hire arrives. This data goes into an SQL server. I have the code written which:

  1. asks me via console input for the user last name. I type in the last name and hit enter.
  2. it then queries the sql database and returns the user in question (query only goes back 30 days to prevent retrieving an old record of someone w the same last name).
  3. AD user is created in an OU I specify from a tree.
  4. Exchange account is created.
I'm trying to finish the code that will add the newly created user to AD groups. I've gotten a list box with a variable early on in the code that lists all our user groups. This selection is stored into a variable ($thegroups) successfully. I need to try and pass the samaccount parameter onto the Add-ADGroupMember command.

Invoke-Sqlcmd -ServerInstance mysqlservername -Database mydatabasename -Query “SELECT * FROM dbo.NewUserForm WHERE Emp_LastName= ‘$MYVAR1’ AND DateReceived >= DATEADD(day, -30, GETDATE())” |

select @{l=‘Name’;e={$.Emp_FirstName+" "+$.Emp_LastName}},
@{l=‘SamAccountName’;e={$.Emp_FirstName.tolower().substring(0,1)+$.Emp_LastName.tolower()}},
@{l=‘UserPrincipalName’;e={$.Emp_FirstName.tolower().substring(0,1)+$.Emp_LastName.tolower()+“@contoso.local”}},
@{l=‘DisplayName’;e={$.Emp_FirstName+" "+$.Emp_LastName}},
@{l=‘GivenName’;e={$.Emp_FirstName}},
@{l=‘Surname’;e={$.Emp_LastName}},
@{l=‘Title’ ;e={$.JobTitle}},
@{l=‘OfficePhone’;e={$.PhoneNumber}} |

New-ADUser -Path “$($xyy)” -Country “us” -ChangePasswordAtLogon $True -State “WA” -City “Contosoville” -StreetAddress “$($theaddress)” -PassThru |
select Name, SamAccountName, UserPrincipalName

Add-ADGroupMember -members SamAccountName -Identity $thegroups

I know I am close. At least I think I am. Any help would be appreciated!

2

Only portions of the code are provided, but you should be able to do something like this:

$newUserParams = @{
    Path                  = $xyy 
    Country               = "us"
    ChangePasswordAtLogon = $True 
    State                 = "WA" 
    City                  = "Contosoville" 
    StreetAddress         = $theaddress
    PassThru              = $True
}

$newUser = New-ADUser @newUserParams |
           Select-Object -Property Name, 
                                   SamAccountName, 
                                   UserPrincipalName

Add-ADGroupMember -Identity $thegroups -Members $newUser.SamAccountName