How to use Powershell data from SQL

Hello,
This is in regards to question asked here:
https://www.experts-exchange.com/questions/29013772/Problems-creating-account-in-AD-with-Powershell-data-from-SQL.html?anchor=a42078157¬ificationFollowed=186907313#a42078157

If you see in the script posted, I have $otherAttributes being used for New-Aduser, however the same variable cannot be used for Set-Aduser as it doesn't support -OtherAttributes.
I want to have the possibility of making the changes to an existing user by using the new values from SQL where the Action Column has value Update.

Further I want to use the same scenario for New-MsolUser & Set-MsolUser.

Regards,
A
LVL 11
AcklesAsked:
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.

Jason CrawfordTransport NinjaCommented:
The post you're referencing is extremely long.  Can you just rephrase the question?
0
Chris DentPowerShell DeveloperCommented:
For the benefit of anyone joining in.

Problem:

Set-ADUser uses a different set of parameters from New-ADUser. The attribute updates need to be handled a little differently.

Summary:

The existing script performs the following actions:
    - Executes a query against SQL to generate a list of updates which should be published into Active Directory
    - Executes the New-ADUser command if a user should be created with a set of dynamically filled parameters
    - Clears the flag from the entry in the SQL database.

Code breakdown:

SQL reader
Import-Module ActiveDirectory


# Path to create users in
$path = "OU=TestUser,OU=Test,DC=Test,DC=com"

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Select statement to return new user accounts
# Needs to return "sAMAccountName" & "Password" columns
# Note: Other columns names should match AD attribute name
$sql = "Select FirstName as GivenName,
				 LastName as sn,
				 DisplayName as DisplayName,
				 samAccountName as sAMAccountName,
				 EmailAddress as mail,
				 City as l,
				 Department as Department,
				 StreetAddress as StreetAddress,
				 State as st,
				 samAccountName+'@wuestundpartner.com' as userPrincipalName,
				 PostalCode as postalcode,
				 MobilePhone as mobile,
				 OfficePhone as telephoneNumber,
				 Department as department,
				 Title as Title,
				 Office as physicalDeliveryOfficeName,
				 c as c,
				 CountryCode as co,
				 Action as Action,					
				 'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "

###########################

$cn = new-object system.data.sqlclient.sqlconnection
$cn.ConnectionString = $connectionString
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $sql
$cmd.connection = $cn
$dr = $cmd.ExecuteReader()

Open in new window

Parameter population:
$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$ActionOrdinal = $dr.GetOrdinal("Action")

while ($dr.Read())
{
	# Get value of sAMAccountName column
	$sAMAccountName = $dr.GetValue($sAMAccountNameOrdinal)
	# Get value password column (converted to secure string for New-ADUser Cmdlet)
	$password = ConvertTo-SecureString -AsPlainText $dr.GetValue($PasswordOrdinal) -Force
	# Get value of DisplayName column
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	$Action = $dr.GetValue($ActionOrdinal)
	
	#write-host "Creating user account..." $sAMAccountName
	
	$otherAttributes = New-Object System.Collections.HashTable
	
	# Create a hash table of attribute names and attribute values
	# Used to populate other attributes. 
	for ($i = 0; $i -le $colCount - 1; $i++) {
        if ($attribute -notin 'Password', 'SAMAccountName', 'Action') {
            $value = $dr.GetValue($i)
            if ($value -is [String]) {
                $value = $value.Trim()
            }
            if ([String]::IsNullOrEmpty("$value") -eq $false) {
                Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"
                $otherAttributes.Add($attribute, $value)
            }
        }
    }
}

Open in new window

Actions:
if ($Action -eq 'create')
	{
		try
		{
			Write-Host "Create branch executed for $sAMAccountName"
			New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -PasswordNeverExpires $true 
			#Write-Host "UserID $($DisplayName) created!"
		}
		catch
		{
			Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
			Write-Host "Error message: $($_.Exception.Message)"
		}
	}
	elseif ($Action -eq 'update')
	{
		Write-Host "Update is triggered for $sAMAccountName"
	}
}

Open in new window

0
Chris DentPowerShell DeveloperCommented:
And what I believe might work...
elseif ($Action -eq 'update') {
    Write-Host "Update is triggered for $sAMAccountName"

    Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes
}

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AcklesAuthor Commented:
Chris,
It does the job, but a slight change in the scenario (sorry)
When the update is made, there is a possibility of Account being Disabled,

SQL has a column: AccountIsEnabled
If I add it in the Array & put it in OtherAttributes, it doesn't accept it.

I would prefer that the value is taken from the SQL, but it's only True or False in SQL.
0
Chris DentPowerShell DeveloperCommented:
Exclude it from otherAttributes; either you using your original switch, or the the modified "-in" approach above.

Then you just need a trigger for it. I suspect the two commands used below will just skip it if the account is already in the requested state. If not, it needs a bit of tweaking to only run when a state change is required.
if ($AccountIsEnabled -eq $true) {
    Get-ADUser -Identity $SamAccountName | Enable-ADAccount
} elseif ($AccountIsEnabled -eq $false) {
    Get-ADUser -Identity $SamAccountName | Disable-ADAccount
}

Open in new window

0
AcklesAuthor Commented:
Can you please see if these lines are correct :
In hash table:  AccountisEnabled as Enabled,

Variable:
$AccountisEnabled = $dr.GetOrdinal("Enabled")

In While:
$AccountisEnabled = $dr.GetValue($AccountisEnabledOrdinal)

In Switch ($attribute)
"Enabled" { } #Ignore
0
Chris DentPowerShell DeveloperCommented:
Yes, I think that's right.
0
AcklesAuthor Commented:
Is it not possible to do it with Get-Aduser | set-Aduser?
0
AcklesAuthor Commented:
Like this:
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes   | Enable-ADAccount
0
Chris DentPowerShell DeveloperCommented:
It looks like Set-ADUser doesn't return by default.
Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Enable-ADAccount

Open in new window

0
AcklesAuthor Commented:
Like this:

elseif ($Action -eq 'update')
      {
            if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes | Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes - | Disable-ADAccount
                  
            }
            Write-Host "Update is triggered for $sAMAccountName"
            #Get-ADUser -Identity $SamAccountName  | Set-ADUser  -Replace $otherAttributes
            
      }
}
0
Chris DentPowerShell DeveloperCommented:
Scratch that, we're over-complicating it.
Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountEnabled -Server serverName

Open in new window

0
AcklesAuthor Commented:
can you please remove server name?
0
Chris DentPowerShell DeveloperCommented:
Done.
1
AcklesAuthor Commented:
I get this  ERROR: Set-ADUser : Index was out of range. Must be non-negative and less than the size of the collection.
0
Chris DentPowerShell DeveloperCommented:
That's an awesome error... chocolate teapot error.

I imagine we're back to one or more of the attributes we're attempting to modify is not-quite-right. It'll be another one at a time job I think.
0
AcklesAuthor Commented:
ha ha ha!!!
how about earlier approach of if else?
I tried that, the script ran without errors but didn't do anything....
0
Chris DentPowerShell DeveloperCommented:
It'll have the same issue if it triggers properly I imagine.
0
AcklesAuthor Commented:
Alright, then how to proceed?
0
Chris DentPowerShell DeveloperCommented:
We'll have to reduce the content of $otherAttributes again until the one causing the problem is isolated.  Same as was done with New-ADUser. Super-fun, huh?

We can, perhaps, speed the process up, like this. At least then you don't have to hack things about quite so much.
foreach ($name in $attributes.Keys) {
    Write-Host "Updating $name with $($attribute[$name])"
    $replace = @{$name = $attributes[$name]}
    Get-ADUser -Identity $SamaccountName | Set-ADUser -Replace $replace
}

Open in new window

0
AcklesAuthor Commented:
Wait,
I tried the command from console: Get-ADUser -Identity tfi | Set-ADUser -City Zürich | Disable-ADAccount

It changed the city, but didn't disable the account
So, I guess something else is to be done.
0
Chris DentPowerShell DeveloperCommented:
No, if you're using that approach you need the -PassThru parameter for Set-ADUser. Set-ADUser does not return anything by default, so no input for Disable-ADAccount.
Get-ADUser -Identity tfi | Set-ADUser -City Zürich -PassThru | Disable-ADAccount

Open in new window

0
AcklesAuthor Commented:
What if we just hard code -Enabled $true in the nested if else statement?
0
AcklesAuthor Commented:
Like this:

elseif ($Action -eq 'update')
      {
            if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes | Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $false
                 
            }
            Write-Host "Update is triggered for $sAMAccountName"
            #Get-ADUser -Identity $SamAccountName  | Set-ADUser  -Replace $otherAttributes -Enabled $true
           
      }
}
0
Chris DentPowerShell DeveloperCommented:
Now you're confusing me :-D

Since I noticed the Enabled parameter we don't need to use the split for Enable/Disable-ADAccount.
elseif ($Action -eq 'update')
      {
        Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountIsEnabled
      }
}

Open in new window

If Enabled is set to $false (or $AccountIsEnabled is set to false) the account will be disabled.

If you want to stick with Enable/Disable-ADAccount you must include the PassThru parameter for Set-ADUser. Without that there's nothing to pass to the enable / disable commands.
elseif ($Action -eq 'update')
      {
            if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Disable-ADAccount
                  
            }
            Write-Host "Update is triggered for $sAMAccountName"
            #Get-ADUser -Identity $SamAccountName  | Set-ADUser  -Replace $otherAttributes -Enabled $true
            
      }
}

