3

I want to create a backstop for lack of notification to IT when an employee leaves. We receive an active employee roster in csv format including the employeeID field monthly. I plan to request for this daily which they can provide via their HRIS system. I'd like to eventually automate via task scheduler a copy from where HR puts the file to a location where we run our scripts - then kick off the below script to run against the copied csv. For now I just need help getting the Powershell script right.

What I would like to do is this:

  • Search AD for employees with an employeeID (no blanks to be returned)
  • Import a csv that has a column of employeeIDs
  • Perform a search against the csv from the AD results
  • For any employee IDs that exist in AD but not in the csv, send an email address to an address, "user $_.Name not an employee

EmployeeID is our most reliable field as HR doesn't have a list of SamAccountNames and people get married and names and email addresses change. I do not want to automate the process of disabling accounts because that would enable a mechanism for a rogue actor in HR to disable everyone's account.

My script is all wrong but here is the thought process I started with:

# Return employees with an employeeID field populated - we're not concerned with service accounts, consultants, etc # $adusers = Get-ADUser -searchbase "OU=MyOU,DC=MyCompany,DC=COM" -Filter {employeeID -like "*" -and enabled -eq $true} -Properties employeeID # # Import active roster # $csv = Import-Csv C:\temp\activeroster-test.csv foreach($emp in $csv) { $csvID = $csv.employeeID $csvName = $csv.Name if($adusers.EmployeeID -notlike $csvID) { echo '**not found in roster**' echo $ADusers.Name } }

I haven't got to the email notification part because I can't seem to even get this. It just returns the people in my roster to the tune of the amount of people in the roster. It's backwards. Help!

Edit - updated with email notification:

# Return employees with an employeeID field populated - we're not concerned with service accounts, consultants, etc $adUsers = Get-ADUser -searchbase "OU=MyOU,DC=Example,DC=COM" -Filter {employeeID -like "*" -and enabled -eq $true} -Properties employeeID # Email Server info $SmtpServer = "emailserver.example.com" $NotificationEmailAddress = "[email protected]" # # Import active roster # $csv = Import-Csv C:\temp\activeroster.csv foreach ($emp in $adUsers) { $csvIDList = $csv.EmployeeID if ($emp.EmployeeID -notin $csvIDList) { $Body = "The following users are still enabled in Active Directory however not found in the active employee roster " + ($($emp.Name) | out-string) Send-MailMessage -From $NotificationEmailAddress -To $NotificationEmailAddress -Subject "Active Accounts Not In Employee Roster" -Priority High -dno onFailure -SmtpServer $SmtpServer -Body $Body } }

I get an email for each user. Thankfully in my test I am doing a small OU and a sample subset of the roster. Heh! Any advise? I think I may need to create another variable that encompasses all the results, yeah?

Improve this question

1 Answer 1

Reset to default
2

Your script isn't all wrong, you're actually pretty close. You're printing all of the returned users from your AD query with each iteration as the affected user, when you just mean to return the one you're iterating over. And you can use -notin on the list you have from the HR csv when comparing to compare to the array of IDs.

# Return employees with an employeeID field populated - we're not concerned with service accounts, consultants, etc # $adUsers = Get-ADUser -searchbase "OU=MyOU,DC=MyCompany,DC=COM" -Filter {employeeID -like "*" -and enabled -eq $true} -Properties employeeID # # Import active roster # $csv = Import-Csv C:\temp\activeroster-test.csv # Email Server info $smtpServer = "emailserver.example.com" $notificationEmailAddress = "[email protected]" [System.Collections.ArrayList]$listOfMissing = @() foreach ($emp in $adUsers) { $csvIDList = $csv.EmployeeID if ($emp.EmployeeID -notin $csvIDList) { Write-Output "**Employee $($emp.Name) not found in roster**" $listOfMissing.Add($emp.Name) } } if ($listOfMissing) { $subject = "Active Accounts Not In Employee Roster" $body = @" The following users are still enabled in Active Directory however not found in the active employee roster: $($listOfMissing | Out-String) "@ $emailParameters = @{ From = $notificationEmailAddress To = $notificationEmailAddress Subject = $subject Priority = 'High' Dno = 'onFailure' SmtpServer = $smtpServer Body = $body } Send-MailMessage @emailParameters } else { Write-Output 'Email not sent, no users found' }

I may also suggest pursuing some regular automated update of that csv with another script, or integration to the HR system, which is usually Workday or something similar, though I know in our positions we sometimes don't have that access. :)

For the email you can check out this SO post.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you. I updated my post as I am now taking a stab at the email notification. I get an email for each result. Can you take a peek at that too? Thank you so much!
Sure thing, what you'll want to do is build a list and then append each user found to the list, then use that list in the body of your email. I made an edit with how I might do it. The "emailParameters" I've added is just a nicer way to pass parameters to a function, called "splatting"
That script update worked beautifully as well. That notification format is more preferable and will allow me to enabled notifications on a schedule for a dozen scripts we run manually right now. Thank you so much!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.