PowerShell: Compare CSV to AD

Question

I'm fairly new to PowerShell and I'm posting this on many forums but I've had success with programming assistance from here before and although this isn't strictly programming, I was hoping someone might know the answer.

My organization had about 5,300 users we needed to disable for a client. Someone decided the best use of our time was have people go through AD and disable them one at a time. Soon as I got wind of this I put a stop to it and used PowerShell to take the CSV list we already had, and ran a cmdlet to disable all of the users in the CSV list.

This appeared to work, but I wanted to run a comparison. I want to compare the users from the CSV file, to the users in AD, and confirm that they are all disabled without having to check all 5300 individually. We checked about 60 random ones to verify my run worked, but I want to make sure none slipped through the cracks.

I've tried a couple scripts and I've tried some variations of cmdlets. None of the scripts I tried even worked, spammed with errors. When I try to run a search of AD either using get-content or import-CSV from the csv file, when I export its giving me about 7600 disabled users (if I search by disabled). There were only 5300 users in total, so it must be giving me all of the disabled users in AD. Other cmdlets i've run appear to do the same thing, its exporting an entire AD list instead of just comparing against my CSV file.

Any assistance anyone can provide would be helpful.

Answer 1

Assuming your CSV has a column called DN you can run the following which will return all users from your spreadsheet which are enabled

import-csv YourUsersCSV.csv | Get-ADUser -Filter 
     {DistinguishedName -eq $_.DN } |
     where{$_.enabled -eq $true} | 
     Select-Object -Property DistinguishedName,samaccountname,enabled

Answer 2

Without knowing the exact structure of your CSV I'm going to assuming it is as such: "CN=","OU=","DC=" "JSmith","Accounting","Foo.com" "BAnderson","HR","Foo.com" "JAustin","IT","Foo.com"

That said, if your first field actually has CN= included (i.e. "CN=JSmith","OU=Accounting","Foo.com") you will want to trim that with .TrimStart("CN=").

$ToRemove = Import-CSV UserList.csv
$UserList=@()
ForEach($User in $ToRemove){
    $Temp = ""|Select "User","Disabled"
    $Temp.User = $User.'CN='
    If((Get-aduser $Temp.User -Prop Enabled).Enabled){$Temp.Disabled='False'}else{$Temp.Disabled='True'}
    $UserList+=$Temp}
$UserList|?{$_.Disabled -eq 'False'}

That loads the CSV into a variable, runs each listing through a loop that checks the 'CN=' property, creates a custom object for each user containing just their name and if they are disabled, and then adds that object to an array for ease of use later. In the end you are left with $UserList that lists everybody in the original CSV and if they are disabled. You can output it to a file, filter it for just those that are still enabled, or whatever you want. As noted before if your CSV actually has CN=JSmith for each line you will want to update line 5 to look as such:

    $Temp.User = $User.'CN='.TrimStart("CN=")

If you don't have any headers in the CSV file you may want to inject them. Just put a line at the top that looks like:
CN=,OU=,DC= Or, if you have varying OU depths you may be better off doing a GC and then running each line through a split, taking the first part, trimming the CN= off the beginning, and checking to see if they are disabled like:

GC SomeFile.CSV||%{$_.split(",")[0].trimstart("CN=")|%{If((get-aduser $_ -prop enabled).enabled){"$_ is Enabled"}else{"$_ is Disabled"}}}