Modify PS to sync from internal user DB to AD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Just verifying the syntax with actual programmers before I run it in case. It seemed weird to have so many "or" clauses. Thanks for the verification!

you could do other comparisons, but the ors you have here are honestly probably less confusing and I don't think alternate methods would be less 'work' on that step.

If you wanted, you could remove the criteria so it would just replace the user's data without checking of course.

otherwise, the or syntax looks good it will only update the user if it finds some part of the user's data is different than the data in the DB, which I assume to be your prefference.

Glad to help :)

ASKER

Hey Ben, So I'm getting the folowing error when running the script:

At C:\rpit\sql_to_AD_sync.ps1:18 char:81
+ ... pos_department or $usr.roomNumber -ne $_.con_mailstop or $usr.employeeID -ne $_. ...
+                    ~~
Unexpected token 'or' in expression or statement.
At C:\rpit\sql_to_AD_sync.ps1:18 char:81
+ ... pos_department or $usr.roomNumber -ne $_.con_mailstop or $usr.employeeID -ne $_. ...
+                    ~~
Missing closing ')' after expression in 'if' statement.
At C:\rpit\sql_to_AD_sync.ps1:17 char:5
+     {
+     ~
Missing closing '}' in statement block.
At C:\rpit\sql_to_AD_sync.ps1:15 char:5
+   % {
+     ~
Missing closing '}' in statement block.
At C:\rpit\sql_to_AD_sync.ps1:18 char:156
+ ... e $_.emp_number)
+                    ~
Unexpected token ')' in expression or statement.
At C:\rpit\sql_to_AD_sync.ps1:22 char:5
+     } else {
+     ~
Unexpected token '}' in expression or statement.
At C:\rpit\sql_to_AD_sync.ps1:25 char:3
+   }
+   ~
Unexpected token '}' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

Looks like you have a typo on a couple of your "OR"s, really hard to notice.

It should be "-or" instead of "or", I fixed it below.

# set SQL vars
$constr = 'Server=XXX; Database=XX; Integrated Security=sspi'
$sqlCmd = 'select userid, pos_title, name_first, name_last, pos_department, con_mailstop, emp_number  from crew where active = 1'

# get according AD info as a indexable hashtable
$ADUsers = @{}
get-aduser -filter { enabled -eq $true } -properties title, SamACcountName, GivenName, Surname, Department, roomNumber, employeeID |
  % { $ADUsers += @{ $_.SamAccountName = $_ } }

# meat and potatoes
$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter ($sqlCmd, $constr)).Fill($set) | out-null

$set.Tables[0] |
  % { 
    if ($usr = $ADUsers[$_.userid])     # This is assigning the AD user object
    {
      if ($usr.Title -ne $_.pos_title -or $usr.Department -ne $_.pos_department -or $usr.roomNumber -ne $_.con_mailstop -or $usr.employeeID -ne $_.emp_number)
      {
        $usr | Set-ADUser -Title $_.Pos_Title -Department $_.pos_department -roomNumber $_.con_mailstop -employeeID $_.emp_number
      }
    } else {
      Write-Host -f yellow "*** Error: User $($_.userid) not found in Active Directory!"
    }
  }

ASKER

Nice catch Ben! Still looks like the same error coming up. I triple checked it was running the updated version

At C:\rpit\sql_to_AD_sync.ps1:18 char:81
+ ... pos_department or $usr.roomNumber -ne $_.con_mailstop or $usr.employeeID -ne $_. ...
+                    ~~
Unexpected token 'or' in expression or statement.
At C:\rpit\sql_to_AD_sync.ps1:18 char:81
+ ... pos_department or $usr.roomNumber -ne $_.con_mailstop or $usr.employeeID -ne $_. ...
+                    ~~
Missing closing ')' after expression in 'if' statement.
At C:\rpit\sql_to_AD_sync.ps1:17 char:5
+     {
+     ~
Missing closing '}' in statement block.
At C:\rpit\sql_to_AD_sync.ps1:15 char:5
+   % {
+     ~
Missing closing '}' in statement block.
At C:\rpit\sql_to_AD_sync.ps1:18 char:156
+ ... e $_.emp_number)
+                    ~
Unexpected token ')' in expression or statement.
At C:\rpit\sql_to_AD_sync.ps1:22 char:5
+     } else {
+     ~
Unexpected token '}' in expression or statement.
At C:\rpit\sql_to_AD_sync.ps1:25 char:3
+   }
+   ~
Unexpected token '}' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

Your output shows that somehow it is running the old code..

ASKER

Yikes. You're right. Not sure what the heck I was doing there, but my apologies. Ran the correct script and now it seems to have an issue with the AD attribute "roomNumber" it seems

Set-ADUser : A parameter cannot be found that matches parameter name 'roomNumber'.
At C:\RPIT\sql_to AD_sync.ps1:20 char:77
+         $usr | Set-ADUser -Title $_.Pos_Title -Department $_.pos_department -roo ...
+                                                                             ~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.ActiveDirectory.Management.Commands.SetADUser

I know this is a legit attribute as I have it populated

https://msdn.microsoft.com/en-us/library/ms679615(v=vs.85).aspx

Looks like that one attribute is not a normal AD attribute, it's an LDAP attribute, this should add it as a separate step.

#Debugging
param(
	$DebugPreference="Continue" #To turn off Debug: $DebugPreference="SilentlyContinue"
)

# set SQL vars
$constr = 'Server=XXX; Database=XX; Integrated Security=sspi'
$sqlCmd = 'select userid, pos_title, name_first, name_last, pos_department, con_mailstop, emp_number  from crew where active = 1'

# get according AD info as a indexable hashtable
$ADUsers = @{}
get-aduser -filter { enabled -eq $true } -properties title, SamACcountName, GivenName, Surname, Department, roomNumber, employeeID |
  % { $ADUsers += @{ $_.SamAccountName = $_ } }

# meat and potatoes
$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter ($sqlCmd, $constr)).Fill($set) | out-null

$set.Tables[0] | % { 
	if ($usr = $ADUsers[$_.userid]) {
		if ($usr.Title -ne $_.pos_title -or $usr.Department -ne $_.pos_department -or $usr.roomNumber -ne $_.con_mailstop -or $usr.employeeID -ne $_.emp_number) {
			Write-Debug "Setting User $($_.userid) Title, Department, EmployeeID"
			$usr | Set-ADUser -Title $_.Pos_Title -Department $_.pos_department -roomNumber $_.con_mailstop -employeeID $_.emp_number
			Write-Debug "Setting User $($_.userid) Room #"
			$usr | Set-ADUser -Add @{roomNumber="$($_.con_mailstop)"}
		}
	} else {
		Write-Warning "*** Error: User $($_.userid) not found in Active Directory!"
	}
}

Cheers :)

ASKER

That makes total sense. The output still shows the error, but maybe the debug will help:

DEBUG: Setting User XXXX Title, Department, EmployeeID
Set-ADUser : A parameter cannot be found that matches parameter name 'roomNumber'.
At C:\RPIT\sql_to AD_sync.ps1:23 char:72
+             $usr | Set-ADUser -Title $_.Pos_Title -Department $_.pos_department -roomNumb ...
+                                                                                 ~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.ActiveDirectory.Management.Commands.SetADUser
 
DEBUG: Setting User XXXX Room #

I get one of these for each user. I REALLY appreciate the help!

I accidentally left "-roomnumber" in the initial command.

#Debugging
param(
	$DebugPreference="Continue" #To turn off Debug: $DebugPreference="SilentlyContinue"
)

# set SQL vars
$constr = 'Server=XXX; Database=XX; Integrated Security=sspi'
$sqlCmd = 'select userid, pos_title, name_first, name_last, pos_department, con_mailstop, emp_number  from crew where active = 1'

# get according AD info as a indexable hashtable
$ADUsers = @{}
get-aduser -filter { enabled -eq $true } -properties title, SamACcountName, GivenName, Surname, Department, roomNumber, employeeID |
  % { $ADUsers += @{ $_.SamAccountName = $_ } }

# meat and potatoes
$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter ($sqlCmd, $constr)).Fill($set) | out-null

$set.Tables[0] | % { 
	if ($usr = $ADUsers[$_.userid]) {
		if ($usr.Title -ne $_.pos_title -or $usr.Department -ne $_.pos_department -or $usr.roomNumber -ne $_.con_mailstop -or $usr.employeeID -ne $_.emp_number) {
			Write-Debug "Setting User $($_.userid) Title, Department, EmployeeID"
			$usr | Set-ADUser -Title $_.Pos_Title -Department $_.pos_department -employeeID $_.emp_number
			Write-Debug "Setting User $($_.userid) Room #"
			$usr | Set-ADUser -Add @{roomNumber="$($_.con_mailstop)"}
		}
	} else {
		Write-Warning "*** Error: User $($_.userid) not found in Active Directory!"
	}
}

ASKER

This is fantastic Ben! Works great, but is there a way to do a replace for the roomNumber value? Currently, if someone has an existing value it will just add an additional one instead of replacing the existing value. Would it just be a matter of changing the line:

$usr | Set-ADUser -Add @{roomNumber="$($_.con_mailstop)"}

with

$usr | Set-ADUser -Replace @{roomNumber="$($_.con_mailstop)"}

Yes, that should do the needful. Glad to help. :)

ASKER

Ben I was asked to add the Telephone Numbers to the script and for some reason, it's not moving them over. I modified the script to add it and was wondering if you can double-check my work to see where I messed up.

#Debugging
param(
	$DebugPreference="SilentlyContinue" #To turn off Debug: $DebugPreference="SilentlyContinue"
)

# set SQL vars
$constr = 'Server=sqlcluster; Database=XXX; Integrated Security=sspi'
$sqlCmd = 'select userid, pos_title, name_first, name_last, pos_department, con_work_voice, con_mailstop, emp_number from crew where active = 1'

# get according AD info as a indexable hashtable
$ADUsers = @{}
get-aduser -filter { enabled -eq $true } -properties title, SamACcountName, GivenName, Surname, Department, telephoneNumber, roomNumber, employeeID |
  % { $ADUsers += @{ $_.SamAccountName = $_ } }

# meat and potatoes
$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter ($sqlCmd, $constr)).Fill($set) | out-null

$set.Tables[0] | % { 
	if ($usr = $ADUsers[$_.userid]) {
		if ($usr.Title -ne $_.pos_title -or $usr.Department -ne $_.pos_department -or $usr.roomNumber -ne $_.con_mailstop -or $usr.employeeID -ne $_.emp_number -or $usr.telephoneNumber -ne $_.con_work_voice) {
			Write-Debug "Setting User $($_.userid) Title, Department, EmployeeID"
			$usr | Set-ADUser -Title $_.Pos_Title -Department $_.pos_department -employeeID $_.emp_number
			Write-Debug "Setting User $($_.userid) Room #"
			$usr | Set-ADUser -Replace @{roomNumber="$($_.con_mailstop)"}
		}
	} else {
		Write-Warning "*** Error: User $($_.userid) not found in Active Directory!"
	}
}