Question

A. Create a PowerShell script named “restore.ps1” within the “Requirements2” folder. For the first line, create a comment and include your first and last name along with your student ID.

Note: The remainder of this task shall be completed within the same script file, “restore.ps1.”

B. Write a single script within the “restore.ps1” file that performs all of the following functions without user interaction:

1. Create an Active Directory organizational unit (OU) named “finance.”

2. Import the financePersonnel.csv file (found in the “Requirements2” directory) into your Active Directory domain and directly into the finance OU. Be sure to include the following properties:

• First Name

• Last Name

• Display Name (First Name + Last Name, including a space between)

• Postal Code

• Office Phone

• Mobile Phone

3. Create a new database on the UCERTIFY3 SQL server instance called “ClientDB.”

4. Create a new table and name it “Client_A_Contacts.” Add this table to your new database.

5. Insert the data from the attached “NewClientData.csv” file (found in the “Requirements2” folder) into the table created in part B4.

C. Apply exception handling using try-catch for System.OutOfMemoryException.

D. Run the script within the uCertify environment. After the script executes successfully, run the following cmdlets individually from within your Requirements2 directory:
1. Get-ADUser -Filter * -SearchBase “ou=finance,dc=ucertify,dc=com” -Properties DisplayName,PostalCode,OfficePhone,MobilePhone > .\AdResults.txt
2. Invoke-Sqlcmd -Database ClientDB –ServerInstance .\UCERTIFY3 -Query ‘SELECT * FROM dbo.Client_A_Contacts’ > .\SqlResults.txt

Note: Ensure you have all of the following files intact within the “Requirements2” folder, including the original files:

• “restore.ps1”

• “AdResults.txt”

• “SqlResults.txt”

E. Compress the “Requirements2” folder as a ZIP archive. When you are ready to submit your final task, run the Get-FileHash cmdlet against the “Requirements2” ZIP archive. Note the hash value and place it into the comment section when you submit your task.

Answer 1

:: Solution ::

1. Get-ADUser -Filter * -SearchBase “ou=finance,dc=ucertify,dc=com” -Properties DisplayName,PostalCode,OfficePhone,MobilePhone > .\AdResults.txt
2. Invoke-Sqlcmd -Database ClientDB –ServerInstance .\UCERTIFY3 -Query ‘SELECT * FROM dbo.Client_A_Contacts’ > .\SqlResults.txt

Note: Ensure you have all of the following files intact within the “Requirements2” folder, including the original files:

• “restore.ps1”

• “AdResults.txt”

• “SqlResults.txt”

E. Compress the “Requirements2” folder as a ZIP archive. When you are ready to submit your final task, run the Get-FileHash cmdlet against the “Requirements2” ZIP archive. Note the hash value and place it into the comment section when you submit your task.

The submission should also include the SQL results and AD results files.

The submission contains the PowerShell script and two input csv files. The hard coded paths to the input files will need to be removed the script should use the local files to run. The submission should also include the SQL results and AD results files.
Please inform if any extra information is needed.

Param
(
[string]$OUName = "finance",
[string]$ADUsersCSVPath = "c:\Users\Administrator\Documents\gitRepos\powershell\Requirements2\financePersonnel.csv",
[string]$SQLDataCSVPath = "c:\Users\Administrator\Documents\gitRepos\powershell\Requirements2\NewClientData.csv",
[string]$OUPath = "DC=nealpimentel,DC=com",
[string]$Database = "ClientDB",
[string]$SqlServer = "DCSVR01"
)
Function Add-ADOU
{
Param
(
[Parameter(Mandatory = $true)]
[string]$OUName,
[Parameter(Mandatory = $false)]
[string]$OUPath = "DC=seandersontech,DC=com"
)
Write-Host -ForegroundColor DarkCyan "Configuring AD"
Write-Host -ForegroundColor Green "Creating OU" $OUName

New-ADOrganizationalUnit -Name $OUName -Path $OUPath

Write-Host -ForegroundColor Magenta "Done"
}
Function Import-ADUsers
{
Param
(
[Parameter(Mandatory)]
[string]$BackupCsvPath,
[Parameter(Mandatory)]
[string]$OUPath
)

Write-Host -ForegroundColor Blue "Importing Users"

$BackUPADUsersBase = Import-CSV $BackUPCSVPath

$ADUsers = $BackUPADUsersBase | Select-Object
@{Name = 'BobAccountNAme' ; Expression = {$_.first_name + $_.last_name}}
@{Name = 'Name' ; Expression = {$_.first_name + " " + $_.last_name}},
@{Name = 'DisplayName' ; Expression = {$_.first_name + " " + $_.last_name}},
@{Name = 'GivenName' ; Expression = {$_.first_name}},
@{Name = 'Surname' ; Expression = {$_.last_name}},
@{Name = 'PostalCode' ; Expression = {$_.zip}},
@{Name = 'OfficePhone' ; Expression = {$_.phone1}},
@{Name = 'MobilePhone' ; Expression = {$_.phone2}}

$ADUsers | New-ADUser -Path $OUPath

Write-Host -ForegroundColor Magenta "Done"
}
Function Add-SQLDB
{
Param
([Parameter(Mandatory)]
[string]$Database,
[Parameter(Mandatory)]
[string]$SqlServer,
[string]$Table = "Client_A_Contacts",
[string]$SqlServerPath = "SQLSERVER:\SQL\DCSVR01\"
)

Write-Host -ForegroundColor DarkRed "Configuring SQL"
Write-Host -ForegroundColor Gray "Creating Database" $Database
  
$svr = Get-Item ($SqlServerPath + "Default")
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -ArgumentList $svr, $Database
$db.Create()

Write-Host -ForegroundColor Blue $db.Name "Created" $db.CreateDate

Write-Host -ForegroundColor DarkGreen "Creating Table " $Table

$CreateTable =
Use ClientDB
CREATE TABLE Client_A_Contacts
(
first_name varchar(100) NOT NULL,
last_name varchar(100) NOT NULL,
samAccount varchar(100) NOT NULL,
city varchar(100) NOT NULL,
county varchar(100) NOT NULL,
zip int NOT NULL,
phone1 varchar(20) NOT NULL,
phone2 varchar(20) NOT NULL
)
  

Invoke_SqlCmd -ServerInstance $SqlServer -Database $Database -Query $CreateTable

Write-Host -ForegroundColor Magenta "Done"
}
Function Import-SQLData
{
Param
(
[Parameter(Mandatory)]
[string]$CSVPath,
[Parameter(Mandatory)]
[string]$Database,
[Parameter(Mandatory)]
[string]$SqlServer
)

Write-Host -ForegroundColor DarkGreen "Importing SQL Data"

$SQLData = Import-Csv $CSVPath

$TableDate = INSERT INTO dbo.Client_A_Contacts
VALUES

$n = 0

ForEach ( $user in $SQLData )
{
$n = 1
$TableData += "('" + $user.first_name
$TableData += "', '" + $user.last_name
$TableData += "', '" + $user.samAccount
$TableData += "', '" + $user.city
$TableData += "', '" + $user.county
$TableData += "', " + $user.zip
$TableData += ", '" + $user.phone1
$TableData += "', '" + $user.phone2
$TableData += "')"
}

Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query $TableData

Write-Host -ForegroundColor Red "Done"
}
Add-ADOU -OUName

$OUPath = "OU=" + $OUName + "," + $OUPath

Import-ADUsers -BackupCsvPath $ADUsersCSVPath -OUPath $OUPath

Add-SQLDB -Database $Database -SqlServer $SqlServer

Import-SQLData -CSVPath $SQLDataCSVPath -Database $Database -SqlServer $SqlServer

Please rate my answer thank you...