Open in new window

0
AcklesAuthor Commented:
Sorry about that,
So I used the Enable-ADAccount, Disable-ADAccount like this:

elseif ($Action -eq 'update')
      {
            if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru| Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Disable-ADAccount
                  
            }
            Write-Host "Update is triggered for $sAMAccountName"            
      }

No error thrown, command runs fine, but nothing happens...


Running: Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountIsEnabled

Throws ERROR: Set-ADUser : Cannot convert 'System.String' to the type 'System.Nullable`1[System.Boolean]' required by parameter 'Enabled'.
0
Chris DentPowerShell DeveloperCommented:
Ahh I did wonder about that, your AccountIsEnabled field doesn't actually appear to be boolean (true or false). It's likely a string containing true or false?

If so, we can make it right :)
$AccountisEnabled = $dr.GetValue($AccountisEnabledOrdinal)
if ($AccountIsEnabled -is [String]) {
    $AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
}

Open in new window

0
AcklesAuthor Commented:
Now:
Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountIsEnabled

ERROR: Set-ADUser : Index was out of range. Must be non-negative and less than the size of the collection.
0
Chris DentPowerShell DeveloperCommented:
Okay, back to that wonderful error. Which is where this bit hopefully comes in:
foreach ($name in $attributes.Keys) {
    Write-Host "Updating $name with $($attribute[$name])"
    $replace = @{$name = $attributes[$name]}
    Get-ADUser -Identity $SamaccountName | Set-ADUser -Replace $replace
}

Open in new window

That is, we write off the "Enabled" parameter part as "that works" and shelve it for a few minutes while we explore setting the attributes individually.
0
AcklesAuthor Commented:
Where do I insert this? Inside the else if?
0
Chris DentPowerShell DeveloperCommented:
Yep, it replaces the content currently used to call Set-ADUser. By stepping through each in turn I hope we can quickly identify which one of the attributes is causing grief.
0
AcklesAuthor Commented:
ERROR: Get-Variable : Cannot find a variable with name 'Test'.
ERROR: +         $AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
0
Jason CrawfordTransport NinjaCommented:
Don't use a dollar sign after Get-Variable:

Get-Variable AccountIsEnabled

Open in new window

0
Chris DentPowerShell DeveloperCommented:
No, you must.

That variable is expected to hold "True" or "False". This lets us convert the string version to the value of the variables $true and $false.
0
Chris DentPowerShell DeveloperCommented:
Code example:
$HonestThisIsABool = "True"
(Get-Variable $HonestThisIsABool).Value

Open in new window

A simple cast wouldn't work in this case because...
[Boolean]"False"

Open in new window

0
AcklesAuthor Commented:
When I remove the $ sign, the part of the script doesn't execute....

Now, with the $ sign in place I get the error as above:

ERROR: Get-Variable : Cannot find a variable with name 'Test'.
SQLADUsers.ps1 (69, 24): ERROR: At Line: 69 char: 24
ERROR: +         $AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
ERROR: +                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : ObjectNotFound: (Test:String) [Get-Variable], ItemNotFoundException
ERROR:     + FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.GetVariableCommand
ERROR:
0
Chris DentPowerShell DeveloperCommented:
Hmm your Enabled field contains Test? Thats... unexpected. What other values does it hold?
0
AcklesAuthor Commented:
Now, I'm Lost.... :(
there is no value as Test, AccountIsEnabled has value False
The only place where the Test is contained is in the field FirstName....
0
Jason CrawfordTransport NinjaCommented:
If this is leading the question off topic feel free to ignore me but, I don't follow:

Capture.PNG
0
Chris DentPowerShell DeveloperCommented:
Too many concurrent threads. Put this aside for the moment, let's stick with the chocolate teapot error and get that fixed first.

Once that's done, we'll fix up the enable / disable handling.
1
AcklesAuthor Commented:
Just for the argument, I changed the FirstName to False & the script ran without errors....
However, no changes were made to the account
0
Chris DentPowerShell DeveloperCommented:
Stick with debugging Set-ADUser, I'll chat with Jason by message, too many threads otherwise :)
0
AcklesAuthor Commented:
No no, I'm not involving in that, don't worry, that is something off the topic for me....
So the only place where Test was mentioned, was in the field FirstName.
I changed it to False, then the script ran without error.
But the impact is null
0
Jason CrawfordTransport NinjaCommented:
Oh wait I see.  I don't think the (Get-Variable whatever).value is resulting in 'False':

Capture.PNG
0
AcklesAuthor Commented:
Gentlemen,
Can you please bear in mind that too many things only confuse...
@Jason,
Thanks for the inputs, but will be highly appreciated if you could translate them to current issue or how to use them in the issue at hand?

Thanks!
0
AcklesAuthor Commented:
Chris,
How about we split the command in 2 parts, like:

elseif ($Action -eq 'update')
      {
             if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes
                  Get-ADUser -Identity $SamAccountName | Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes
                  Get-ADUser -Identity $SamAccountName | Disable-ADAccount
                  
            }
}
0
Chris DentPowerShell DeveloperCommented:
That works, it is, after all your script so it should be written so it makes sense to you.
0
AcklesAuthor Commented:
I guess...
When I run the script now, it doesn't even reach the place of Else If.... seems there's a lot of code which is now conflicting...

Let me try to make sense & come back ..
0
Jason CrawfordTransport NinjaCommented:
When you're running Set-ADUser and you use the -Replace parameter it should be a hash table:

Set-ADUser whoever -Replace @{Somettribute="$otherAttributes"}

I don't know what attribute you're working with or what the value of $otherAttributes is, but Technet is pretty clear on the syntax:

https://technet.microsoft.com/en-us/library/ee617215.aspx

Capture.PNG
0
AcklesAuthor Commented:
The variable $AccountIsEnabled has value First, which I fail to understand from where is it getting......
0
Jason CrawfordTransport NinjaCommented:
Well that's why God created breakpoints
0
AcklesAuthor Commented:
& Jason you think I was so favourite of God I would be talking to Experts ;)
1
AcklesAuthor Commented:
All right,
Here's the code Gentlemen, I have gone BLIND trying to figure out ...
Please help....

Import-Module ActiveDirectory

# Path to create users in
$path = "OU=TestUser,OU=test,DC=test,DC=com"

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Select statement to return new user accounts
# Needs to return "sAMAccountName" & "Password" columns
# Note: Other columns names should match AD attribute name
$sql = "Select FirstName as GivenName,
				LastName as sn,
				 DisplayName as DisplayName,
				 samAccountName as sAMAccountName,
				 EmailAddress as mail,
				 City as l,
				 State as st,
				 c as c,
				 CountryCode as co,
				 Department as Department,
				 StreetAddress as StreetAddress,
				 samAccountName+'@test.com' as userPrincipalName,
				 PostalCode as postalcode,
				 Title as Title,
				 Department as department,
				 Office as physicalDeliveryOfficeName,
				 OfficePhone as telephoneNumber,
				 MobilePhone as mobile,
				 Action as Action,			
				 AccountisEnabled as Enabled,
				 '11Passw0rd' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "

###########################

$cn = new-object system.data.sqlclient.sqlconnection
$cn.ConnectionString = $connectionString
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $sql
$cmd.connection = $cn
$dr = $cmd.ExecuteReader()

$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$ActionOrdinal = $dr.GetOrdinal("Action")
$AccountisEnabled = $dr.GetOrdinal("Enabled")

while ($dr.Read())
{
	# Get value of sAMAccountName column
	$sAMAccountName = $dr.GetValue($sAMAccountNameOrdinal)
	# Get value password column (converted to secure string for New-ADUser Cmdlet)
	$password = ConvertTo-SecureString -AsPlainText $dr.GetValue($PasswordOrdinal) -Force
	# Get value of DisplayName column
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	# Get value of Action column
	$Action = $dr.GetValue($ActionOrdinal)
	# Get value of AccountisEnabled column
	$AccountisEnabled = $dr.GetValue($AccountisEnabledOrdinal)
	<#if ($AccountIsEnabled -is [String])
	{
		$AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
	}
	#>
	write-host "Creating user account..." $sAMAccountName
	
	$otherAttributes = New-Object System.Collections.HashTable
	
	# Create a hash table of attribute names and attribute values
	# Used to populate other attributes. 
	
	for ($i = 0; $i -le $colCount - 1; $i++)
	{
		$attribute = $dr.GetName($i)
		
		switch ($attribute)
		{
			"Password"{ } #Ignore
			"SAMAccountName" { } #Ignore
			"Action" { } #Ignore
			"Enabled" { } #Ignore
			default
			{
				#$otherAttributes.Add($attribute, $dr.GetValue($i))
				$value = $dr.GetValue($i)
				if ($value -is [String])
				{
					$value = $value.Trim()
				}
				if ([String]::IsNullOrEmpty("$value") -eq $false)
				{
					#Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"
					$otherAttributes.Add($attribute, $value)
				}
			}
		}
		
	}
	# Capture Error for not resetting SQL Values in Action
	$Error.clear()
	# Create Active Directory User Account
	if ($Action -eq 'create')
	{
		try
		{
			Write-Host "Create branch executed for $sAMAccountName"
			New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -Enable $true -otherAttributes $otherAttributes -AccountPassword $password -PasswordNeverExpires $true 
			
			#Write-Host "UserID $($DisplayName) created!"
		}
		catch
		{
			Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
			
			Write-Host "Error message: $($_.Exception.Message)"
		}
	}
	elseif ($Action -eq 'update')
	{
		Write-Host "update elseif"
		Write-Host "this is value $AccountisEnabled"
		 if ($AccountIsEnabled -eq 'true')
		{
			Write-Host "Update true"
			#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru| Enable-ADAccount 
			#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes 
			#Get-ADUser -Identity $SamAccountName | Enable-ADAccount
		}
		elseif ($AccountIsEnabled -eq 'false')
		{
			Write-Host "Update false"
			#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Disable-ADAccount 
			Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes 
			Get-ADUser -Identity $SamAccountName | Disable-ADAccount
			
		}
		<#
		foreach ($name in $attributes.Keys)
		{
			Write-Host "Updating $name with $($attribute[$name])"
			$replace = @{ $name = $attributes[$name] }
			Get-ADUser -Identity $SamaccountName | Set-ADUser -Replace $replace
		}#>
		#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountIsEnabled
		#Write-Host "Update is triggered for $sAMAccountName"		
	}
	##############
		#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountEnabled 
		#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled 
		#Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"
	}

