ORPHANED
ACCOUNT
DETECTOR
Hi, my name is Derek Visch, and I help IT Teams automate their employee provisioning process. Automating directory management allows you to focus on your business again by saving time, securing your directory, eliminating license waste, and shifting the responsibility of employee data to HR (with mechanisms to work around poor HR data). After the automation is in place, it unlocks a new mindset for your team, which allows you to focus more on what matters in the business and less on things that don't differentiate you from everyone else.
This Orphaned Account Detector will find all accounts currently in Active Directory, Microsoft 365, or Google Workspace that should be inactive according to your HR system, with step-by-step details on how to get the data, match the data, and find those inactive accounts. If you're lucky enough to have a team, send this to someone on your team and have them report back with the results. Or reach out to us, and we'll automate everything for you!
The result:
You'll have a clean directory system that aligns perfectly with your HR system. Most of our clients find thousands of dollars a year in savings from this report alone, and we're giving this to you for free!
Sound good? I'm excited to share this with you!
Book a CallWHY ORPHANED ACCOUNTS
ARE SUCH A PROBLEM
OF EX-EMPLOYEES
CAN STILL ACCESS
CORPORATE APPS
OF SAAS LICENSES
GO UNUSED
OneLogin's team did a study showing that 50% of ex-employees can still access corporate apps after leaving their company.
53% of SaaS licenses go unused. Unused licenses are a waste of money for all companies. We can fix some of this by simply disabling these accounts.
ORPHANED ACCOUNT
DETECTOR STEPS
GET THE DATA
MATCH ACCOUNTS
MAKE THE DATA ACTIONABLE
DISABLE THE ACCOUNTS
LET EVERYONE KNOW
1. GET THE DATA
We need to get data from our source systems to match data between our HR and Directory systems. We'll focus on Active Directory, Microsoft 365, and Google Workspace, but the steps can be applied to any directory system.
In the next few sections, we'll walk you through how to pull data from HR and your Directory System. This will be a piece of cake!
HR Data: Export HR Data
The easiest way to get HR data is to email your HR team and ask them for an export. They have to do exports all of the time as a part of normal operations so every HR tool has the capability to do something like this.
Here's the template:
HR Export for IT System Cleanup Hey, Hope you're having a great day! We're looking to clean up our IT systems and clean up any unused licenses. To do this, we must ensure our IT systems align with HR. Could you please send me an export (CSV, if possible) of our Employees with the following fields? "ID, Firstname, Lastname, Status" Ideally, this would include all active and inactive employees. The status field should indicate whether they are terminated, full-time, active, etc. Thank you!
Directory Data
Active Directory
- Run PowerShell as an Administrator.
- Install RSAT if it is not already installed. Follow Windows steps, or run:
Get-WindowsCapability -Name RSAT* -Online | Add-WindowsCapability -Online
- Export users:
Get-ADUser -Filter * -Properties * | Select-Object GivenName, Surname, DistinguishedName, Enabled | export-csv -path "$env:UserProfile\Desktop\aduserexport.csv"
You're done. The aduserexport.csv file is on your desktop.
Microsoft 365
- Run PowerShell as an Administrator.
- Install the module:
Install-Module Microsoft.Graph.Users - Connect:
Connect-MgGraph -Scopes "User.Read.All"If you are using older servers without Edge, you might need to add exceptions using the prompts. If the command fails with a JavaScript error in the browser, rerun the command.
- Export users:
Get-MgUser -Property givenName, surname, userprincipalname, accountEnabled -All | Select-Object givenname, surname, userprincipalname, accountenabled | export-csv -path "$env:UserProfile\Desktop\entrauserexport.csv"
You're done. The entrauserexport.csv file is on your desktop.
Google Workspace
- Login to your admin panel at https://admin.google.com.
- Go to Users under Directory.
- Click Download Users.
- Be sure Name and Status is in the list of Columns.
- Click CSV, then click Download.
You're done. Use the downloaded CSV file for the next steps.
Other Directories
For other directory systems, look up how to export users and be sure to export the First Name, Last Name, and Status fields. Note that this process can be used for any SaaS application as well; we are not just limited to directories.
2. MATCH
Match Preparation
Match preparation time. We extracted the data from both of our sources. Now, we must use the data by matching the two data sets. We will use a simple name match here, concatenating the first and last names. The simple name match will get us 90% of the matches for 10% of the work manual matching would take.
- Open a new Excel Worksheet or Google Sheet, feel free to use the template.
- Import the Directory CSV.
- In a separate sheet import the HR CSV.
- On both sheets, make a new column called "Match." We'll lower and concat the first and last names.
=LOWER(CONCAT(A2,B2))
This is the first row for us.
- Almost there!
Staging the Data
We extracted the data from both sources and set up a match field for each data source. Now, we need to match the two data sets to one another. Remember, we have a template available for your convenience. It's designed to simplify the process, making it easier than following these steps. However, we've included the steps just in case you encounter any issues with the template for your specific use case.
- Find duplicates by creating a "Duplicates" column and using:
=IF(COUNTIF(E:E, E2)>1, "duplicate", "")
Where E is the Match column.
- Create the Duplicates column for both the HR sheet and the Directory sheet.
- Go to the Directory sheet. We'll spend the rest of our time here.
- Create a new column called "HR Status" in the Directory sheet and populate it with:
=INDEX('HR Export'!D:D, MATCH(E2, 'HR Export'!E:E, 0))This points to the HR Export sheet, where column D is the HR Active Status. E2 is the Match value.
- We're done with the hard part. Pat yourself on the back!
3. MAKE THE DATA ACTIONABLE
We have all the data we need. Now, what do we do with all of this? Each step here is where the value comes from. Again, you can peek at the template, which has a third sheet called "Actionable Data."
1. Create a filter
Create a filter on the directory sheet (Data → Filter).
2. Find unused licenses
- Filter out any values that say "duplicate" in both duplicate columns.
- Filter HR Status to "Inactive" values.
- Filter the Enabled / Status field for the directory to "Active" values.
- You now have a list of orphaned accounts in your directory system.
3. Look into all unmatched accounts
(if you do not have inactive accounts in your HR data set, they will all exist here)
- Reset the filter (turn it off and back on).
- Filter to HR Status value is N/A (meaning it did not match anything).
- Create a column to comment about each of these accounts. Some will be service accounts you want to keep, but the rest are orphaned accounts that do not have HR accounts.
4. Review HR duplicates
- Reset the filter.
- Filter to just duplicate HR values.
- Talk to HR about these or manually match them to a directory account.
5. Review directory duplicates
- Reset the filter.
- Filter to just duplicate directory values.
- Are these duplicate accounts? Additional licenses could be hidden in these duplicates.
4. DISABLE THE ACCOUNTS
You've analyzed the data and found all the accounts you want to disable. Now go into Active Directory, Microsoft 365, Google Workspace (or whatever system you are matching with) and disable those accounts and licenses. Keep track of how much this saves your company, as you'll want to let your team and boss know about this later.
5. LET EVERYONE KNOW
We need to remember to do this step as a technical team. If someone on your team did the work, give your coworker all the credit and make them feel great. Otherwise, let your team and your boss know what you just accomplished. Here's an example email, Teams, or Slack message.
Team,
I just wanted to make sure you were aware that I disabled some accounts as I was cleaning up our Microsoft 365 environment using this Orphaned Account Detector guide. If anything breaks, reach out, and we can dive in together.
I found a bunch of accounts that were disabled in our HR system but enabled in Microsoft 365. We will save about $5,000 this year in license fees from Microsoft now that we have everything cleaned up!
Best,
Your Name
READY TO RAMP UP?
NEXT STEPS
There you have it. The Orphaned Account Detector: the exact strategy I've used to save my clients thousands of dollars of yearly license fees from Microsoft, Google, and other applications. Now that you know how to join data between external systems, you can do this for all your applications, securing and saving for you and your company.
It's your responsibility as the IT leader in your company to make this happen. If you're serious about moving your company toward automation, take the next step now.
You may qualify for hands-on help from me. To get help from me on building a culture of automation for your business, book a call now. On the call, we'll work together to determine the best way to help you automate your systems, starting with your onboarding and offboarding checklist.
I want you to promise me you'll invest some attention here. Do not let this be an underutilized tool sitting in your toolbox. Nothing stops business faster than a security breach, and implementing these steps closes one more door for the bad guys to get in.