• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 78
  • Last Modified:

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!"
    }
  }
0
RubinPostaer
Asked:
RubinPostaer
  • 8
  • 7
1 Solution
 
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
 
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now