$dr.Close()
# If the code runs successfully, then Clear the value from SQL in Action
<#	if (!$Error)
{
	$updateqry = "update dbo.GetActiveDirectoryUsers set Action = ' ' where Action = 'create' OR Action ='update' ;"
	$cmd.CommandText = $updateqry
	$cmd.ExecuteNonQuery()
}#>
$cn.Close()

Open in new window

0
AcklesAuthor Commented:
First error found: $AccountisEnabledOrdinal = $dr.GetOrdinal("Enabled")
0
Chris DentPowerShell DeveloperCommented:
Does that fix retrieval of the enabled / disabled state?
0
AcklesAuthor Commented:
Chris,
The value is showing False!
Let me go bit further...
0
Jason CrawfordTransport NinjaCommented:
I would put a breakpoint before damn near every variable and track their values.  Personally I use ISE Steroids and the Variable Monitor Addon, but there is a standalone variable module for ISE available on the tools website:

https://social.technet.microsoft.com/wiki/contents/articles/2969.windows-powershell-ise-add-on-tools.aspx

Developing good debugging techniques and writing in an abundance of logging goes a long way in these situations.
0
Chris DentPowerShell DeveloperCommented:
I can't disagree. I use VS Code, probably much the same (it also has a watch interface). It's free, I like not paying for ISE Steriods.
0
Jason CrawfordTransport NinjaCommented:
I didn't.  I just blogged about it and got a free license under Tobias' Helping Hands Initiative :)
0
AcklesAuthor Commented:
Thanks Jason,
I have put it on my to-do list !

@Chris,
Seems to Disable the account!!!
0
AcklesAuthor Commented:
However, now the create doesn't execute at all :(
The code is as posted last...
0
AcklesAuthor Commented:
Ok, this is strange, but the script doesn't execute if ($Action -eq 'create')
or if ($Action -eq 'update')

The value of $Action is captured correctly as create & update?
0
Chris DentPowerShell DeveloperCommented:
Your code is getting too complex, we need to split it up a bit. Reduce the scope, make debugging a bit easier. One sec.
0
AcklesAuthor Commented:
This is the latest code:

Import-Module ActiveDirectory

# Path to create users in
$path = "OU=TestUser,OU=test,DC=test,DC=com"

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Select statement to return new user accounts
# Needs to return "sAMAccountName" & "Password" columns
# Note: Other columns names should match AD attribute name
$sql = "Select FirstName as GivenName,
				LastName as sn,
				 DisplayName as DisplayName,
				 samAccountName as sAMAccountName,
				 EmailAddress as mail,
				 City as l,
				 State as st,
				 c as c,
				 CountryCode as co,
				 Department as Department,
				 StreetAddress as StreetAddress,
				 samAccountName+'@test.com' as userPrincipalName,
				 PostalCode as postalcode,
				 Title as Title,
				 Department as department,
				 Office as physicalDeliveryOfficeName,
				 OfficePhone as telephoneNumber,
				 MobilePhone as mobile,
				 Action as Action,			
				 AccountisEnabled as Enabled,
				 '11Passw0rd' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "

###########################

$cn = new-object system.data.sqlclient.sqlconnection
$cn.ConnectionString = $connectionString
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $sql
$cmd.connection = $cn
$dr = $cmd.ExecuteReader()

$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$ActionOrdinal = $dr.GetOrdinal("Action")
$AccountisEnabledOrdinal = $dr.GetOrdinal("Enabled")

while ($dr.Read())
{
	# Get value of sAMAccountName column
	$sAMAccountName = $dr.GetValue($sAMAccountNameOrdinal)
	# Get value password column (converted to secure string for New-ADUser Cmdlet)
	$password = ConvertTo-SecureString -AsPlainText $dr.GetValue($PasswordOrdinal) -Force
	# Get value of DisplayName column
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	# Get value of Action column
	$Action = $dr.GetValue($ActionOrdinal)
	# Get value of AccountisEnabled column
	$AccountisEnabled = $dr.GetValue($AccountisEnabledOrdinal)
	if ($AccountIsEnabled -is [String])
	{
		$AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
	}
	write-host "Creating user account..." $sAMAccountName
	
	$otherAttributes = New-Object System.Collections.HashTable
	
	# Create a hash table of attribute names and attribute values
	# Used to populate other attributes. 
	
	for ($i = 0; $i -le $colCount - 1; $i++)
	{
		$attribute = $dr.GetName($i)
		
		switch ($attribute)
		{
			"Password"{ } #Ignore
			"SAMAccountName" { } #Ignore
			"Action" { } #Ignore
			"Enabled" { } #Ignore
			default
			{
				#$otherAttributes.Add($attribute, $dr.GetValue($i))
				$value = $dr.GetValue($i)
				if ($value -is [String])
				{
					$value = $value.Trim()
				}
				if ([String]::IsNullOrEmpty("$value") -eq $false)
				{
					$otherAttributes.Add($attribute, $value)
				}
			}
		}
		
	}
	Write-Host "Action = $Action"
	# Capture Error for not resetting SQL Values in Action
	$Error.clear()
	
	# Create Active Directory User Account
	if ($Action -eq 'create')
	{
		Write-Host "Create executed"
		try
		{
			Write-Host "Create branch executed for $sAMAccountName"
			New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -Enable $true -otherAttributes $otherAttributes -AccountPassword $password -PasswordNeverExpires $true 
			
			Write-Host "UserID $($DisplayName) created!"
		}
		catch
		{
			Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
			
			Write-Host "Error message: $($_.Exception.Message)"
		}
	}
	elseif ($Action -eq 'update')
	{
		if ($AccountIsEnabled -eq $true)
		{
			Write-Host "Update true"
			Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru| Enable-ADAccount
		}
		elseif ($AccountIsEnabled -eq $false)
		{
			Write-Host "Update false"
			Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Disable-ADAccount 
		}
	}
}

$dr.Close()
# If the code runs successfully, then Clear the value from SQL in Action
<#	if (!$Error)
{
	$updateqry = "update dbo.GetActiveDirectoryUsers set Action = ' ' where Action = 'create' OR Action ='update' ;"
	$cmd.CommandText = $updateqry
	$cmd.ExecuteNonQuery()
}#>
$cn.Close()

Open in new window

1
Chris DentPowerShell DeveloperCommented:
Are you happy to do a bit of component testing for me? it'll be a bit of a repeat of work you've already done. The goal is to split it down into far more manageable chunks.

The smaller chunks make targeted debugging a lot easier.
0
AcklesAuthor Commented:
Sure, I can also learn something for future!
0
Chris DentPowerShell DeveloperCommented:
Okay, the goal of the first part is to get clean things out of the SQL database. All this function does is execute the SQL query and convert the output into an object (one object per row).

Once this has run, we should have values in the variable $usersToProcess. Assuming the code works, by writing it in this style we have a reusable function for performing queries against an SQL database.
function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)
                    $value = $reader.GetValue($i)
                    
                    if ($value -is [DBNull]) {
                        $value = $null
                    }
                    if ($value -is [String]) {
                        $value = $value.Trim()
                    }

                    $psObject | Add-Member $name $value
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Department,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

Open in new window

0
AcklesAuthor Commented:
Alright, I put this in new script, to start from Scratch...
So far when I run I only get : ERROR: Add-Member : Cannot add a member with the name "department" because a member with that name already exists. If you want to overwrite the member anyway, use  the Force parameter to overwrite it.
0
Chris DentPowerShell DeveloperCommented:
That only happens if there's more than one column with same name in the query. And now I look at the query, there is.

Can we lose the repetition of Department in the query? I had to check to make sure I didn't add that :)
0
Chris DentPowerShell DeveloperCommented:
We'll have a handler for the condition as well I think. The example below removes the duplicated value from the query as well.
function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String]) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

Open in new window

0
AcklesAuthor Commented:
No Error, only You cannot call a method on a null-valued expression.
0
Chris DentPowerShell DeveloperCommented:
Can you show me the full error message?

Updated function to account for what might be the most likely source of the error.
function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

Open in new window

