Automation: Comparing Account Inventory to Active Directory Accounts

This is the first time I have shared something like this. I’ve actually created a ton of python scripts to automate things that need to be done at a certain frequency. It just speeds up the process. If you like this kind of info, let me know in the comments and I will share more. I’m also planning on adding them to a community website soon.

I use python because it works with Windows, Mac, etc. as long as python is installed.

This script is useful if you keep an account inventory of all authorized accounts. A lot of companies want to use their AD export as evidence, but if you have a list of accounts that you know are authorized, then you can use it as a source of truth and compare it to your AD export to find any accounts that are not authorized, possibly ones that someone forgot to disable or remove, etc.

---

This is what the Script Does:

Finds missing, inactive, or unauthorized accounts
Detects mismatches in security groups, expiration dates, and privileged accounts
Generates an Excel report (discrepancy_report.xlsx)
Sends an Email Notification (SMTP-based) with detected discrepancies
Sends logs to Microsoft Sentinel for real-time security monitoring

This script assumes:
• The Account Inventory is in a CSV file named account_inventory.csv.
• The Active Directory export is in a CSV file named active_directory_export.csv.
• Both files contain a “User ID” column (used for matching users).

How to Use It

1. Copy the code below and paste it into a text editor like Notepad or BbEdit, change the variables in the Send Email Notification for Discrepancies and the Log Discrepancies to Microsoft Sentinel sections.
2. Save the text file as account-comparison.py
3. Export Account Inventory (save as account_inventory.csv).
   If your Account Inventory is currently in Excel, save it as account_inventory.csv.
   Ensure it contains columns like:
   User ID, Account Status, Security Group(s), Account Expiration Date, Privileged Account
4. Export Active Directory accounts (e.g., using PowerShell, save as active_directory_export.csv):
   Get-ADUser -Filter * -Property sAMAccountName, Enabled, MemberOf, AccountExpirationDate, AdminCount |
   Select-Object sAMAccountName, Enabled, MemberOf, AccountExpirationDate, AdminCount |
   Export-Csv -Path “C:\active_directory_export.csv” -NoTypeInformation
5. Move active_directory_export.csv to the same directory as the script.
6. Once both CSV files are in place, run the script to compare them and generate a discrepancy report.
7. Run the script in Python: python account-comparison.py
8. The script outputs discrepancy_report.xlsx, containing:
   • Accounts missing in AD
   • Accounts missing in Inventory
   • Status mismatches
   • Security group mismatches
   • The script will also send detected discrepancies to Microsoft Sentinel.

import pandas as pd
import smtplib
import json
import requests
from datetime import datetime
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

# Load Account Inventory (maintained manually)
account_inventory = pd.read_csv("account_inventory.csv")

# Load Active Directory Export (exported from AD)
active_directory = pd.read_csv("active_directory_export.csv")

# Standardizing column names (ensure they match in both files)
account_inventory.rename(columns={
    "User ID": "UserID", "Account Status": "Status", "Security Group(s)": "SecurityGroups",
    "Account Expiration Date": "ExpirationDate", "Privileged Account": "Privileged"
}, inplace=True)

active_directory.rename(columns={
    "sAMAccountName": "UserID", "Enabled": "Status", "MemberOf": "SecurityGroups",
    "AccountExpirationDate": "ExpirationDate", "AdminCount": "Privileged"
}, inplace=True)

# Convert Status to a common format
account_inventory["Status"] = account_inventory["Status"].str.lower()
active_directory["Status"] = active_directory["Status"].apply(lambda x: "active" if x else "inactive")

# Convert ExpirationDate to datetime format
account_inventory["ExpirationDate"] = pd.to_datetime(account_inventory["ExpirationDate"], errors='coerce')
active_directory["ExpirationDate"] = pd.to_datetime(active_directory["ExpirationDate"], errors='coerce')

# Convert Privileged account status to common format
account_inventory["Privileged"] = account_inventory["Privileged"].apply(lambda x: "Yes" if x == "Yes" else "No")
active_directory["Privileged"] = active_directory["Privileged"].apply(lambda x: "Yes" if x == 1 else "No")

# Find missing accounts
missing_in_ad = account_inventory[~account_inventory["UserID"].isin(active_directory["UserID"])]
missing_in_inventory = active_directory[~active_directory["UserID"].isin(account_inventory["UserID"])]

# Find mismatched attributes (e.g., account status, security groups, expiration date, privileged flag)
merged = account_inventory.merge(active_directory, on="UserID", suffixes=('_Inventory', '_AD'))
status_mismatches = merged[merged["Status_Inventory"] != merged["Status_AD"]]
security_group_mismatches = merged[merged["SecurityGroups_Inventory"] != merged["SecurityGroups_AD"]]
expiration_date_mismatches = merged[merged["ExpirationDate_Inventory"] != merged["ExpirationDate_AD"]]
privileged_account_mismatches = merged[merged["Privileged_Inventory"] != merged["Privileged_AD"]]

# Generate a report
discrepancy_report = {
    "Missing in Active Directory": missing_in_ad,
    "Missing in Account Inventory": missing_in_inventory,
    "Status Mismatches": status_mismatches,
    "Security Group Mismatches": security_group_mismatches,
    "Expiration Date Mismatches": expiration_date_mismatches,
    "Privileged Account Mismatches": privileged_account_mismatches
}

# Save report to an Excel file
report_filename = "discrepancy_report.xlsx"
with pd.ExcelWriter(report_filename) as writer:
    for sheet_name, df in discrepancy_report.items():
        df.to_excel(writer, sheet_name=sheet_name, index=False)

print(f"Discrepancy report generated: {report_filename}")

### ** Send Email Notification for Discrepancies**
EMAIL_SENDER = "admin@company.com"
EMAIL_RECEIVER = "security_team@company.com"
EMAIL_PASSWORD = "YourPassword"
SMTP_SERVER = "smtp.office365.com"
SMTP_PORT = 587

def send_email():
    msg = MIMEMultipart()
    msg['From'] = EMAIL_SENDER
    msg['To'] = EMAIL_RECEIVER
    msg['Subject'] = " Account Inventory Discrepancy Report"

    email_body = f"""
    <h2>Account Inventory Discrepancy Detected</h2>
    <p>Please review the attached report for details on:</p>
    <ul>
        <li>Accounts missing in Active Directory</li>
        <li>Accounts missing in Inventory</li>
        <li>Status mismatches</li>
        <li>Security group mismatches</li>
        <li>Expiration date mismatches</li>
        <li>Privileged account mismatches</li>
    </ul>
    <p>Report file: {report_filename}</p>
    """
    
    msg.attach(MIMEText(email_body, 'html'))

    try:
        server = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
        server.starttls()
        server.login(EMAIL_SENDER, EMAIL_PASSWORD)
        server.sendmail(EMAIL_SENDER, EMAIL_RECEIVER, msg.as_string())
        server.quit()
        print("Email notification sent!")
    except Exception as e:
        print(f" Failed to send email: {e}")

send_email()

### **Log Discrepancies to Microsoft Sentinel**
M365_WORKSPACE_ID = "YOUR_WORKSPACE_ID"
M365_SHARED_KEY = "YOUR_SHARED_KEY"
LOG_TYPE = "AccessManagementDiscrepancies"

def send_to_sentinel(log_data):
    uri = f"https://{M365_WORKSPACE_ID}.ods.opinsights.azure.com/api/logs?api-version=2016-04-01"
    log_json = json.dumps(log_data)
    content_length = len(log_json)

    headers = {
        "Content-Type": "application/json",
        "Authorization": f"SharedKey {M365_WORKSPACE_ID}:{M365_SHARED_KEY}",
        "Log-Type": LOG_TYPE,
        "x-ms-date": datetime.utcnow().strftime('%a, %d %b %Y %H:%M:%S GMT')
    }

    response = requests.post(uri, headers=headers, data=log_json)
    if response.status_code == 200:
        print(" Successfully sent logs to Microsoft Sentinel.")
    else:
        print(f" Failed to send logs. Status Code: {response.status_code}, Response: {response.text}")

# Prepare log entries for Sentinel
log_entries = []
for key, df in discrepancy_report.items():
    for _, row in df.iterrows():
        log_entries.append({
            "DiscrepancyType": key,
            "UserID": row.get("UserID", "N/A"),
            "Status_Inventory": row.get("Status_Inventory", "N/A"),
            "Status_AD": row.get("Status_AD", "N/A"),
            "SecurityGroups_Inventory": row.get("SecurityGroups_Inventory", "N/A"),
            "SecurityGroups_AD": row.get("SecurityGroups_AD", "N/A"),
            "ExpirationDate_Inventory": str(row.get("ExpirationDate_Inventory", "N/A")),
            "ExpirationDate_AD": str(row.get("ExpirationDate_AD", "N/A")),
            "Privileged_Inventory": row.get("Privileged_Inventory", "N/A"),
            "Privileged_AD": row.get("Privileged_AD", "N/A"),
        })

# Send logs to Microsoft Sentinel
if log_entries:
    send_to_sentinel(log_entries)