TL;DR How to bounce ATCTS off AD IOT ID users who are in one but not the other. They should match almost perfectly, but does your organization’s do so in practice?
Background
Higher has dictated that your organization’s ATCTS compliance needs to be 95% instead of 90%. As we all know users will spend more time arguing about why they didn’t do their annual cyber awareness training than taking the 15–20 minutes to actually do it. The annual AUP takes 30 seconds, but compliance with that tends to run even lower.
We can force compliance by disabling or expiring the noncompliant users’ accounts in AD.
We can also tackle the proverbial low hanging fruit by identifying users who are in our organization’s ATCTS but not our OU in AD, then inactivating them in ATCTS.
AD attributes that will be important in this are shown below:
Export ATCTS users
Everyone already generates reports showing who has not done their cyber awareness training and/or AUP within the last year. The trick is to modify the report, add the DoD ID numbers of the users, and remove all other data points. ATCTS calls this the EDIPI. Either copy/paste the entire column of DoD ID #s into notepad and save as ATCTS.txt or simply save to CSV from Excel, then open the CSV in Notepad as save as ATCTS.txt.
Export AD users
We need a list of all the users in our organization’s OU by their DoD ID #s. It is tempting to use the AD attribute EmployeeID, however I have found that in practice this is unworkable. This is due to the people that create our accounts. The user will complain if their UserPrincipalName (UPN) is incorrect as a fat finger error in the UPN will stop them from logging in. Hence the UPN works much better for our purposes.
We can export the users in our OU as shown below (OU path/domain are fictional, adjust to your org’s path):
((Get-ADUser -Filter * -SearchBase “ou=BDE,ou=DIV,ou=CORP,ou=Users,ou=_US,dc=us,dc=test,dc=local” -Properties *).UserPrincipalName).substring(0,10) | Out-File .\AD.txtSidenote on the usage of text files
I tried to bounce two CSV files off each other in PowerShell, but all I got after much Google and trial & error was a migraine. Then the thought occurred to me, why not just use simple *.txt files? PowerShell has no issue pulling data from or outputting data into CSV or text files. Comparing two text files was quick and easy, and we can output the results into CSV.
Compare the files
Next either take advantage of PowerShell’s compatibility with some BASH commands:
diff (cat AD.txt) (cat ATCTS.txt)Or use PowerShell’s command for this:
Compare-Object (Get-Content .\AD.txt) (Get-Content .\ATCTS.txt) | Export-Csv diff.csvThe order of which file comes first in the command is critically important. I have always put AD first, then ATCTS. You can use whichever order makes you happy, however the rest of this howto describes my method. If you flip the order then the output will be flipped as well, FYSA.
Interpret the output
<= means the user is in AD but not ATCTS
=> means the user is in ATCTS but not AD
Split the halves of the resulting CSV, remove the column of arrows, and save as InADNotATCTS.csv and InATCTSNotAD.csv. We can then use the output.
Please note that this is just our BDE’s OU in AD and just our BDE’s ATCTS. The user could be in the wrong OU or the wrong unit in ATCTS. We will test for that next.
Action the output, simple version
Make a subfolder with a name that makes sense to you, cd into it, then run the CSV of people who are in ATCTS but not AD through Users_in_ATCTS_not_AD.ps1. The simple version is below:
#Get the list
$Users = Get-Content .\InATCTSNotAD.csv
#Process the list
ForEach ($user in $users)
{
#Get the user's info from AD
#Example: ('GivenName -like "' + $name + '*"')
$info = Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties * | Select-Object CanonicalName
"'($info)'" >> test.csv
}
This simple version simply finds out whether the user is even still around, and if so outputs the CanonicalName into a CSV file. You can then simply paste the column from test.csv into InATCTSNotAD.csv and have a spreadsheet of DoD ID #s from ATCTS with the CanonicalName for each one, if the user exists in AD. If they do not then ‘()’ will be in the column for CanonicalName. I have seen this far too often.
An example was a MSG who retired and was never removed from ATCTS. He was still dinging our compliance % in ATCTS a year later. This is the proverbial low hanging fruit. Just inactivate or remove users like that from your organization’s ATCTS.
Action the output, complex version
What if the user is still around, but is in a different org’s OU? Maybe they PCSed but failed to properly out-process. Perhaps their new unit did not pull them in and they are hanging out in a Transient OU. What if we want more data than just the CanonicalName?
In order to provide more data points I created a more complex version of the earlier script and made it a function. I also put
- ActiveDirectory.Format.ps1xml
- ActiveDirectory.psd1
- ActiveDirectory.Types.ps1xml
- Microsoft.ActiveDirectory.Management.dll
in my subfolder for this script. This allows running the query on any workstation, not just ones that have RSAT installed already.
You can either “dot source” the function by ‘ . .\ Users_in_ATCTS_not_AD.ps1 ‘ or do a ‘ Import-Module .\ Users_in_ATCTS_not_AD.ps1 ‘
Once that is done simply do a ‘ Get-ADInfo InATCTSNotAD.csv ‘
Import-Module .\Microsoft.ActiveDirectory.Management.dll
Import-Module .\ActiveDirectory.psd1
Write-Host "This function steps through a CSV of DoD ID #s from ATCTS and creates CSVs with the data"
Write-Host "The syntax is 'Get-ADInfo Filename' ."
Function Get-ADInfo {
param (
[parameter(Mandatory=$False)]
[ValidateNotNullOrEmpty()]$File
)
$users = Get-Content $File
#Process the list (CanonicalName)
ForEach ($user in $users)
{
#Get the user's info from AD
$info = (Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties *).CanonicalName
"'($info)'" >> CanonicalName.csv
}
ForEach ($user in $users)
{
#Get the user's info from AD
$info = (Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties *).Enabled
"'($info)'" >> Enabled.csv
}
ForEach ($user in $users)
{
#Get the user's info from AD
$info = ((Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties *).DistinguishedName -split ",")[1]
"'($info)'" >> DistinguishedName.csv
}
ForEach ($user in $users)
{
#Get the user's info from AD
$info = (Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties *).CreateTimeStamp
"'($info)'" >> CreateTimeStamp.csv
}
ForEach ($user in $users)
{
$info = (Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties *).LastLogonDate
"'($info)'" >> LastLogonDate.csv
}
ForEach ($user in $users)
{
$info = (Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties *).EmailAddress
"'($info)'" >> EmailAddress.csv
}
}
As before, ‘()’ means the user is not in AD at all. Obviously if they are not in AD, then all the resulting CSVs will have ‘()’ for that DoD ID #. As before, just copy/paste whatever data you need back into the original CSV as a column.
The EmailAddress.csv file comes in handy for sending out a form email to any users who are hanging out in Transient
Why a separate CSV for each AD attribute
Could I have put all the AD attributes in one CSV? Yes, but then instead of ‘()’ for users who are not in AD the script would skip that DoD ID and keep going. This causes the original CSV of DoD ID #s to not match up with the resulting output. Since the whole point of this is to find users who are NOT in AD I left the script as shown.
Users in AD but not ATCTS
These users by definition will not be on any ATCTS compliance hitlist. It is good to be aware of though. I ran across a few too many in this category during audits.
Take the InADNotATCTS.csv file from earlier, save it somewhere handy, and run this script from that same folder:
#Export a full report with Name, DoD ID #, OU, EmailAddress, DTG account was created, & DTG of last logon for all users who are in AD but NOT in ATCTS
#Get the list
$Users = Get-Content .\InADNotATCTS.csv
#Process the list
ForEach ($user in $users)
{
If ((Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties *).Name -ne $null)
{
#Get the user's info from AD
Get-ADUser -Filter ('UserPrincipalName -Like "' + $user + '*"') -Properties * | Select-Object Name, {$_.UserPrincipalName.substring(0,10)}, CanonicalName, EmailAddress, CreateTimeStamp, LastLogonDate | Export-Csv testingIV.csv -Append
}
Else{echo "no AD account" | Export-Csv testingIV.csv -Append
}
}Summary
All this took me a bit of Microsoft training, home labbing, Google, and trial & error. Once I had it hobbled together though it came in really handy for cleaning up ATCTS and auditing in general. If you have any questions just shoot me a message on FB or my work email.
-Rich
References:
