Microsoft

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.

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:

  1. Pulls AD account information from your domain controller (DC).
  2. Checks password quality based on various criteria, like whether the password is weak, missing, or stored improperly.
  3. Expands the data into a more readable format.
  4. 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:
    1. $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.
    2. $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
  • Processing Steps:
    1. First Loop ($account): Processes the jdoe account.
    2. Second Loop ($criterion): Checks each criterion for jdoe.
      • WeakPassword:
        • Condition Check: True (since password123 is listed in the dictionary)
        • Inner Loop: Adds a new object { "WeakPassword" = "jdoe" } to $expandedData.
      • PasswordNeverExpires:
        • Condition Check: True (since it’s enabled)
        • Inner Loop: Adds another object { "PasswordNeverExpires" = "jdoe" } to $expandedData.
      • Other Criteria: If jdoe doesn’t have issues like EmptyPassword or LMHash, those criteria are skipped and no new rows are created.

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:

  1. Open Power BI and go to Home -> Get Data -> Text/CSV.
Load data from CSV in Power BI
  1. Browse to your exported PasswordQuality.csv and click Transform Data.
Transform CSV Data in Power BI
  1. In the Power Query editor, click on Use First Row as Headers.
Click on Use First Row as Headers in Power BI
  1. Then, click on Replace Values.
Click on Replace Values in Power BI
  1. 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.
Replace blanks with null in Power BI
  1. Save the Power Query changes by clicking on Close & Apply.
Save Power Query Changes in Power BI
  1. 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.
Create new table in Power BI
  1. 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
        )
    )
  1. 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.
Number of Users Affected by Each Issue Power BI Report

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button