Powershell - Comparing SQL to AD

Question

I need to compare one list to the other and create a third list of every employee ID found in AD, but not in SQL. I have two commands that work to pull the data from each. I'm struggling to create a command that will combine these in a way to do what I want.

One connects to a SQL database and is pulling any current employees from there.

$sqlpeeps = Invoke-Sqlcmd -ServerInstance '192.168.1.1' -Database 'DATABASE' 
-Query "SELECT * FROM [COMPANY].[dbo].[employee] WHERE EmployeeStatus in 
('A', 'S', 'L')"

The other command is grabbing all of our active AD accounts.

$adpeeps = get-aduser -filter * -searchbase 
"OU=Users,OU=Logins,DC=COMPANY,DC=COM" -properties *

I think what I need is some sort of foreach loop, but I can't seem to find a way to say "not in" with powershell, so I am having trouble writing it.

$adpeeps | foreach ($_.EmployeeID in $sqlpeeps) <do nothing?> else {out-file 
"C:\users\user\Desktop\here.txt"}

If it makes helping me with this easier, there is one column in the SQL data called FILE# which directly correlates to an AD attribute, EmployeeID. Is there an easy way to cut out all extraneous data so I am only using these two columns for comparison?

Ideally, the script needs to find AD accounts that were deleted out of our SQL table-- in other words, a list to hand off for manual deletion.

`!(object -in object)` I suggest a `for` loop so you can do every third easier like `for($i=0; $i -lt $sqlpeeps.length; $i+=3){do stuff}` — Nico Nekoru, Jun 10 '20 at 18:49

score 0 · Answer 1 · answered Jun 10 '20 at 20:11

i assume that $sqlpeeps has a column EmployeeID (it it really is called "FILE#" you can use the AS key in your query like described here)

# iterate through $adpeeps and process each user as $aduser 
foreach($aduser in $adpeeps) {
    # test if the sql column EmployeeID does not contain the employeeid of the ad user
    if($sqlpeeps.EmplyeeID -notcontains $aduser.employeeid) {
        Out-File -Path "C:\users\user\Desktop\here.txt" -Append
    }
}

Powershell - Comparing SQL to AD

1 Answers1