PowerShell脚本异常：AD员工ID与SQL比对输出所有ID问题排查

The core issue here is that you're comparing entire objects instead of just the EmployeeID values. Let's break down what's happening and fix it:

Root Cause #

• $sqlpeeps contains objects returned by Invoke-Sqlcmd, each with an EmployeeID property.
• $adpeeps contains full AD user objects, each with their own EmployeeID property.
• When you run $_ -notin $sqlpeeps, you're checking if the entire AD user object exists in the SQL object collection—which it never will, since they're different object types. That's why every AD user passes the filter.

Corrected Script #

Here's the fixed version with clear explanations:

# Import the Active Directory module
Import-Module ActiveDirectory

# Fetch EmployeeIDs from SQL, extract just the ID values (not full objects)
$sqlpeeps = Invoke-Sqlcmd -ServerInstance '192.168.1.1' -Database 'COMPANY' -Query "SELECT EmployeeID FROM [COMPANY].[dbo].[employee] WHERE [EmployeeStatus] in ('A', 'S', 'L')"
$sqlEmployeeIDs = $sqlpeeps | Select-Object -ExpandProperty EmployeeID

# Fetch AD users with EmployeeID property, filter out those without an ID
$adpeeps = Get-ADUser -Filter * -SearchBase "OU=OU,OU=OU,OU=OU,DC=DC,DC=COM" -Properties 'EmployeeID' | Where-Object { $_.EmployeeID -ne $null }

# Compare just the EmployeeID values to find AD users not in SQL
$adpeeps | Where-Object { $_.EmployeeID -notin $sqlEmployeeIDs } | Out-Host

Key Changes Explained #

1. Extract SQL EmployeeID Values:

   • Select-Object -ExpandProperty EmployeeID converts the SQL object collection into a simple array of EmployeeID strings/numbers. This lets us compare just the IDs instead of full objects.

2. Filter AD Users Without EmployeeID:

   • Adding Where-Object { $_.EmployeeID -ne $null } ensures we only consider AD users who actually have an EmployeeID set (since users without one can't be in your SQL list anyway).

3. Compare Correct Properties:

   • Changed $_ -notin $sqlpeeps to $_.EmployeeID -notin $sqlEmployeeIDs—now we're comparing the AD user's EmployeeID directly against the list of SQL IDs.

Edge Cases to Consider #

• Data Type Mismatches: If your SQL EmployeeID is an integer but AD stores it as a string, you might need to convert one to match the other (e.g., [string]$_.EmployeeID -notin $sqlEmployeeIDs or [int]$_.EmployeeID -notin $sqlEmployeeIDs).
• Case Sensitivity: PowerShell's -notin is case-insensitive by default, but if your IDs are case-sensitive (unlikely for EmployeeIDs), you can use -cnotin instead.

内容的提问来源于stack exchange，提问作者MutinyMate