04_practice_scenario.md (6030B)
1 # Practice Scenario: New Employee Onboarding Automation 2 3 ## The Situation 4 5 Rogers School District uses a third-party HR system (let's call it **HRConnect**) as the source of truth for employee records. When a new staff member is hired, HR enters them in HRConnect. Your job is to make sure that new employee automatically gets: 6 7 1. A record in the district's internal SQL database (`DistrictDB`) 8 2. An Active Directory account 9 3. Logged so there's an audit trail 10 11 HR exports a CSV from HRConnect each morning. You need to process it. 12 13 --- 14 15 ## Step 1: Understand the Source Data 16 17 The CSV (`hr_export.csv`) looks like this: 18 19 ``` 20 EmployeeID,FirstName,LastName,Email,Department,HireDate 21 1001,Jane,Doe,jdoe@rogers.k12.ar.us,Teachers,2026-06-01 22 1002,Mark,Smith,msmith@rogers.k12.ar.us,Custodial,2026-06-01 23 1003,Linda,Park,lpark@rogers.k12.ar.us,Administration,2026-06-01 24 ``` 25 26 --- 27 28 ## Step 2: SQL — Find Who Is New 29 30 Before creating anything, check who already exists in `DistrictDB` to avoid duplicates. 31 32 ```sql 33 -- What's already in our system 34 SELECT EmployeeID, LastName, Email 35 FROM dbo.Employees 36 WHERE Active = 1; 37 38 -- After importing the CSV to a staging table, find the delta 39 SELECT h.EmployeeID, h.FirstName, h.LastName, h.Email, h.Department 40 FROM dbo.HRStaging h 41 LEFT JOIN dbo.Employees e ON h.EmployeeID = e.EmployeeID 42 WHERE e.EmployeeID IS NULL; -- these are the new hires 43 ``` 44 45 **What this shows:** You don't blindly create accounts for every row — you find the diff first. This prevents duplicate accounts and is exactly the "validate before deployment" behavior they listed. 46 47 --- 48 49 ## Step 3: PowerShell — Process the CSV and Create AD Accounts 50 51 ```powershell 52 Import-Module ActiveDirectory 53 54 function Write-Log { 55 param([string]$Message) 56 $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss" 57 "$timestamp - $Message" | Tee-Object -FilePath "onboarding.log" -Append 58 } 59 60 $csvPath = "hr_export.csv" 61 $ouPath = "OU=Staff,DC=rogers,DC=k12,DC=ar,DC=us" 62 $newHires = Import-Csv $csvPath 63 64 foreach ($hire in $newHires) { 65 66 $username = ($hire.FirstName[0] + $hire.LastName).ToLower() # e.g. "jdoe" 67 68 # Check if AD account already exists 69 $existing = Get-ADUser -Filter { SamAccountName -eq $username } -ErrorAction SilentlyContinue 70 71 if ($existing) { 72 Write-Log "SKIP: $username already exists in AD" 73 continue 74 } 75 76 try { 77 New-ADUser ` 78 -Name "$($hire.FirstName) $($hire.LastName)" ` 79 -GivenName $hire.FirstName ` 80 -Surname $hire.LastName ` 81 -SamAccountName $username ` 82 -UserPrincipalName "$username@rogers.k12.ar.us" ` 83 -EmailAddress $hire.Email ` 84 -Department $hire.Department ` 85 -Path $ouPath ` 86 -AccountPassword (ConvertTo-SecureString "TempP@ss2026!" -AsPlainText -Force) ` 87 -ChangePasswordAtLogon $true ` 88 -Enabled $true 89 90 Write-Log "CREATED: $username ($($hire.FirstName) $($hire.LastName)) - $($hire.Department)" 91 92 } catch { 93 Write-Log "ERROR: Failed to create $username - $($_.Exception.Message)" 94 } 95 } 96 97 Write-Log "--- Onboarding run complete ---" 98 ``` 99 100 --- 101 102 ## Step 4: Write Back to SQL 103 104 After creating the AD accounts, record the result in `DistrictDB` so the database stays in sync with AD. 105 106 ```powershell 107 # After successful New-ADUser, also write to the DB 108 $query = @" 109 INSERT INTO dbo.Employees (EmployeeID, FirstName, LastName, Email, Department, HireDate, ADCreated, Active) 110 VALUES ('$($hire.EmployeeID)', '$($hire.FirstName)', '$($hire.LastName)', 111 '$($hire.Email)', '$($hire.Department)', '$($hire.HireDate)', GETDATE(), 1) 112 "@ 113 114 Invoke-Sqlcmd -ServerInstance "db01" -Database "DistrictDB" -Query $query 115 ``` 116 117 --- 118 119 ## Step 5: Validate Before You're Done 120 121 After the run, verify things look right before calling it complete. 122 123 ```sql 124 -- Confirm new hires landed in the DB 125 SELECT EmployeeID, FirstName, LastName, ADCreated 126 FROM dbo.Employees 127 WHERE ADCreated >= CAST(GETDATE() AS DATE) 128 ORDER BY ADCreated DESC; 129 130 -- Make sure no one from the CSV was skipped 131 SELECT h.EmployeeID, h.LastName 132 FROM dbo.HRStaging h 133 LEFT JOIN dbo.Employees e ON h.EmployeeID = e.EmployeeID 134 WHERE e.EmployeeID IS NULL; -- should return 0 rows if successful 135 ``` 136 137 --- 138 139 ## What This Scenario Demonstrates 140 141 | Job Requirement | How This Covers It | 142 |---|---| 143 | Automate employee onboarding | The whole script | 144 | PowerShell proficiency | CSV import, AD cmdlets, error handling, logging | 145 | SQL proficiency | Staging, LEFT JOIN diff, INSERT, validation queries | 146 | Validate before production | Step 2 diff + Step 5 post-run checks | 147 | Audit trail / documentation | `onboarding.log` + DB timestamps | 148 | Work independently | Script handles its own errors and keeps going | 149 150 --- 151 152 ## Practice Exercises 153 154 1. **Run it mentally end-to-end** — trace what happens for a new hire, an existing hire, and a hire where `New-ADUser` throws an error 155 2. **Add a department-to-OU mapping** — Teachers go to `OU=Teachers`, Custodial to `OU=Support`, etc. 156 3. **Add a dry-run flag** — `$DryRun = $true` prints what would happen without actually creating anything; great for testing 157 4. **Write the SQL that disables accounts** for employees whose `Active` flag flipped to 0 in the HR export (offboarding is just as important as onboarding) 158 159 --- 160 161 ## If They Ask "Walk Me Through How You'd Handle Onboarding Automation" 162 163 Don't recite this script. Instead, say: 164 165 > "First I'd identify the source of truth — HR system, SIS, whatever they're using. Then I'd understand the export format. The script would compare the incoming data against what's already in AD and the database to find the delta — who's new, who changed, who's gone. I'd create or update records, write back to the DB, and log everything. Before it goes to production I'd test it against a sample and verify the output manually. Then I'd schedule it and monitor the logs." 166 167 That answer covers their entire job description in four sentences.