Modify PS to sync from internal user DB to AD

I need to add additional AD attributes from our internal user DB to AD. I have a PowerShell already written but need a little hand on adding the additional attributes.


This currently works to get our Title and Department to sync up:


# set SQL vars
$constr = 'Server=XXX; Database=XX; Integrated Security=sspi'
$sqlCmd = 'select userid, pos_title, name_first, name_last, pos_department 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 |
  % { $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)
      {
        $usr | Set-ADUser -Title $_.Pos_Title -Department $_.pos_department
      }
    } else {
      Write-Host -f yellow "*** Error: User $($_.userid) not found in Active Directory!"
    }
  }


I want to sync two additional SQL fields named "con_mailstop" & "emp_number" to the AD attributes "roomNumber" & "employeeID" respectivley.


Would the below work to add the additional attributes?


# 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!"
    }
  }
RubinPostaerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ben Personick (Previously QCubed)Lead Network EngineerCommented:
Yes, this looks correct, what trouble are you having?

# 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!"
    }
  }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RubinPostaerAuthor Commented:
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!
0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
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.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Ben Personick (Previously QCubed)Lead Network EngineerCommented:
Glad to help :)
0
RubinPostaerAuthor Commented:
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
 

Open in new window

0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
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!"
    }
  }

Open in new window

0
RubinPostaerAuthor Commented:
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

Open in new window

0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
Your output shows that somehow it is running the old code..
0
RubinPostaerAuthor Commented:
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

Open in new window


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

https://msdn.microsoft.com/en-us/library/ms679615(v=vs.85).aspx
0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
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!"
	}
}

Open in new window


Cheers :)
0
RubinPostaerAuthor Commented:
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 #

Open in new window


I get one of these for each user. I REALLY appreciate the help!
0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
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!"
	}
}

Open in new window

0
RubinPostaerAuthor Commented:
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)"}

Open in new window



with

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

Open in new window

0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
Yes, that should do the needful.  Glad to help.  :)
1
RubinPostaerAuthor Commented:
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!"
	}
}

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.