Active Directory Password Quality Report in Power BI
Weak passwords, duplicate passwords, unknown admin accounts, the list goes on… These all present a serious threat to your company’s Active Directory security.
In this post, I’ll show you how to use the DSInternals PowerShell module to test Active Directory password quality and identify flaws in your AD security posture. Then, we’ll create a Power BI report to get a quick glance at the number of password quality issues, together with a list of users affected by each issue.
Table of Contents
Complete PowerShell Script
## Active Directory Password Quality Data Export ## #░█████╗░██╗░░░██╗██████╗░███████╗██████╗░██╗██╗░░░░░██╗░░░░░░█████╗░ #██╔══██╗╚██╗░██╔╝██╔══██╗██╔════╝██╔══██╗██║██║░░░░░██║░░░░░██╔══██╗ #██║░░╚═╝░╚████╔╝░██████╦╝█████╗░░██████╔╝██║██║░░░░░██║░░░░░██║░░██║ #██║░░██╗░░╚██╔╝░░██╔══██╗██╔══╝░░██╔══██╗██║██║░░░░░██║░░░░░██║░░██║ #╚█████╔╝░░░██║░░░██████╦╝███████╗██║░░██║██║███████╗███████╗╚█████╔╝ #░╚════╝░░░░╚═╝░░░╚═════╝░╚══════╝╚═╝░░╚═╝╚═╝╚══════╝╚══════╝░╚════╝░ # Define Variables $dictionary = ".\Dictionary.txt" $domain = "dc=contoso,dc=com" $dc = "10.10.10.1" # Retrieve AD Accounts and Test Password Quality $data = Get-ADReplAccount -All -Server $dc -NamingContext $domain | Test-PasswordQuality -WeakPasswordsFile $dictionary -IncludeDisabledAccounts # Define the Password Quality Criteria $qualityCriteria = @( "ClearTextPassword", "LMHash", "EmptyPassword", "WeakPassword", "SamAccountNameAsPassword", "DefaultComputerPassword", "PasswordNotRequired", "PasswordNeverExpires", "AESKeysMissing", "PreAuthNotRequired", "DESEncryptionOnly", "Kerberoastable", "DelegatableAdmins", "SmartCardUsersWithPassword", "DuplicatePasswordGroups" ) # Initialize an array to store expanded data $expandedData = @([pscustomobject]@{ "ClearTextPassword" = "" "LMHash" = "" "EmptyPassword" = "" "WeakPassword" = "" "SamAccountNameAsPassword" = "" "DefaultComputerPassword" = "" "PasswordNotRequired" = "" "PasswordNeverExpires" = "" "AESKeysMissing" = "" "PreAuthNotRequired" = "" "DESEncryptionOnly" = "" "Kerberoastable" = "" "DelegatableAdmins" = "" "SmartCardUsersWithPassword" = "" "DuplicatePasswordGroups" = "" }) # Iterate through each account and expand the criteria into separate rows foreach ($account in $data) { foreach ($criterion in $qualityCriteria) { # Check if the criterion contains values if ($account.PSObject.Properties[$criterion].Value -and $account.$criterion.Count -gt 0) { # Expand each item in the collection into a new row foreach ($user in $account.$criterion) { $expandedData += [pscustomobject]@{ "$Criterion" = $user } } } } } # Export all expanded data into a single CSV $expandedData | Export-Csv -Path "PasswordQuality.csv" -NoTypeInformation Write-Host "Exported expanded data to PasswordQuality.csv with $($expandedData.Count) records."
Script Breakdown
We’ll start with the PowerShell script that gathers all the information we need from Active Directory.
Here’s what it does:
- Pulls AD account information from your domain controller (DC).
- Checks password quality based on various criteria, like whether the password is weak, missing, or stored improperly.
- Expands the data into a more readable format.
- Exports the results to a CSV file for analysis.
1. Setting Up Your Variables
# Define Variables $dictionary = ".\Dictionary.txt" $domain = "dc=contoso,dc=com" $dc = "10.10.10.1"
You need to define a few things at the start: where the script can find a list of weak passwords (Dictionary.txt
), your domain name, and the IP address of your domain controller. Change these to match your environment.
You can customize the dictionary as needed, but a good starting point can be found here.
2. Getting AD Accounts and Testing Password Quality
$data = Get-ADReplAccount -All -Server $dc -NamingContext $domain | Test-PasswordQuality -WeakPasswordsFile $dictionary -IncludeDisabledAccounts
This section uses the Get-ADReplAccount
and Test-PasswordQuality
cmdlets from the DSInternals PowerShell module.
It retrieves all the accounts in your domain and checks their password quality. We’re looking for things like weak passwords, missing encryption, or even accounts that use their username as a password (yikes!). This script can also include disabled accounts—because even they can be a security risk if left unchecked.
3. Defining Password Quality Criteria
$qualityCriteria = @( "ClearTextPassword", "LMHash", "EmptyPassword", "WeakPassword", "SamAccountNameAsPassword", "DefaultComputerPassword", "PasswordNotRequired", "PasswordNeverExpires", "AESKeysMissing", "PreAuthNotRequired", "DESEncryptionOnly", "Kerberoastable", "DelegatableAdmins", "SmartCardUsersWithPassword", "DuplicatePasswordGroups" )
This part sets the criteria we care about—whether the password is empty, weak, or stored as an old LMHash, just to name a few. These are the key things that pose risks, and the Test-PasswordQuality
cmdlet checks for all of them.
4. Storing and Expanding the Data
$expandedData = @() foreach ($account in $data) { foreach ($criterion in $qualityCriteria) { if ($account.PSObject.Properties[$criterion].Value -and $account.$criterion.Count -gt 0) { foreach ($user in $account.$criterion) { $expandedData += [pscustomobject]@{ "$criterion" = $user } } } } }
This portion of the script is all about organizing the data in a way that’s easy to analyze and visualize later on, especially when we move the data into Power BI. Let’s break it down step-by-step:
A) Initializing the Expanded Data Array
$expandedData = @()
- Purpose: Here, we’re creating an empty array called
$expandedData
. This array will store each password quality issue we find, structured in a consistent format. - Why It Matters: By initializing an empty array, we ensure that we have a clean slate to start adding our processed data. This helps in avoiding any unintended data carryover from previous runs or other parts of the script.
B) Looping Through Each AD Account
foreach ($account in $data) { ... }
- Purpose: This outer loop goes through each account retrieved earlier by the
Get-ADReplAccount
cmdlet. - Why It Matters: We need to evaluate each account individually to check for any password-related issues. This ensures that no account is overlooked in our analysis.
C) Checking Each Password Quality Criterion
foreach ($criterion in $qualityCriteria) { ... }
- Purpose: For every account, we loop through each password quality criterion defined in the
$qualityCriteria
array. - Why It Matters: Each criterion represents a specific type of password issue (e.g., weak password, empty password). By iterating through each one, we can systematically check for all possible vulnerabilities associated with the account.
D) Confirm Existence of Password Quality Issues
if ($account.PSObject.Properties[$criterion].Value -and $account.$criterion.Count -gt 0) { ... }
- Purpose: This
if
statement checks two things:$account.PSObject.Properties[$criterion].Value
: Ensures that the current criterion has a value, meaning that there is at least one instance of this issue for the account.$account.$criterion.Count -gt 0
: Confirms that the number of issues under this criterion is greater than zero.
- Why It Matters: We only want to record criteria that are actually present. This prevents our final data set from being cluttered with empty or irrelevant entries, making our analysis cleaner and more focused on real issues.
E) Expanding Each Issue into a New Row
foreach ($user in $account.$criterion) { $expandedData += [pscustomobject]@{ "$criterion" = $user } }
- Purpose: For each issue found under the current criterion, we create a new custom PowerShell object and add it to the
$expandedData
array.[pscustomobject]@{ "$criterion" = $user }
: This creates a new object with a property named after the current criterion and assigns it the value of the specific issue ($user
).$expandedData += ...
: This appends the newly created object to the$expandedData
array.
- Why It Matters: By expanding each issue into its own row, we transform our data into a flat, tabular format that’s ideal for analysis and visualization. This structure is especially useful when importing the data into Power BI, as it allows for easy creation of filters, charts, and other visuals based on specific criteria.
F) Putting It All Together
Let’s visualize what’s happening with an example:
- Suppose we have an AD account,
jdoe
, with the following password issues:- WeakPassword:
password123
- PasswordNeverExpires: Enabled
- WeakPassword:
- Processing Steps:
- First Loop (
$account
): Processes thejdoe
account. - Second Loop (
$criterion
): Checks each criterion forjdoe
.WeakPassword
:- Condition Check:
True
(sincepassword123
is listed in the dictionary) - Inner Loop: Adds a new object
{ "WeakPassword" = "jdoe" }
to$expandedData
.
- Condition Check:
PasswordNeverExpires
:- Condition Check:
True
(since it’s enabled) - Inner Loop: Adds another object
{ "PasswordNeverExpires" = "jdoe" }
to$expandedData
.
- Condition Check:
- Other Criteria: If
jdoe
doesn’t have issues likeEmptyPassword
orLMHash
, those criteria are skipped and no new rows are created.
- First Loop (
This flattened structure makes it straightforward to create visuals in Power BI, such as:
- Bar Charts: Showing the number of accounts with each type of password issue.
- Tables: Listing all accounts alongside their specific vulnerabilities.
- Pie Charts: Representing the proportion of each issue relative to the total number of issues.
G) Why Use This Approach?
- Simplicity: By expanding each issue into its own row, the data becomes easier to work with, especially when dealing with multiple criteria across numerous accounts.
- Flexibility: This format allows you to filter, sort, and visualize the data with little extra data manipulation.
- Scalability: As your organization grows and the number of AD accounts increases, this method remains efficient and manageable.
5. Exporting to CSV
$expandedData | Export-Csv -Path "PasswordQuality.csv" -NoTypeInformation Write-Host "Exported expanded data to PasswordQuality.csv with $($expandedData.Count) records."
Finally, all the findings get exported to a CSV file, which we’ll use as a data source to create our Power BI report. The CSV will contain the password quality issues as columns, and one user per row (under the appropriate column). One user can have multiple rows in the CSV if his account has more than one password quality issue.
Create a Power BI Report
Now that we have the data in PasswordQuality.csv
, it’s time to visualize it in Power BI. This will help you spot trends and focus on the biggest security risks.
Steps:
- Open Power BI and go to Home -> Get Data -> Text/CSV.
- Browse to your exported
PasswordQuality.csv
and click Transform Data.
- In the Power Query editor, click on Use First Row as Headers.
- Then, click on Replace Values.
- Leave Value to find blank and enter
null
in the Replace With field. Then, click on OK. This will take care of the blank values in our data.
- Save the Power Query changes by clicking on Close & Apply.
- This next step is crucial. We will use our data to create a new table with two columns, User and Issue. This will make it easier to visualize and filter the data. Go to Modeling -> New table.
- In the DAX query, type the following code:
Issues = VAR _AESKeysMissing = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[AESKeysMissing], "Issue", "AESKeysMissing" ) VAR _ClearTextPassword = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[ClearTextPassword], "Issue", "ClearTextPassword" ) VAR _DESEncryptionOnly = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[DESEncryptionOnly], "Issue", "DESEncryptionOnly" ) VAR _DefaultComputerPassword = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[DefaultComputerPassword], "Issue", "DefaultComputerPassword" ) VAR _DelegatableAdmins = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[DelegatableAdmins], "Issue", "DelegatableAdmins" ) VAR _DuplicatePasswordGroups = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[DuplicatePasswordGroups], "Issue", "DuplicatePasswordGroups" ) VAR _EmptyPassword = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[EmptyPassword], "Issue", "EmptyPassword" ) VAR _Kerberoastable = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[Kerberoastable], "Issue", "Kerberoastable" ) VAR _LMHash = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[LMHash], "Issue", "LMHash" ) VAR _PasswordNeverExpires = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[PasswordNeverExpires], "Issue", "PasswordNeverExpires" ) VAR _PasswordNotRequired = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[PasswordNotRequired], "Issue", "PasswordNotRequired" ) VAR _PreAuthNotRequired = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[PreAuthNotRequired], "Issue", "PreAuthNotRequired" ) VAR _SamAccountNameAsPassword = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[SamAccountNameAsPassword], "Issue", "SamAccountNameAsPassword" ) VAR _SmartCardUsersWithPassword = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[SmartCardUsersWithPassword], "Issue", "SmartCardUsersWithPassword" ) VAR _WeakPassword = SELECTCOLUMNS ( PasswordQuality, "User", PasswordQuality[WeakPassword], "Issue", "WeakPassword" ) RETURN DISTINCT ( UNION ( _AESKeysMissing, _ClearTextPassword, _DESEncryptionOnly, _DefaultComputerPassword, _DelegatableAdmins, _DuplicatePasswordGroups, _EmptyPassword, _Kerberoastable, _LMHash, _PasswordNeverExpires, _PasswordNotRequired, _PreAuthNotRequired, _SamAccountNameAsPassword, _SmartCardUsersWithPassword, _WeakPassword ) )
- Now, you can visualize the data as needed. A report which I find convenient is a donut chart with the Issue column as the legend, and the count of the User column as the values. This shows the most common password quality issues in the domain. The list of users affected by each issue can be filtered by clicking on the different donut slices.
Wrapping Up
And that’s it! You’ve now used a PowerShell script to gather AD password quality data and created a Power BI report to visualize it. This is a powerful way to stay on top of password security, spot potential vulnerabilities, and make sure your organization’s accounts are protected.
Remember, scheduling the script at regular intervals (and refreshing the report data) can help you stay ahead of any security risks related to weak passwords. And with Power BI, you can easily share the findings and keep the rest of your team in the loop.
Let me know if you have any questions or if you’d like to see more advanced Power BI visuals. Stay secure!