SQL

Voor deze oefeningen zijn nodig: member server plus GUI en RSAT-AD-Tools.

Oefening 1. SQL en AD

Installeer Chocolatey, SQL Server Express 2016 en de AdventureWorks database:

Invoke-Webrequest https://raw.githubusercontent.com/jacqinthebox/powershell-training/master/tools/AdventureWorks.ps1 -UseBasicParsing | Invoke-WebRequest

Installeer eventueel ook SQL Server Managament Studio:

choco install sql-server-management-studio -y

Query de AdventureWorks Database

Importeer de SQLPS module

$env:PSModulePath = $env:PSModulePath + ";C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules" 
Import-Module SQLPS 

Declareer de variabelen

$instance = '.\sqlexpress' 
$db = 'AdventureWorks2014'
$query = 'Select top 10 * from HumanResources.vEmployee order by LastName'

Voer de query uit tegen de AdventureWorks2014 database

$mensen = (Invoke-Sqlcmd -Query $query -Database $db -ServerInstance $instance)

Importeer naar Active Directory

New-ADOrganizationalUnit AW

$pass = ConvertTo-SecureString -String "Help2016!" -AsPlainText -Force

foreach ($m in $mensen) {

new-aduser -Name $m.LastName -GivenName $m.FirstName -Surname $m.LastName -EmailAddress $m.EmailAddress -StreetAddress $m.AddressLine1 -City $m.City -Path "OU=AW,DC=MDS,DC=local" -AccountPassword $pass -Enabled $true

}

Herschrijf onderstaand script tot een module

Inclusief help en foutenafhandeling

$env:PSModulePath = $env:PSModulePath + ";C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules" 
Import-Module SQLPS 

$instance = '.\sqlexpress' 
$db = 'AdventureWorks2014'
$query = 'Select top 10 * from HumanResources.vEmployee order by LastName'

$mensen = (Invoke-Sqlcmd -Query $query -Database $db -ServerInstance $instance)

$pass = ConvertTo-SecureString -String "Help2016!" -AsPlainText -Force

foreach ($m in $mensen) {

    new-aduser -Name $m.LastName -GivenName $m.FirstName -Surname $m.LastName -EmailAddress $m.EmailAddress -StreetAddress $m.AddressLine1 -City $m.City -Path "OU=AW,DC=MDS,DC=local" -AccountPassword $pass -Enabled $true

}

Skeleton script:

Function New-Function {
  <#
           .SYNOPSIS
            Creates an AD User from the AdventureWorks Database
           .DESCRIPTION
           .PARAMETER
           .PARAMETER
           .EXAMPLE
  #>

  [CmdletBinding()] 
  Param(
    [Parameter(Mandatory=$true)]	
    [string]$Instance,
    [Parameter(Mandatory=$true)]	
    [string]$Database
    #etcetera
  ) 
  BEGIN {
    
  }
  PROCESS {
    
    }	
  }
  END {
    
  }
}

Oefening 2. Exporteren van SQL naar csv

Schrijf een nieuwe functie die de medewerkers exporteert naar een csv bestand. Maar voeg toe aan het rapport:

• Datum in dienst = vandaag
• Behandelaar = "Piet van Personeelszaken"
• BSN = de bsn staat vermeld in onderstaande csv:

"BusinessEntityID","LastName","BSN"
"285","Abbas","746458872"
"38","Abercrombie","646458472"
"211","Abolrous","546458472"
"121","Ackerman","446458472"
"67","Adams","437893364"
"270","Ajenstat","937893364"
"287","Alberts","633797014"
"207","Alderson","258256162"
"216","Alexander","626115243"
"227","Altman","626004443"

Spoiler alert!

Hieronder een deel van een mogelijke oplossing:

$bsn = Import-Csv .\bsn.csv

foreach ($m in $mensen) {
    
   foreach ($b in $bsn) {
        
        if ($b.BusinessEntityID -eq $m.BusinessEntityID) {
               $bsnnr = $b.bsn
        }

    } 

    $object = [pscustomobject]@{
        LastName = $m.LastName;
        GivenName = $m.FirstName;
        InDienst = Get-Date;
        Behandelaar = "Piet van Personeelszaken"
        BSN = $bsnnr;
       }
      
    Write-Output $object
    }
   }