0
AcklesAuthor Commented:
This ran without any errors.
0
Chris DentPowerShell DeveloperCommented:
Okay, but does $usersToProcess have anything in it? :)
0
AcklesAuthor Commented:
Chris,
I lost you, the second function you gave has no HashTable?
Was I supposed to add it to First function?
0
AcklesAuthor Commented:
My variables are like this:
"Select FirstName as GivenName,
                        LastName as sn,
                         DisplayName as DisplayName,
                         samAccountName as sAMAccountName,
                         EmailAddress as mail,
                         City as l,
                         State as st,
                         c as c,
                         CountryCode as co,
                         Department as Department,
                         StreetAddress as StreetAddress,
                         samAccountName+'@wuestundpartner.com' as userPrincipalName,
                         PostalCode as postalcode,
                         Title as Title,
                         Department as department,
                         Office as physicalDeliveryOfficeName,
                         OfficePhone as telephoneNumber,
                         MobilePhone as mobile,
                         Action as Action,                  
                         AccountisEnabled as Enabled,
                         '11Passw0rd' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "


I added them under your second function & got the same "You cannot call a method on a null-valued expression"
0
Chris DentPowerShell DeveloperCommented:
The goal if this command is to get information out of the database. It does absolutely nothing else, it's entire job is defined by that.

Once we've got a valid representation of the table we'll move onto the next step, defining how to call New-ADUser with as little work as possible.
0
AcklesAuthor Commented:
ok, then if i run the second function, it runs without errors.
0
Chris DentPowerShell DeveloperCommented:
Step back a moment, all we're doing with this function is running the database query. The expected output is a set of objects representing what we had in the database.

If it's run exactly as above this command should show us everything we managed to scrape out:
$usersToProcess | Out-GridView

Open in new window

The idea is to separate the database operation from the rest. Get the database query and it's output right and the work the next part must do is much more straight-forward.
0
AcklesAuthor Commented:
should I add this at bottom?
0
AcklesAuthor Commented:
or inside the open connection?
0
Chris DentPowerShell DeveloperCommented:
At the end. The function, if it works, is sacrosanct, it should not be changed at all. We pass the $sql and $connectionString variables in as parameters.
0
AcklesAuthor Commented:
I added after the close of function, but no grid view
0
Chris DentPowerShell DeveloperCommented:
Did you remove the code I had to call the function?

It won't do anything on its own.

This is the code I'm hoping you'll run for me (after fixing the connection string towards the bottom):
function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

$usersToProcess | Out-GridView

Open in new window

0
AcklesAuthor Commented:
What do you mean: (after fixing the connection string towards the bottom)?

If i run the code as it is, i still get : You cannot call a method on a null-valued expression.
0
Chris DentPowerShell DeveloperCommented:
Test version which should give a line number. I need the exact error (the whole thing), not just the descriptive message. Coding the SQL query parser from memory I'm afraid, no servers I can test against.
function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    # try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    # } catch {
    #    throw
    #} finally {
    #    if ($SqlConnection.State -eq 'Opened') {
    #        $SqlConnection.Close()
    #    }
    #}
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

$usersToProcess | Out-GridView

Open in new window

0
AcklesAuthor Commented:
Sorry about that, here is the error:
ERROR: You cannot call a method on a null-valued expression.
SQL-AD-O365-LF.ps1 (29, 9): ERROR: At Line: 29 char: 9
ERROR: +                 if (-not $psObject.Properties.Item($name))
ERROR: +                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
ERROR:     + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
ERROR: You cannot call a method on a null-valued expression.

It did open the grid view for a second
0
Chris DentPowerShell DeveloperCommented:
Awesome, thanks. Silly mistake (on my part, of course) fixed below.
function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

$usersToProcess | Out-GridView

Open in new window

0
AcklesAuthor Commented:
Alright, no errors, grid-view flashes & then goes away!
0
Chris DentPowerShell DeveloperCommented:
Ahh you're running it in a script? I'm more of a paste into the console...

Not to worry, let's drop it to a file instead. We'll definitely be able to view that.

This:
$usersToProcess | Out-GridView

Open in new window

Becomes:
$usersToProcess | Export-Csv usersToProcess.csv -NoTypeInformation

Open in new window

This step is just to verify we're getting something useful back from SQL.
0
AcklesAuthor Commented:
No worries, I ran it from console & opens Grid-view.
I see all the 3 users I have staged for Testing!!!
0
Chris DentPowerShell DeveloperCommented:
Awesome, just what we needed.

Righto, we've happily separated the database read from the rest, that's great because now we can introduce two more process-specific functions. We'll start with the function to create the new user.
function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Use as many named parameters as possible
            foreach ($property in $UserInformation.PSObject.Properties | Where-Object Name -notin 'Action', 'Password') {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            New-ADUser @params -WhatIf
        }
    }
}

Open in new window

We use this function like this:
$usersToProcess | NewADUser

Open in new window

At the  moment it won't make changes, that's because I added the WhatIf parameter to New-ADUser (at the bottom of the function). It should show you a blob of stuff which shows us how it's managed to fill the parameters for New-ADUser (or not if I made mistakes).
1
AcklesAuthor Commented:
NO Errors so far
Shall I remove whatIf?
0
Chris DentPowerShell DeveloperCommented:
It should have dumped a blob of text to the screen. Did that pop up?

By all means remove WhatIf and give it a try. I'd be moderately surprised if I got it right first time :)
0
AcklesAuthor Commented:
Ok I removed the WhatIf & it shows the Grid-View
then I added at the bottom: $usersToProcess | New-ADUser
& got these errors:

RROR: New-ADUser : The input object cannot be bound because it did not contain the information required to bind all mandatory parameters:  Name
SQL-AD-O365-LF.ps1 (146, 19): ERROR: At Line: 146 char: 19
ERROR: + $usersToProcess | New-ADUser
ERROR: +                   ~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (@{GivenName=Fir...ord=11Passw0rd}:PSObject) [New-ADUser], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : InputObjectMissingMandatory,Microsoft.ActiveDirectory.Management.Commands.NewADUser
0
Chris DentPowerShell DeveloperCommented:
Ahh I see, I missed that you'd filled that from DisplayName. It's fixed here.
function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Use as many named parameters as possible
            foreach ($property in $UserInformation.PSObject.Properties | Where-Object Name -notin 'Action', 'Password') {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            New-ADUser @params
        }
    }
}

Open in new window

0
AcklesAuthor Commented:
I ran it as you have posted, it throws no error, however no account is created or updated.
Do I have to add
$usersToProcess | New-ADUser
?
0
AcklesAuthor Commented:
Because when I add, I get the following:

ERROR: New-ADUser : The input object cannot be bound because it did not contain the information required to bind all mandatory parameters:  Name
SQL-AD-O365-LF.ps1 (150, 19): ERROR: At Line: 150 char: 19
ERROR: + $usersToProcess | New-ADUser
ERROR: +                   ~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (@{GivenName=Fir...ord=11Passw0rd}:PSObject) [New-ADUser], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : InputObjectMissingMandatory,Microsoft.ActiveDirectory.Management.Commands.NewADUser
ERROR:
0
Chris DentPowerShell DeveloperCommented:
It's a case of update that function in the main script then re-run it. The whole things looks like this now. Do notice that I've intentionally omitted the hyphen from the last line. This is so it uses our function as a wrapper, the wrapper (hopefully) fills all the parameters New-ADUser needs.
#Requires -Module ActiveDirectory

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Use as many named parameters as possible
            foreach ($property in $UserInformation.PSObject.Properties | Where-Object Name -notin 'Action', 'Password') {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            New-ADUser @params
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

$usersToProcess | NewADUser

Open in new window

0
AcklesAuthor Commented:
where are you importing module active directory?
0
Chris DentPowerShell DeveloperCommented:
Right at the top, the #Requires -Module statement will load it. If you're not comfortable with that you can re-instate Import-Module ActiveDirectory. It can go at the top of the script.
0
AcklesAuthor Commented:
Sorry, but it throws error:

ERROR: New-ADUser : Cannot convert 'System.String' to the type 'System.Nullable`1[System.Boolean]' required by parameter 'Enabled'.
SQL-AD-O365-LF.ps1 (112, 15): ERROR: At Line: 112 char: 15
ERROR: +             New-ADUser @params
ERROR: +                        ~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [New-ADUser], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.ActiveDirectory.Management.Commands.NewADUser

It does show all the attributes of the user:
Creating user with the following information
{
    "AccountPassword":  {
                            "Length":  10
                        },
    "userPrincipalName":  "tno@test.com",
    "DisplayName":  "Test Now",
    "sAMAccountName":  "tno",
    "Name":  "Test Now",
    "Department":  "Team Z",
    "Title":  "Re",
    "GivenName":  "Test",
    "OtherAttributes":  {
                            "st":  "Zürich",
                            "mobile":  "",
                            "co":  "756",
                            "mail":  "test.now@test.com",
                            "l":  "Zürich",
                            "telephoneNumber":  "+4 45",
                            "c":  "CH",
                            "physicalDeliveryOfficeName":  "",
                            "sn":  "Now"
                        },
    "postalcode":  "8001",
    "StreetAddress":  " 5",
    "Path":  "OU=TestUser,OU=test,DC=test,DC=com",
    "Enabled":  "True"
}
0
AcklesAuthor Commented:
Chris,
It still is stuck at Enabled!
0
Chris DentPowerShell DeveloperCommented:
Ah yes, sorry I forgot to inject that update. A minor tweak happily.

I've got to catch a train, I'll pick it up when I get home.
0
AcklesAuthor Commented:
No worries, I am leaving too.
If you get time, have a look, I can only check tomorrow.

Thanks a lot Chris & have a Great Evening!!!
0
Chris DentPowerShell DeveloperCommented:
Head-start on tomorrow.

Before we run the NewADUser function we're going to apply what amounts to a cleaning filter to $usersToProcess to fix that Enabled value again. This does not belong in Invoke-SqlQuery because it's a transformation we're applying to the data set (not something the SQL query did wrong).
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

Open in new window

This does not need assigning, it updates values in the array in memory.

And the rest of the updates. The code below contains the following changes:
  • Clean Enabled property (the snippet immediately above)
  • NewADUser will no longer set attributes with no value
  • SetADUser has been added
  • RemoveAction has been added
  • All functions have error handling
  • Every action taken writes to a Status field either with OK or a failure notice an any error message

It's longer than the original, but this gives us the opportunity to debug at each distinct stage. I fully expect there will still be errors, but I hope we have better tools to break down the errors now.

This is not written to die at the first error ($ErrorActionPreference = 'Stop'). It does as much work as it can and will only take an account out of the database "queue" if it succeeds. I include this notice because there's a lot of community pressure behind that style of error handling, I'm not sold on it.
#Requires -Module ActiveDirectory

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | Where-Object { $_.Name -notin 'Action', 'Password', 'Status' -and $null -ne $_.Value }

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $replace = @{}
            $command = Get-Command Set-ADUser

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | Where-Object { $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and $null -ne $_.Value }

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    param(
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *, Status

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

Open in new window

0
Jason CrawfordTransport NinjaCommented:
Small suggestion...I notice several try/catch/finally blocks, but I'm not 100% sure the commands in the try block wouldn't generate non-terminating errors so I set the $ErrorActionPreference.  I also started a transcript log and wrapped everything up in a begin/process/end.

#Requires -Module ActiveDirectory
#Requires -Version 3.0

begin {
  $date = Get-Date -Format 'yyyyMMddHHmm'
  $currenteap = $ErrorActionPreference
  $ErrorActionPreference = 'Stop'
  Start-Transcript -Path "$env:USERPROFILE\Desktop\transcript_$date.log"
}

process {
  function Invoke-SqlQuery {
    param(
      [String]$Query,

      [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
      $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = $ConnectionString
      $SqlConnection.Open()

      $SqlCommand = $SqlConnection.CreateCommand()
      $SqlCommand.CommandText = $Query
    
      $reader = $SqlCommand.ExecuteReader()

      if ($reader.HasRows) {
        while ($reader.Read()) {
          $psObject = New-Object -TypeName PSObject
          for ($i = 0; $i -lt $reader.FieldCount; $i++) {
            $name = $reader.GetName($i)

            if (-not $psObject.PSObject.Properties.Item($name)) {
              $value = $reader.GetValue($i)
                        
              if ($value -is [DBNull]) {$value = $null}
              if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {$value = $value.Trim()}

              $psObject | Add-Member $name $value
            }
          }
          $psObject
        }
      }

      $reader.Close()
    } catch {throw} finally {
      if ($SqlConnection.State -eq 'Opened') {$SqlConnection.Close()}
    }
  }

  function NewADUser {
    param (
      [Parameter(ValueFromPipeline = $true)]
      [PSObject]$UserInformation,

      [String]$Path = 'OU=TestUser,OU=test,DC=test,DC=com'
    )

    process {
      if ($UserInformation.Action -eq 'create') {
        $params = @{
          Path            = $Path
          AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
        }
        $otherAttributes = @{}
        $command = Get-Command New-ADUser

        # Create a list of properties (from UserInformation) which will be written to the new user
        $propertiesToSet = $UserInformation.PSObject.Properties | Where-Object -FilterScript { $_.Name -notin 'Action', 'Password', 'Status' -and $null -ne $_.Value }

        # Use as many named parameters as possible
        foreach ($property in $propertiesToSet) {
          if ($command.Parameters.ContainsKey($property.Name)) {$params.($property.Name) = $property.Value} else {$otherAttributes.Add($property.Name, $property.Value)}
        }
            
        # Load everything else into OtherAttributes
        if ($otherAttributes.Count -gt 0) {$params.OtherAttributes = $otherAttributes}
        if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {$params.Name = $UserInformation.DisplayName}

        Write-Host -Object 'Creating user with the following information'
        Write-Host -Object ($params | ConvertTo-Json -Depth 3)

        try {
          New-ADUser @params -ErrorAction Stop
          $UserInformation.Status = 'OK'
        } catch {
          $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

          Write-Error -ErrorRecord $_
        }
      }
    }
  }

  function SetADUser {
    param (
      [Parameter(ValueFromPipeline = $true)]
      [PSObject]$UserInformation
    )

    process {
      if ($UserInformation.Action -eq 'update') {
        $params = @{
          Identity = $_.SamAccountName
        }
        $replace = @{}
        $command = Get-Command Set-ADUser

        # Create a list of properties (from UserInformation) which will be set on the existing user
        $propertiesToSet = $UserInformation.PSObject.Properties | Where-Object -FilterScript { $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and $null -ne $_.Value }

        # Use as many named parameters as possible
        foreach ($property in $propertiesToSet) {
          if ($command.Parameters.ContainsKey($property.Name)) {$params.($property.Name) = $property.Value} else {$replace.Add($property.Name, $property.Value)}
        }
            
        # Load everything else into OtherAttributes
        if ($replace.Count -gt 0) {$params.Replace = $replace}
        if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {$params.Name = $UserInformation.DisplayName}

        Write-Host -Object 'Updating user with the following information'
        Write-Host -Object ($params | ConvertTo-Json -Depth 3)

        try {
          Set-ADUser @params -ErrorAction Stop
          $UserInformation.Status = 'OK'
        } catch {
          $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

          Write-Error -ErrorRecord $_
        }
      }
    }
  }

  function RemoveAction {
    param(
      [Parameter(ValueFromPipeline = $true)]
      [PSObject]$UserInformation
    )

    begin {
      $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = $ConnectionString
      $SqlConnection.Open()
    }

    process {
      if ($UserInformation.Status -eq 'OK') {
        try {
          $SqlCommand = $SqlConnection.CreateCommand()
          $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
          $SqlCommand.ExecuteNonQuery()
        } catch {Write-Error -ErrorRecord $_}
      }
    }

    end {
      $SqlConnection.Close()
    }
  }

  #
  # Main
  #

  # Connection string to SQL Server database
  $ConnectionString = 'Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;'

  # Query
  $sql = "Select FirstName as GivenName,
    LastName as sn,
    DisplayName,
    samAccountName,
    EmailAddress as mail,
    City as l,
    State as st,
    c,
    CountryCode as co,
    Department,
    StreetAddress,
    samAccountName+'@test.com' as userPrincipalName,
    PostalCode,
    Title,
    Office as physicalDeliveryOfficeName,
    OfficePhone as telephoneNumber,
    MobilePhone as mobile,
    Action,            
    AccountisEnabled as Enabled,
    '11Passw0rd' as Password
  FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

  # A status field is added to each entry. This is used to persistently record the result of our commands.
  $usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $ConnectionString |
  Select-Object -Property *, Status

  # Update any fields which need modification
  $usersToProcess | ForEach-Object -Process {$_.Enabled = (Get-Variable -Name $_.Enabled).Value}

  # Commit the changes to AD
  # NewADUser internally filters usersToProcess on Action = Create
  $usersToProcess | NewADUser
  # SetADUser internally filters usersToProcess on Action = Update
  $usersToProcess | SetADUser

  # Update database
  # RemoveAction internally filters usersToProcess on Status = OK
  $usersToProcess | RemoveAction
}

end {
  $ErrorActionPreference = $currenteap
  Stop-Transcript
}

Open in new window

0
Chris DentPowerShell DeveloperCommented:
hahaha... see community pressure.

I explicitly do *not* want to die on every single error and therefore recommend that ErrorActionPreference is *not* globally set. Doing so causes Write-Error to throw and kill the entire process when really a single operation needs to be trapped.

Non-terminating errors are created when individual changes fail a command (like New-ADUser) can fail for a myriad of reasons. The error messages are also captured in the status property of each object.

Error are handled in small try-catch blocks and written to a status object. Write-Error is used to communicate the error to anyone watching / transcripting.

With respect to begin/process/end. The main script is not processing a pipeline. Begin/process/end are an unnecessary complication, they're not be used, they're just clutter.
0
Jason CrawfordTransport NinjaCommented:
Well that single hypothetical process you're referring to could very easily have a domino effect that causes a myriad of other issues.  Shouldn't your goal be to account for as many conceivable errors as you can?  I notice you did not address the error action at all, so if a non-terminating error occurs in one of the try blocks, it makes the catch block not just clutter, but useless on a level usually reserved for Project Managers.  

Man I'll defer to you everytime, and I readily admit you can run laps around me here so don't take it as pressure.  Take it as just me suggesting alternate ideas and learning from an authority on the subject.  We're already about 100 threads deep here what's a few more?  We're doing this for free so our motivation is the quality of the help we provide both to the OP and each other, right?  

By the way speaking of authorities, I've seen Jeffery Snover and Ed Wilson both use begin/process/end in a similar manner and it was very effective when used in conjunction with a return statement.
0
Chris DentPowerShell DeveloperCommented:
This would be a very, very significant detour from the content of the (very long) thread. I'm totally happy to discuss it, but it is an important topic and, if it's worth discussing, it's worth a question :)

The try / catch blocks above isolate the single points I think might die. There are a very small number of PS commands in the script, only PS commands throw non-terminating errors. All are isolated and tagged with ErrorAction Stop.

The point of error control in this script is to avoid throwing a fat terminating error every time a single user operation goes a bit wrong because an error with a single user does not necessarily represent a big problem. Same applies if you were making a change across 1000 servers, you'd be annoyed if the script completely bombed half way through because just one server failed.

Here captures it nicely for me:

https://github.com/PoshCode/PowerShellPracticeAndStyle/blob/master/Best%20Practices/Error%20Handling.md

Ideally, whatever command or code you think might bomb should be dealing with one thing: querying one computer, deleting one file, updating one user. That way, if an error occurs, you can handle it and then get on with the next thing.

We have control around the "one" operation. The controlling script surrounding that is left as a reporting vehicle for when things do go wrong. And they will with this kind of operation, on a semi-regular basis. That they do is not necessarily a show stopper.

Begin / process / end can be used in whatever way is right for a situation. But I'd argue with everyone (including Ed and Jeffery) that they're clutter unless there's something achieved by using them. You can see I use begin / process / end within the script, in the commands processing pipeline input, it's not that I don't see the value when used properly.
0
AcklesAuthor Commented:
Wow!!!
That was lot of Brain Storming ....

Morning Guys,

Chris,
Thanks, I ran the code & got the following:

New-ADUser:
Creating user with the following information
{
    "AccountPassword":  {
                            "Length":  10
                        },
    "userPrincipalName":  "tse@test.com",
    "DisplayName":  "Test Second",
    "sAMAccountName":  "tse",
    "Name":  "Test Second",
    "Department":  "Team Z",
    "Title":  "Andreas",
    "GivenName":  "Test",
    "OtherAttributes":  {
                            "st":  "Zürich",
                            "mobile":  "",
                            "co":  "756",
                            "mail":  "test.second@test.com",
                            "l":  "Zürich",
                            "telephoneNumber":  "",
                            "c":  "CH",
                            "physicalDeliveryOfficeName":  "",
                            "sn":  "Second"
                        },
    "postalcode":  "8001",
    "StreetAddress":  " 5",
    "Path":  "OU=TestUser,OU=test,DC=test,DC=com",
    "Enabled":  false
}

ERROR: NewADUser : The server is unwilling to process the request
SQL-AD-O365-LF.ps1 (270, 19): ERROR: At Line: 270 char: 19
ERROR: + $usersToProcess | NewADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (CN=Test Second,......,DC=com:String) [Write-Error], ADException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,NewADUser
ERROR:

Set-ADUser

Updating user with the following information
{
    "postalcode":  "60311",
    "Enabled":  false,
    "Replace":  {
                    "st":  "Frankfurt",
                    "mobile":  "",
                    "co":  "276",
                    "mail":  "test.first@test.com",
                    "l":  "Frankfurt",
                    "telephoneNumber":  "",
                    "c":  "DE",
                    "physicalDeliveryOfficeName":  "",
                    "sn":  "Test"
                },
    "userPrincipalName":  "tfi@test.com",
    "GivenName":  "First",
    "Name":  "Test First",
    "Identity":  "tfi",
    "StreetAddress":  "Am ",
    "Title":  " ",
    "Department":  "Team Z",
    "DisplayName":  "Test First"
}
ERROR: SetADUser : A parameter cannot be found that matches parameter name 'Name'.
SQL-AD-O365-LF.ps1 (272, 19): ERROR: At Line: 272 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [Write-Error], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : NamedParameterNotFound,SetADUser
ERROR:
0
Chris DentPowerShell DeveloperCommented:
Good stuff, I realised on the way here that there's a bug in my attempt to prevent blank values sneaking in.  This attempts to fix that again.
#Requires -Module ActiveDirectory

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status' -and 
                    $null -ne $_.Value -and
                    ($Value -isnot [String] -or $Value.Trim() -ne '')
                }


            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $replace = @{}
            $command = Get-Command Set-ADUser

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and 
                    $null -ne $_.Value -and
                    ($Value -isnot [String] -or $Value.Trim() -ne '')
                }

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    param(
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *, Status

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

Open in new window

0
AcklesAuthor Commented:
sorry Chris,
ERROR: NewADUser : The server is unwilling to process the request
SQL-AD-O365-LF.ps1 (280, 19): ERROR: At Line: 280 char: 19
ERROR: + $usersToProcess | NewADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (CN=Test Second,...dpartner,DC=com:String) [Write-Error], ADException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,NewADUser
ERROR:
0
AcklesAuthor Commented:
for set-aduser

ERROR: SetADUser : A parameter cannot be found that matches parameter name 'Name'.
SQL-AD-O365-LF.ps1 (282, 19): ERROR: At Line: 282 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [Write-Error], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : NamedParameterNotFound,SetADUser
ERROR:
0
Chris DentPowerShell DeveloperCommented:
Cool, looking better. I wonder where it found the Name parameter... odd, that was the point in dynamically binding. One tick, let me install the AD module.
0
AcklesAuthor Commented:
Awesome!!!
0
Chris DentPowerShell DeveloperCommented:
The next iteration. Parameter discovery is a little more targeted now. We're still dynamically discovering and binding, but I've limited the scope to individual parameter sets (as I should have done in the first place).

I've removed the erroneous Name parameter I was accidentally passing to Set-ADUser.

I've added brief help to each of the commands to describe the intent of each.

I don't expect this to resolve the Server is Unwilling error, but please can you paste the information it says it's pushing, need to see how close this is getting.
#Requires -Module ActiveDirectory

# Utility functions

function Get-CommandParameter {
    # .SYNOPSIS
    #   Get a set of parameters from a specific parameter set.
    # .DESCRIPTION
    #   Get a set of parameters from a specific parameter set.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.ParameterMetadata
    # .EXAMPLE
    #   Get-CommandParameter Set-ADUser -ParameterSetName Identity
    
    [OutputType([System.Management.Automation.ParameterMetadata])]
    param(
        # Retrieve parameters for the specified command.
        [Parameter(Mandatory = $true)]
        [String]$CommandName,

        # Limit results to parameters from the specified parameter set.
        [String]$ParameterSetName = '__AllParameterSets',

        # Return the results as a hashtable, using the parameter name as a key.
        [Switch]$AsHashtable
    )

    try {
        $hashtable = @{}

        $command = Get-Command -Name $CommandName -ErrorAction Stop
        $command.Parameters.Values | 
            Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
            ForEach-Object {
                if ($AsHashtable) {
                    $hashtable.Add($_.Name, $_)
                } else {
                    $_
                }
            }

        if ($AsHashtable) {
            $hashtable
        }
    } catch {
        throw
    }
}

# Generic functions

function Invoke-SqlQuery {
    # .SYNOPSIS
    #   Invoke an SQL query.
    # .DESCRIPTION
    #   Invoke an SQL query against a server described by a connection string.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.PSObject
    # .EXAMPLE
    #   Invoke-SqlQuery -Query "SELECT * FROM Table" -ConnectionString "Server=server\instance;Database=db;Trusted_Connection=yes;"

    [OutputType([System.Management.Automation.PSObject])]
    param(
        # An SQL query to execute.
        [Parameter(Mandatory = $true)]
        [String]$Query,

        # The connection string to use. A connection will be created prior to executing the query, then removed afterwards.
        [Parameter(Mandatory = $true)]
        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Process functions

function NewADUser {
    # .SYNOPSIS
    #   Implements the create action.
    # .DESCRIPTION
    #   NewADUser dynamically binds parameters for the New-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path            = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status' -and 
                    $null -ne $_.Value -and
                    ($Value -isnot [String] -or $Value.Trim() -ne '')
                }

            try {
                $validParameters = Get-CommandParameter New-ADUser -AsHashtable

                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        $otherAttributes.Add($property.Name, $property.Value)
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($otherAttributes.Count -gt 0) {
                    $params.OtherAttributes = $otherAttributes
                }
                if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                    $params.Name = $UserInformation.DisplayName
                }

                Write-Host "Creating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        $params = @{
            Identity = $_.SamAccountName
        }
        $replace = @{}

        # Create a list of properties (from UserInformation) which will be set on the existing user
        $propertiesToSet = $UserInformation.PSObject.Properties | 
            Where-Object {
                $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and 
                $null -ne $_.Value -and
                ($Value -isnot [String] -or $Value.Trim() -ne '')
            }

        if ($UserInformation.Action -eq 'update') {
            $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($validParameters.Contains($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    # .SYNOPSIS
    #   Remove the action flag for every object in UserInformation where status is set to 'OK'
    param(
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *, @{Name = 'Status';Expression = 'NotProcessed'}

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

Open in new window

0
AcklesAuthor Commented:
New-ADUser:

ERROR: NewADUser : The server is unwilling to process the request
SQL-AD-O365-LF.ps1 (377, 19): ERROR: At Line: 377 char: 19
ERROR: + $usersToProcess | NewADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (CN=Test Second,...,DC=com:String) [Write-Error], ADException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,NewADUser
ERROR:

Set-ADUser:

ERROR: SetADUser : replace
SQL-AD-O365-LF.ps1 (379, 19): ERROR: At Line: 379 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (tfi:ADUser) [Write-Error], ADInvalidOperationException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,SetADUser
ERROR:

>> Execution time: 00:00:01
0
Chris DentPowerShell DeveloperCommented:
Please can you post the informational blobs? Those that look like:
Updating user with the following information
{
    "postalcode":  "60311",

Open in new window

0
AcklesAuthor Commented:
New-ADUser

Creating user with the following information
{
    "AccountPassword":  {
                            "Length":  10
                        },
    "userPrincipalName":  "tse@test.com",
    "DisplayName":  "Test Second",
    "samAccountName":  "tse",
    "Name":  "Test Second",
    "Department":  "Team Z",
    "Title":  " ",
    "GivenName":  "Test",
    "OtherAttributes":  {
                            "st":  "Zürich",
                            "mobile":  "",
                            "co":  "756",
                            "mail":  "test.second@test.com",
                            "l":  "Zürich",
                            "telephoneNumber":  "",
                            "c":  "CH",
                            "physicalDeliveryOfficeName":  "",
                            "sn":  "Second"
                        },
    "PostalCode":  "8001",
    "StreetAddress":  " 5",
    "Path":  "OU=TestUser,OU=test,DC=test,DC=com",
    "Enabled":  false
}
0
AcklesAuthor Commented:
Why is DisplayName & Name same?
0
Chris DentPowerShell DeveloperCommented:
Dammit, it's still passing those empty values. I seriously thought I'd killed those.

It's the same because you previously had displayName pushing into the Name parameter:
New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -PasswordNeverExpires $true 

Open in new window

Oh and you set PasswordNeverExpires. Let's see that too. One sec, going to take a longer look at those empty values.
0
AcklesAuthor Commented:
I understand, but what I am wondering is why is it showing twice?
 "userPrincipalName":  "tse@test.com",
    "DisplayName":  "Test Second",
    "samAccountName":  "tse",
    "Name":  "Test Second",
    "Department":  "Team Z",
    "Title":  " ",
    "GivenName":  "Test",
0
Chris DentPowerShell DeveloperCommented:
Ahh it's the simple bugs that get you. Typo. Fixed.

Same again please (with user info again please :)).
#Requires -Module ActiveDirectory

# Utility functions

function Get-CommandParameter {
    # .SYNOPSIS
    #   Get a set of parameters from a specific parameter set.
    # .DESCRIPTION
    #   Get a set of parameters from a specific parameter set.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.ParameterMetadata
    # .EXAMPLE
    #   Get-CommandParameter Set-ADUser -ParameterSetName Identity
    
    [OutputType([System.Management.Automation.ParameterMetadata])]
    param(
        # Retrieve parameters for the specified command.
        [Parameter(Mandatory = $true)]
        [String]$CommandName,

        # Limit results to parameters from the specified parameter set.
        [String]$ParameterSetName = '__AllParameterSets',

        # Return the results as a hashtable, using the parameter name as a key.
        [Switch]$AsHashtable
    )

    try {
        $hashtable = @{}

        $command = Get-Command -Name $CommandName -ErrorAction Stop
        $command.Parameters.Values | 
            Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
            ForEach-Object {
                if ($AsHashtable) {
                    $hashtable.Add($_.Name, $_)
                } else {
                    $_
                }
            }

        if ($AsHashtable) {
            $hashtable
        }
    } catch {
        throw
    }
}

# Generic functions

function Invoke-SqlQuery {
    # .SYNOPSIS
    #   Invoke an SQL query.
    # .DESCRIPTION
    #   Invoke an SQL query against a server described by a connection string.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.PSObject
    # .EXAMPLE
    #   Invoke-SqlQuery -Query "SELECT * FROM Table" -ConnectionString "Server=server\instance;Database=db;Trusted_Connection=yes;"

    [OutputType([System.Management.Automation.PSObject])]
    param(
        # An SQL query to execute.
        [Parameter(Mandatory = $true)]
        [String]$Query,

        # The connection string to use. A connection will be created prior to executing the query, then removed afterwards.
        [Parameter(Mandatory = $true)]
        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Process functions

function NewADUser {
    # .SYNOPSIS
    #   Implements the create action.
    # .DESCRIPTION
    #   NewADUser dynamically binds parameters for the New-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path            = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status' -and 
                    $null -ne $_.Value -and
                    ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
                }

            try {
                $validParameters = Get-CommandParameter New-ADUser -AsHashtable

                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        $otherAttributes.Add($property.Name, $property.Value)
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($otherAttributes.Count -gt 0) {
                    $params.OtherAttributes = $otherAttributes
                }
                if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                    $params.Name = $UserInformation.DisplayName
                }

                Write-Host "Creating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        $params = @{
            Identity = $_.SamAccountName
        }
        $replace = @{}

        # Create a list of properties (from UserInformation) which will be set on the existing user
        $propertiesToSet = $UserInformation.PSObject.Properties | 
            Where-Object {
                $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and 
                $null -ne $_.Value -and
                ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
            }

        if ($UserInformation.Action -eq 'update') {
            $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($validParameters.Contains($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    # .SYNOPSIS
    #   Remove the action flag for every object in UserInformation where status is set to 'OK'
    param(
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *, @{Name = 'Status';Expression = 'NotProcessed'}

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

Open in new window

0
Chris DentPowerShell DeveloperCommented:
> I understand, but what I am wondering is why is it showing twice?

Because Name and DisplayName are two completely different attributes.
0
AcklesAuthor Commented:
BAM!!!
0
AcklesAuthor Commented:
NICE!!!
All accounts made, with correct Attributes, perfectly enabled & disabled & SQL column also updated!!!
0
Chris DentPowerShell DeveloperCommented:
Excellent, long road, but hopefully not too dispiriting for you :)
0
Chris DentPowerShell DeveloperCommented:
Oh I forgot... Password Never Expires. Because it's a parameter for both New-ADUser and Set-ADUser, all we have to do is add the value to the usersToProcess collections after it's come out of SQL. The commands we have will take care of figuring out how to inject the value.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *,
                  @{Name = 'PasswordNeverExpires'; Expression = $true },
                  @{Name = 'Status'; Expression = 'NotProcessed'}

Open in new window

Both New and Set will take it from there without further changes.
0
AcklesAuthor Commented:
No Man, you have been very kind.
Now, 2 things left.
1) incorporating email notification on task completion , failure or success
2) creating the same accounts for O365

I had in earlier script email notification enabled with :
Send-MailMessage -To "abc@test.com" -SmtpServer "smtp.office365.com" -Credential $mycreds -UseSsl "User Creation Task - Successful" -Port "587" -Body "This is an automatically generated message.<br> The following Users have been created! <br> $($DisplayName) created! <br>Best regards<br><b>Your Support Bot</b>" -From "it@test.com" -BodyAsHtml

this was in the if statement on success


                  Send-MailMessage -To "abc@test.com" -SmtpServer "smtp.office365.com" -Credential $mycreds -UseSsl "User Creation Task - Failed" -Port "587" -Body "This is an automatically generated message.<br> The following Users failed to create <br> $($DisplayName) failed to create, Error message: $($_.Exception.Message) <br>Best regards<br><b>Your Support Bot</b>" -From "it@test.com" -BodyAsHtml

on failure.

I have the credentials setup correctly, can you advise where to embed the same in new ?
0
AcklesAuthor Commented:
So I entered this:

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
Select-Object *, @{ Name = 'PasswordNeverExpires'; Expression = $true },
                    @{ Name = 'Status'; Expression = 'NotProcessed' }
# Update any fields which need modification


& got this error:

ERROR: Select-Object : Key "Expression" has illegal type System.Boolean; expected types are {System.String, System.Management.Automation.ScriptBlock}.
SQL-AD-O365-LF.ps1 (371, 1): ERROR: At Line: 371 char: 1
ERROR: + Select-Object *, @{ Name = 'PasswordNeverExpires'; Expression = $true },
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [Select-Object], NotSupportedException
ERROR:     + FullyQualifiedErrorId : DictionaryKeyIllegalValue1,Microsoft.PowerShell.Commands.SelectObjectCommand
ERROR: Get-Variable : Cannot validate argument on parameter 'Name'. The argument is null. Supply a non-null argument and try the command again.
0
Chris DentPowerShell DeveloperCommented:
Sorry, sorry. Very silly of me.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *,
                  @{Name = 'PasswordNeverExpires'; Expression = { $true }},
                  @{Name = 'Status'; Expression = 'NotProcessed' }

Open in new window

1
Chris DentPowerShell DeveloperCommented:
Notifications. At this point we have a chance that some might work, and some might fail. I suggest that these are reported at the same time, but that the difference is highlighted. Let me put a sample together and you can see what you think, I just need to dig out the code I've used in the past for pretty reporting.
0
AcklesAuthor Commented:
Ha Ha Ha,
I'm so glad you make mistakes too ;)

Works perfect!!

Can you please help me on Mail?
0
AcklesAuthor Commented:
No rush, Thanks!
Chris, is it possible to invoke-pssession for O365 in the same script?
I want to use the same variables from SQL to create & make changes in O365 accounts....
0
Chris DentPowerShell DeveloperCommented:
Yes, absolutely. I can't advise to a great extent on the commands, no access to them / never used O365. In general terms it'll be another bolt-on step after both New and Set have had a turn.

It'll need a little control as well, Jason was going for domino affects earlier and until now we haven't had the potential. We do now though, because if New-ADUser fails it might not be prudent to try and do things with that user in O365.

Happily, there's a persistent Status field now, so we should only act if Status -eq 'OK'. Basically, it won't be a problem :)
0
AcklesAuthor Commented:
I have the command for O365,
Just not sure how to invoke connection with SQL, but as you said, we'll manage!!!
0
AcklesAuthor Commented:
Chris,
Did you get time to look for Notification?
0
Chris DentPowerShell DeveloperCommented:
Not yet, sorry. Bit busy today. I should have time later though, I haven't forgotten :)
0
AcklesAuthor Commented:
NO worries, just getting excited to finish 1st part....

I tried to incorporate the O365 in it, but I guess it's not that simple as I thought....
Once this get's done, I will look at it. I'm sure will need help there too....
0
Chris DentPowerShell DeveloperCommented:
The whole thing, plus reporting / notification :)
#Requires -Module ActiveDirectory

# Utility functions

function Get-CommandParameter {
    # .SYNOPSIS
    #   Get a set of parameters from a specific parameter set.
    # .DESCRIPTION
    #   Get a set of parameters from a specific parameter set.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.ParameterMetadata
    # .EXAMPLE
    #   Get-CommandParameter Set-ADUser -ParameterSetName Identity
    
    [OutputType([System.Management.Automation.ParameterMetadata])]
    param (
        # Retrieve parameters for the specified command.
        [Parameter(Mandatory = $true)]
        [String]$CommandName,

        # Limit results to parameters from the specified parameter set.
        [String]$ParameterSetName = '__AllParameterSets',

        # Return the results as a hashtable, using the parameter name as a key.
        [Switch]$AsHashtable
    )

    try {
        $hashtable = @{}

        $command = Get-Command -Name $CommandName -ErrorAction Stop
        $command.Parameters.Values | 
            Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
            ForEach-Object {
                if ($AsHashtable) {
                    $hashtable.Add($_.Name, $_)
                } else {
                    $_
                }
            }

        if ($AsHashtable) {
            $hashtable
        }
    } catch {
        throw
    }
}

# Generic functions

function Invoke-SqlQuery {
    # .SYNOPSIS
    #   Invoke an SQL query.
    # .DESCRIPTION
    #   Invoke an SQL query against a server described by a connection string.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.PSObject
    # .EXAMPLE
    #   Invoke-SqlQuery -Query "SELECT * FROM Table" -ConnectionString "Server=server\instance;Database=db;Trusted_Connection=yes;"

    [OutputType([System.Management.Automation.PSObject])]
    param (
        # An SQL query to execute.
        [Parameter(Mandatory = $true)]
        [String]$Query,

        # The connection string to use. A connection will be created prior to executing the query, then removed afterwards.
        [Parameter(Mandatory = $true)]
        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Process functions

function NewADUser {
    # .SYNOPSIS
    #   Implements the create action.
    # .DESCRIPTION
    #   NewADUser dynamically binds parameters for the New-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path            = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status' -and 
                    $null -ne $_.Value -and
                    ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
                }

            try {
                $validParameters = Get-CommandParameter New-ADUser -AsHashtable

                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        $otherAttributes.Add($property.Name, $property.Value)
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($otherAttributes.Count -gt 0) {
                    $params.OtherAttributes = $otherAttributes
                }
                if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                    $params.Name = $UserInformation.DisplayName
                }

                Write-Host "Creating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        $params = @{
            Identity = $_.SamAccountName
        }
        $replace = @{}

        # Create a list of properties (from UserInformation) which will be set on the existing user
        $propertiesToSet = $UserInformation.PSObject.Properties | 
            Where-Object {
                $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and 
                $null -ne $_.Value -and
                ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
            }

        if ($UserInformation.Action -eq 'update') {
            $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($validParameters.Contains($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    # .SYNOPSIS
    #   Remove the action flag.
    # .DESCRIPTION
    #   Remove the action flag for every object in UserInformation where status is set to 'OK'.
    # .INPUTS
    #   System.Management.Automation.PSObject
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

function NewReport {
    # .SYNOPSIS
    #   Create a report.
    # .DESCRIPTION
    #   This step creates an HTML report based on the UserInformation collection.
    # .INPUTS
    #   System.Management.Automation.PSObject[]
    # .OUTPUTS
    #   System.String

    param (
        # The UserInformation set which holds the state of each action performed by this script.
        [Parameter(Mandatory = $true)]
        [AllowNull()]
        [PSObject[]]$UserInformation
    )

    # HTML header
    $htmlHead = "<style>
        body {
            font-family: Arial;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            border: 1px solid;      
        }

        th {
            background-color: #87CEFA;
            border: 1px solid;
            padding: 1px;
        }

        td {
            border: 1px solid;
            padding: 1px;
        }

        td.Red {
            color: Red;
        }

        td.Green {
            color: Green;
        }
    </style>"

    if ($null -ne $UserInformation) {
        $UserInformation |
            Select-Object SamAccountName, DisplayName, Action, Status |
            Sort-Object {
                switch -Wildcard ($_.Status) {
                    'Failed*' { 1 }
                    'OK'      { 2 }
                    default   { 2 }
                }
            }, DisplayName |
            ConvertTo-Html -Head $htmlHead |
            ForEach-Object {
                # Colour the status cells in.
                switch -Regex ($_) {
                    '<td>Failed' { $_ -replace '<td>Failed', '<td class="Red">Failed'; break }
                    '<td>OK'     { $_ -replace '<td>OK', '<td class="Green">OK'; break }
                    default      { $_ }
                }
            } | Out-String
    }
}

function SendMailReport {
    param(
        [Parameter(Mandatory = $true)]
        [PSObject[]]$UserInformation,

        [Parameter(Mandatory = $true)]
        [String]$Body,

        [String]$To = 'someone@domain.com',

        [String]$From = 'someone@domain.com',

        [String]$Subject = ('User update: {0}' -f (Get-Date).ToShortDateString()),

        [String]$SmtpServer = 'someserver.domain.com'
    )

    # Export this to add as an attachment.
    $UserInformation | Export-Csv UserInformation.csv -NoTypeInformation

    $params = @{
        To          = $To
        From        = $From
        Subject     = $Subject
        Body        = $Body
        BodyAsHtml  = $true
        SmtpServer  = $SmtpServer
        Attachments = '.\UserInformation.csv'
    }
    Send-MailMessage @params
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *,
                  @{Name = 'PasswordNeverExpires'; Expression = { $true }},
                  @{Name = 'Status'; Expression = 'NotProcessed'}

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

# Send job notification
$report = NewReport -UserInformation $usersToProcess
if ($report) {
    SendMailReport -UserInformation $usersToProcess -Body $report
}

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
AcklesAuthor Commented:
it creates the csv, but no email?
the SMTP server needs the password, where do I define it?
Is it possible not to create csv & send the mail directly?

Thanks!
0
AcklesAuthor Commented:
This is the error:

ERROR: Send-MailMessage : The operation has timed out.
SQL-AD-O365-LF.ps1 (533, 2): ERROR: At Line: 533 char: 2
ERROR: +     Send-MailMessage @params
ERROR: +     ~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (System.Net.Mail.SmtpClient:SmtpClient) [Send-MailMessage], SmtpException
ERROR:     + FullyQualifiedErrorId : SmtpException,Microsoft.PowerShell.Commands.SendMailMessage
ERROR:


The SMTP server is 'smtp.office365.com'

what worked for me was in my earlier email notification, i had declared the Credentials.
 -SmtpServer "smtp.office365.com" -Credential $mycreds -UseSsl  -Port "587"

It seems O365 SMTP server needs these...

Any thoughts?
0
Chris DentPowerShell DeveloperCommented:
What you you mean create the CSV and send the mail directly? We're doing that, the other function just deals with the pretty content of the email.

To add those parameters:
    $params = @{
        To          = $To
        From        = $From
        Subject     = $Subject
        Body        = $Body
        BodyAsHtml  = $true
        Attachments = '.\UserInformation.csv'
        SmtpServer  = "smtp.office365.com"
        Port        = 587
        Credential  = $Credential
    }
    Send-MailMessage @params

Open in new window

Now this does rather lead onto credential storage because you've got to fill that credential variable.

We can create a Credential like this:
$Credential = New-Object PSCredential($username, $securePassword)

Open in new window

You've created a secure string ($securePassword) before, it's nothing new. If you don't happen to have a password vault system I suggest you encrypt the credentials under the account running the script. Would you like instructions?
0
AcklesAuthor Commented:
Sure
Thanks
0
AcklesAuthor Commented:
I meant not to create CSV , but send the content in mail body
That way it can be seen directly on the mobile device too
0
Chris DentPowerShell DeveloperCommented:
It has both body (subset of fields) and attachment (everything) :)
0
AcklesAuthor Commented:
Sorry
Since it didn't work, couldn't know
0
Chris DentPowerShell DeveloperCommented:
Password handling.

So, if you're going to include a password in the script for the SMTP connection you get a couple of options.

1. Plain Text
2. Encrypted using built-in stuff

To encrypt using built in stuff you effectively lock decryption of the password to a single user and single computer. That means we must run encryption under the same user on the same computer to give us valid cipher text.

'tis simple and one-time to create the encrypted password. Drop it a a file or something:
'PlainTextPassword' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString

Open in new window

And to consume it at runtime:
Get-Content pwd.txt | ConvertTo-SecureString

Open in new window

All that's left is to make that into a Credential:
$Credential = New-Object PSCredential('TheUser', (Get-Content pwd.txt | ConvertTo-SecureString))

Open in new window

No one else can decrypt the password (well, without breaking the encryption scheme / brute force, that kind of thing).
0
AcklesAuthor Commented:
Awesome Chris!
Works perfect!!!
0
AcklesAuthor Commented:
Thanks Chris, Again!
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.