dpat.py

#!/usr/bin/python

import webbrowser
import io
import os
import re
import argparse
import sqlite3
import sys
import json
import csv
import tzlocal
from datetime import datetime
from importlib.metadata import version
import pyad 
from pyad import adquery, adgroup, aduser, pyadutils
from win32com import client
from pywintypes import TimeType
from shutil import copyfile
try:
    import html as htmllib
except ImportError:
    import cgi as htmllib  
import binascii
import hashlib
from distutils.util import strtobool
from pprint import pprint

# show only the first and last char of a password or a few more chars for a hash
def sanitize(pass_or_hash,protect):
    if not protect:
        return pass_or_hash
    else:
        sanitized_string = pass_or_hash
        lenp = len(pass_or_hash)
        if lenp == 32:
            sanitized_string = pass_or_hash[0:4] + \
                "*"*(lenp-8) + pass_or_hash[lenp-5:lenp-1]
        elif lenp > 2:
            sanitized_string = pass_or_hash[0] + \
                "*"*(lenp-2) + pass_or_hash[lenp-1]
        return sanitized_string

# nt2lmcrack functionality
# the all_casings functionality was taken from https://github.com/BBerastegui/foo/blob/master/casing.py
def all_casings(input_string):
    if not input_string:
        yield ""
    else:
        first = input_string[:1]
        if first.lower() == first.upper():
            for sub_casing in all_casings(input_string[1:]):
                yield first + sub_casing
        else:
            for sub_casing in all_casings(input_string[1:]):
                yield first.lower() + sub_casing
                yield first.upper() + sub_casing

def crack_it(nt_hash, lm_pass):
    password = None
    for pwd_guess in all_casings(lm_pass):
        hash = hashlib.new('md4', pwd_guess.encode('utf-16le')).hexdigest()
        if nt_hash.lower() == hash.lower():
            password = pwd_guess
            break
    return password

def get_cn_by_samaccountname(samaccountname,base_dn):
    # lookup CN by samaccountname
    q = adquery.ADQuery()
    q.execute_query(attributes=['CN'],where_clause="SamAccountName = '{0}'".format(samaccountname),base_dn=base_dn)
    user_cn = q.get_all_results()
    if len(user_cn) == 0:
        return None
    else:
        user_cn = user_cn[0]['CN']
    return user_cn

def get_aduser_properties(username,username_full,properties,base_dn):
    if not isinstance(properties, list):
        properties = [properties]
    tz = tzlocal.get_localzone()
    entity = dict()
    try: 
        user = aduser.ADUser.from_cn(username)
    except: 
        user = aduser.ADUser.from_cn(get_cn_by_samaccountname(username,base_dn))
    try:
        for prop in properties:
            # have to pull these out of UAC settings
            if prop == "Enabled":
                attr = not user.get_user_account_control_settings()['ACCOUNTDISABLE']
            elif prop == "PasswordExpired":
                attr = user.get_user_account_control_settings()['PASSWORD_EXPIRED']
            elif prop == "PasswordNeverExpires":
                attr = user.get_user_account_control_settings()['DONT_EXPIRE_PASSWD']
            elif prop == "PasswordNotRequired":
                attr = user.get_user_account_control_settings()['PASSWD_NOTREQD']
            else:
                attr = user.get_attribute(prop)
            attr = attr[0] if isinstance(attr,list) and len(attr) >= 1 else attr
            if isinstance(attr,TimeType):
                # these will be in UTC
                attr = attr.isoformat()
            if isinstance(attr,client.CDispatch):
                # these will be in local time
                # convert localtime to UTC
                dt = pyadutils.convert_datetime(attr)
                tz_aware = dt.replace(tzinfo=tz)
                attr = datetime.utcfromtimestamp(tz_aware.timestamp()).isoformat() + "+00:00"
            if isinstance(attr,bool):
                attr = str(attr)
            entity[prop] = attr
        entity['username_full'] = username_full
    except:
        print("Couldn't find account: {0}".format(username))
    return entity

def get_group_members(base_dn,group_cns=None):
    group_info = dict()
    if not isinstance(group_cns,list):
        group_cns = [group_cns]
    # if no groups specified, get all groups
    if len(group_cns) < 1 and group_cns[0] is None:
        # check pyad version is .5.15 or can'd use ldap_dialect param
        # https://docs.microsoft.com/en-us/windows/win32/adsi/search-filter-syntax
        # below will get only security groups... 
        print("Attempting to retrieve all groups...")
        q = adquery.ADQuery()
        if version('pyad') == '0.5.15':
            q.execute_query(attributes=['CN'],
                where_clause="(&(objectCategory=group)(groupType:1.2.840.113556.1.4.803:=2147483648))",
                base_dn=base_dn,ldap_dialect=True)
        else:
            q.execute_query(attributes=['CN'],
                where_clause="objectClass='group'",
                base_dn=base_dn)
        group_cns = [g['CN'] for g in q.get_all_results()]
    print("Found {0} groups".format(len(group_cns)))
    for cn in group_cns:
        # get group members and their properties
        print("Processing {0}".format(cn))
        entities = list()
        members = list()
        group = adgroup.ADGroup.from_cn(cn)
        members = group.get_members(recursive=True,ignoreGroups=True)
        # if there are members, get the desired attributes
        if len(members) >= 1:
            for member in members:
                # Foreign SIDS will cause problems
                if member._type == 'foreign-security-principal':
                    print("Skipping foreign SID\n{0}".format(member.adsPath))
                    continue
                upn = member.get_attribute('UserPrincipalName')
                upn = upn[0] if len(upn) >= 1 else None
                if upn is None:
                    continue
                username_full = "{0}\\{1}".format(upn.split('@')[1],upn.split('@')[0])
                entities.append(username_full)
            group_info[cn] = entities
    return group_info

class HtmlBuilder:
    def __init__(self,folder_for_html_report,protect):
        self.bodyStr = ""
        self.folder_for_html_report = folder_for_html_report
        self.protect = protect

    def build_html_body_string(self, str):
        self.bodyStr += str + "</br>\n"

    def get_html(self):
        return "<!DOCTYPE html>\n" + "<html>\n<head>\n<link rel='stylesheet' href='report.css'>\n</head>\n" + "<body>\n" + self.bodyStr + "</html>\n" + "</body>\n"

    def add_table_to_html(self, table_list, headers=[], col_to_not_escape=None):
        html = '<table border="1">\n'
        html += "<tr>"
        for header in headers:
            if header is not None:
                html += "<th>" + str(header) + "</th>"
            else:
                html += "<th></th>"
        html += "</tr>\n"
        for line in table_list:
            html += "<tr>"
            col_num = 0
            for column in line:
                if column is not None:
                    col_data = column
                    if ((("Password") in headers[col_num] and not ("Password Length" in headers[col_num] \
                        or "PasswordExpired" in headers[col_num] or "PasswordNeverExpires" in headers[col_num] or "PasswordNotRequired" in headers[col_num])) \
                            or ("Hash" in headers[col_num]) or ("History" in headers[col_num])):
                        col_data = sanitize(column,self.protect)
                    if col_num != col_to_not_escape:
                        col_data = htmllib.escape(str(col_data))
                    html += "<td>" + col_data + "</td>"
                else:
                    html += "<td></td>"
                col_num += 1
            html += "</tr>\n"
        html += "</table>"
        self.build_html_body_string(html)

    def write_html_report(self, filename):
        with open(os.path.join(self.folder_for_html_report, filename), "w") as f:
            copyfile('report.css', os.path.join(self.folder_for_html_report, "report.css"))
            f.write(self.get_html())
        return filename

class DPAT():
    def __init__(self,ntds_file,cracked_file,grouplists=None,groupdirectory=None,pullFromAD=False,baseDN=None,machineaccts=False,\
    filename_for_html_report="_DomainPasswordAuditReport.html",folder_for_html_report="DPAT Report",writedb=False,sanitize_results=False,\
    filename_for_db_on_disk="pass_audit.db",ad_properties=['Enabled','PwdLastSet','PasswordExpired','PasswordNeverExpires','PasswordNotRequired','whenCreated','whenChanged']):
        #default limit on queries
        self.DEFAULT_LIMIT = 20
        self.ntds_file=ntds_file
        self.cracked_file=cracked_file
        self.grouplists=grouplists
        self.groupdirectory=groupdirectory
        self.pullFromAD=pullFromAD
        self.baseDN=baseDN
        self.machineaccts=machineaccts
        self.filename_for_html_report=filename_for_html_report
        self.folder_for_html_report=folder_for_html_report
        self.writedb=writedb
        self.sanitize=sanitize_results
        self.filename_for_db_on_disk=filename_for_db_on_disk
        if None != self.filename_for_db_on_disk:
            self.db_path = os.path.join(self.folder_for_html_report,self.filename_for_db_on_disk)
        self.ad_properties=ad_properties
        self.columns = ["Username", "Password", "Password Length", "NT Hash", "Only LM Cracked"]
        # This is used to bypass updating AD properties in get_all_hashes() call
        # Assuming fresh pull, want this to run only the first time the method is called
        # Helpful to disable from the get-go if just working with the DB
        self.ad_properties_not_updated = True
        self.extended_file_properties = False
        # used for aged password metrics
        self.windows_epoch = "1970-01-01T04:56:00+00:00"

        # This should be False as it is only a shortcut used during development
        self.speed_it_up = False
        # NOTE: this is an array of tuples (groupname, groupname) for compatibility
        self.compare_groups = []

        if self.sanitize:
            self.folder_for_html_report = self.folder_for_html_report + "_Sanitized"

        # initialize groups
        self.init_groups()
        
        # create report folder if it doesn't already exist
        if not os.path.exists(self.folder_for_html_report):
            os.makedirs(self.folder_for_html_report)

        # initialize db cursor
        self.init_db()

    def init_groups(self):
        if self.groupdirectory is not None:
            # gather everything in the directory
            if self.grouplists is None:
                self.grouplists = [os.path.join(self.groupdirectory,f) for f in os.listdir(self.groupdirectory) if os.path.isfile(os.path.join(self.groupdirectory,f))]
        # gather just the specified files
            else:
                self.grouplists = [os.path.join(self.groupdirectory,f) for f in self.grouplists if os.path.isfile(os.path.join(self.groupdirectory,f))]
        if self.grouplists is not None:
            if self.pullFromAD:
                for group in self.grouplists:
                    self.compare_groups.append((group,group))
            else:
                for groupfile in self.grouplists:
                    self.compare_groups.append((os.path.splitext(os.path.basename(groupfile))[0], groupfile))

    def init_db(self):
        self.conn = sqlite3.connect(':memory:')
        if self.writedb:
            if os.path.exists(self.db_path):
                os.remove(self.db_path)
            self.conn = sqlite3.connect(self.db_path)
        if self.speed_it_up:
            self.conn = sqlite3.connect(self.db_path)
        self.conn.text_factory = str
        self.cursor = self.conn.cursor()
    
    def connect_to_db(self,filename_for_db_on_disk=None):
        if None == filename_for_db_on_disk:
            filename_for_db_on_disk = self.db_path
        self.conn = sqlite3.connect(filename_for_db_on_disk)
        self.cursor = self.conn.cursor()

    def close_db(self):
        self.conn.commit()
        self.conn.close()

    def get_groups_users(self):
        groups_users = {}
        if self.pullFromAD:
            # pull non-foreign SID group members recursively
            groups_users = get_group_members(base_dn=self.baseDN,group_cns=[g for _, g in self.compare_groups])
        else:
            for group in self.compare_groups:
                user_domain = ""
                user_name = ""
                try:
                    users = []
                    fing = io.open(group[1], encoding='utf-16')
                    for line in fing:
                        if "MemberDomain" in line:
                            user_domain = (line.split(":")[1]).strip()
                        if "MemberName" in line:
                            user_name = (line.split(":")[1]).strip()
                            users.append((user_domain + "\\" + user_name))
                except:
                    print("Doesn't look like the Group Files are in the form output by PowerView, assuming the files are already in domain\\username"\
                        " list form unless they are csv/json format")
                    # If the users array is empty, assume the file was not in the PowerView PowerShell script output format that you get from running:
                    # Get-NetGroupMember -GroupName "Enterprise Admins" -Domain "some.domain.com" -DomainController "DC01.some.domain.com" > Enterprise Admins.txt
                    # You can list domain controllers for use in the above command with Get-NetForestDomain
                    if len(users) == 0:
                        ext = group[1].split(".") [-1]
                        users = []
                        if ext == 'csv':
                            with open(group[1],"r",encoding="UTF-8") as fing:
                                csvreader = csv.DictReader(fing)
                                for row in csvreader:
                                    users.append(row)
                            self.extended_file_properties = True
                            self.ad_properties = [header for _,header in enumerate(users[0])]
                        elif ext == 'json':
                            with open(group[1],"r",encoding="UTF-8") as fing:
                                users = json.loads(fing.read())
                            self.extended_file_properties = True
                            self.ad_properties = [header for _,header in enumerate(users[0])]
                        else:
                            with open(group[1],"r",encoding="UTF-8") as fing:
                                for line in fing:
                                    users.append(line.rstrip("\r\n"))
                groups_users[group[0]] = users
        return groups_users

    def setup_db(self):
        # Create tables and indices
        # moving here so ad_properties can be updated first if needed
        self.cursor.execute('''CREATE TABLE hash_infos
            (username_full text collate nocase, username text collate nocase, lm_hash text, lm_hash_left text, lm_hash_right text, nt_hash text, password text, 
            lm_pass_left text, lm_pass_right text, only_lm_cracked boolean, history_index int, history_base_username text, {0})'''.format(" text, ".join(self.ad_properties) + " text"))
        self.cursor.execute("CREATE INDEX index_nt_hash ON hash_infos (nt_hash);")
        self.cursor.execute("CREATE INDEX index_lm_hash_left ON hash_infos (lm_hash_left);")
        self.cursor.execute("CREATE INDEX index_lm_hash_right ON hash_infos (lm_hash_right);")
        self.cursor.execute("CREATE INDEX lm_hash ON hash_infos (lm_hash);")
        self.cursor.execute("CREATE INDEX username ON hash_infos (username);")

        # Create boolean column for each group
        for group in self.compare_groups:
            sql = "ALTER TABLE hash_infos ADD COLUMN \"" + group[0] + "\" boolean"
            self.cursor.execute(sql)

    def process_ndts_file(self):
        with open(self.ntds_file) as fin:
            for line in fin:
                vals = line.rstrip("\r\n").split(':')
                if len(vals) == 1:
                    continue
                usernameFull = vals[0]
                lm_hash = vals[2]
                lm_hash_left = lm_hash[0:16]
                lm_hash_right = lm_hash[16:32]
                nt_hash = vals[3]
                username = usernameFull.split('\\')[-1]
                history_base_username = usernameFull
                history_index = -1
                username_info = r"(?i)(.*\\*.*)_history([0-9]+)$"
                results = re.search(username_info,usernameFull)
                if results:
                    history_base_username = results.group(1)
                    history_index = results.group(2)
                # Exclude machine accounts (where account name ends in $) by default
                if self.machineaccts or not username.endswith("$"):
                    self.insert_machine_account(usernameFull, username, lm_hash, lm_hash_left, lm_hash_right, nt_hash, history_index, history_base_username)
                    
    #TODO: may need to update this with AD properties
    def insert_machine_account(self,usernameFull, username, lm_hash, lm_hash_left, lm_hash_right, nt_hash, history_index, history_base_username):
        self.cursor.execute("INSERT INTO hash_infos (username_full, username, lm_hash , lm_hash_left , lm_hash_right , nt_hash, history_index, history_base_username) "\
            "VALUES (?,?,?,?,?,?,?,?)",(usernameFull, username, lm_hash, lm_hash_left, lm_hash_right, nt_hash, history_index, history_base_username))

    def update_group_memberships(self,groups_users):
        for group in groups_users:
                for user in groups_users[group]:
                    if self.extended_file_properties:
                        if 'UserPrincipalName' in user:
                            upn = user['UserPrincipalName'].split("@")
                            sql = "UPDATE hash_infos SET " 
                            for prop in self.ad_properties:
                                sql += ("\"" + prop + "\" = \"" + str(user[prop]) + "\",")
                            
                            sql = sql + "\"" + group + "\" = 1 WHERE username_full = \"" + (upn[1] + "\\" + upn[0]) + "\""
                        else:
                            raise KeyError("User didn't contain UPN. Required to process from csv/json file. {0}".format(user))
                    else:
                        sql = "UPDATE hash_infos SET \"" + group + \
                            "\" = 1 WHERE username_full = \"" + user + "\""
                    self.cursor.execute(sql)

    def update_nt_password(self,password,hash):
        self.cursor.execute("UPDATE hash_infos SET password = ? WHERE nt_hash = ?", (password, hash))

    def update_lm_left_password(self,password,hash):
        self.cursor.execute("UPDATE hash_infos SET lm_pass_left = ? WHERE lm_hash_right = ?", (password, hash))

    def update_lm_right_password(self,password,hash):
        self.cursor.execute("UPDATE hash_infos SET lm_pass_right = ? WHERE lm_hash_right = ?", (password, hash))

    def update_lm_password(self,password,hash):
        self.update_lm_right_password(password,hash)
        self.update_lm_left_password(password,hash)

    def process_pot_file(self):
        with open(self.cracked_file) as fin:
            for lineT in fin:
                line = lineT.rstrip('\r\n')
                colon_index = line.find(":")
                hash = line[0:colon_index]
                # Stripping $NT$ and $LM$ that is included in John the Ripper output by default
                jtr = False
                if hash.startswith('$NT$') or hash.startswith('$LM$'):
                    hash = hash.lstrip("$NT$")
                    hash = hash.lstrip("$LM$")
                    jtr = True
                password = line[colon_index+1:len(line)]
                lenxx = len(hash)
                if re.match(r"\$HEX\[([^\]]+)", password) and not jtr:
                    hex2 = (binascii.unhexlify(re.findall(r"\$HEX\[([^\]]+)", password)[-1]))
                    l = list()
                    for x in list(hex2):
                        if type(x) == int:
                            x = str(chr(x))
                        l.append(x)
                    password = ""
                    password = password.join(l)
                if lenxx == 32:  # An NT hash
                    self.update_nt_password(password, hash)
                elif lenxx == 16:  # An LM hash, either left or right
                    self.update_lm_password(password, hash)

    def get_lm_results(self):
        self.cursor.execute('SELECT nt_hash,lm_pass_left,lm_pass_right FROM hash_infos WHERE (lm_pass_left is not NULL or lm_pass_right is not NULL) '\
            'and password is NULL and lm_hash is not "aad3b435b51404eeaad3b435b51404ee" group by nt_hash')
        return self.cursor.fetchall()

    def set_lm_cracked(self,password,hash):
        self.cursor.execute('UPDATE hash_infos SET only_lm_cracked = 1, password = \'' + password + '\' WHERE nt_hash = \'' + hash + '\'')

    def crack_lm_hashes(self):
        result_list = self.get_lm_results()
        count = len(result_list)
        if count != 0:
            print("Cracking %d NT Hashes where only LM Hash was cracked (aka lm2ntcrack functionality)" % count)
        for pair in result_list:
            lm_pwd = ""
            if pair[1] is not None:
                lm_pwd += pair[1]
            if pair[2] is not None:
                lm_pwd += pair[2]
            password = crack_it(pair[0], lm_pwd)
            if password is not None:
                self.set_lm_cracked(password.replace("'", "''"),pair[0])
            count -= 1

    def setup(self):
        #get group members
        groups_users = self.get_groups_users()        

        #setup column headers, types, and indexes
        self.setup_db()

        # Read in NTDS file, needs to be done pre-update_group_memberships b/c it will insert machine accounts that are otherwise ignored
        self.process_ndts_file()

        # update group membership flags, if file was json/csv may need to udpate entries
        self.update_group_memberships(groups_users)

    def ingest_files(self):
        # DB and group membership setup
        self.setup()

        # read in POT file
        self.process_pot_file()

        # Do additional LM cracking
        self.crack_lm_hashes()
    
    def update_user_ad_properties(self,username,fullname):
        update_data = ()
        # query prep
        query_prep = ['{0}=?'.format(self.ad_properties[i]) for i in range(0,len(self.ad_properties))]
        set_string = ",".join(query_prep)
        user_properties = get_aduser_properties(username=username,username_full=fullname,properties=self.ad_properties,base_dn=self.baseDN)
        if user_properties is not None:
            update_query = "UPDATE hash_infos SET {0} WHERE username_full = \"{1}\"".format(set_string,user_properties['username_full'])
            for p in [(user_properties[k],) for k in self.ad_properties]:
                update_data += p
            # update the entry
            self.cursor.execute(update_query, update_data)

    def update_ad_properties(self):
        # get users
        self.cursor.execute("SELECT username, username_full from hash_infos")
        all_usernames = [u for u in self.cursor.fetchall()]
        counter = 0
        #for upn in upns:
        for user in all_usernames:
            counter += 1
            if not (counter % 500):
                print("Pulling AD attributes...{0}/{1} accounts completed".format(counter,len(all_usernames)))
            try:
                self.update_user_ad_properties(user[0],user[1])
            except:
                continue
        self.columns.extend(self.ad_properties)

    def get_all_hashes_from_db(self,enabled_only=False):
        if self.pullFromAD or self.extended_file_properties:
            all_hashes_query = 'SELECT username_full,password,LENGTH(password) as plen,nt_hash,only_lm_cracked,'+ ",".join(self.ad_properties) + ' \
                FROM hash_infos WHERE history_index = -1'
        else:
            all_hashes_query = 'SELECT username_full,password,LENGTH(password) as plen,nt_hash,only_lm_cracked \
                FROM hash_infos WHERE history_index = -1' 
        if enabled_only:
            all_hashes_query += " AND Enabled = 'True'"
        all_hashes_query += ' ORDER BY plen DESC, password'
        self.cursor.execute(all_hashes_query)
        return self.cursor.fetchall()

    def get_all_hashes(self,enabled_only=False):
        if (self.pullFromAD or self.extended_file_properties):
            if self.pullFromAD and self.ad_properties_not_updated:
                self.update_ad_properties()
                self.ad_properties_not_updated = False
            else:
                # if update_ad_properties() is not called, columns aren't extended with additional properties
                for p in self.ad_properties:
                    if p not in self.columns:
                        self.columns.append(p)
        return self.get_all_hashes_from_db(enabled_only=enabled_only)

    def get_num_unique_hashes(self,enabled_only=False):
        query = 'SELECT count(DISTINCT nt_hash) FROM hash_infos WHERE history_index = -1'
        if enabled_only:
            query += " AND Enabled = 'True'"
        self.cursor.execute(query)
        return self.cursor.fetchone()[0]

    def get_num_cracked_users(self,enabled_only=False):
        query = 'SELECT count(*) FROM hash_infos WHERE password is not NULL AND history_index = -1'
        if enabled_only:
            query += " And Enabled = 'True'"
        self.cursor.execute(query)
        return self.cursor.fetchone()[0]

    def get_num_unique_passwords(self,enabled_only=False):
        query = 'SELECT count(Distinct password) FROM hash_infos where password is not NULL AND history_index = -1 '
        if enabled_only:
            query += "AND Enabled = 'True'"
        self.cursor.execute(query)
        return self.cursor.fetchone()[0]

    def get_group_members_from_db(self,group):
        self.cursor.execute("SELECT username_full,nt_hash FROM hash_infos WHERE \"" + group + "\" = 1 AND history_index = -1")
        return self.cursor.fetchall()

    def get_users_sharing_hash(self,hash):
        # this handles the 'Users Sharing this Hash' column
        self.cursor.execute("SELECT username_full FROM hash_infos WHERE nt_hash = \"" + hash + "\" AND history_index = -1")
        return self.cursor.fetchall()
    
    def get_password_by_hash(self,hash):
        self.cursor.execute("SELECT password,lm_hash FROM hash_infos WHERE nt_hash = \"" + hash + "\" AND history_index = -1 LIMIT 1")
        return self.cursor.fetchone()

    def get_user_ad_properties(self,username_full):
        self.cursor.execute("SELECT "+ ",".join(self.ad_properties) +" FROM hash_infos WHERE username_full = \"" + username_full + "\" AND history_index = -1 LIMIT 1")
        return self.cursor.fetchone()

    def get_cracked_group_users(self,group):
        if self.pullFromAD or self.extended_file_properties:
            group_cracked_query = "SELECT username_full, LENGTH(password) as plen, password, only_lm_cracked, "+ ", ".join(self.ad_properties) +\
                " FROM hash_infos WHERE \"" + group + "\" = 1 and password is not NULL and password is not '' ORDER BY plen"
        else:
            group_cracked_query = "SELECT username_full, LENGTH(password) as plen, password, only_lm_cracked FROM hash_infos WHERE \"" +\
                group + "\" = 1 and password is not NULL and password is not '' ORDER BY plen"
        self.cursor.execute(group_cracked_query)
        return self.cursor.fetchall()
    
    def get_lm_hash_count(self,unique=False,enabled_only=False):
        query = 'SELECT count({0}) FROM hash_infos WHERE lm_hash is not "aad3b435b51404eeaad3b435b51404ee" AND history_index = -1'.format('DISTINCT lm_hash' if unique else '*')
        if enabled_only:
            query += " AND Enabled = 'True'"
        self.cursor.execute(query)
        return self.cursor.fetchone()[0]

    def get_user_passwords_cracked_bc_lm(self,enabled_only=False):
        query = 'SELECT username_full,password,LENGTH(password) as plen,only_lm_cracked FROM hash_infos WHERE only_lm_cracked = 1 ORDER BY plen AND history_index = -1'
        if enabled_only:
            query += " AND Enabled = 'True'"
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_num_nt_hashes_lm_only_cracked(self):
        self.cursor.execute('SELECT COUNT(DISTINCT nt_hash) FROM hash_infos WHERE only_lm_cracked = 1 AND history_index = -1')
        return self.cursor.fetchone()[0]

    def get_password_length_stats(self,enabled_only=False):
        query = 'SELECT LENGTH(password) as plen,COUNT(password) FROM hash_infos WHERE plen is not NULL AND history_index = -1 AND plen is not 0'
        if enabled_only:
            query += " AND Enabled = 'True'"
        query += ' GROUP BY plen ORDER BY plen'
        self.cursor.execute(query)
        return self.cursor.fetchall()
        
    def get_cracked_lm_and_not_nt(self,enabled_only=False):
        query = 'SELECT lm_hash, lm_pass_left, lm_pass_right, nt_hash FROM hash_infos WHERE (lm_pass_left is not "" or lm_pass_right is not "")' \
            ' AND history_index = -1 and password is NULL and lm_hash is not "aad3b435b51404eeaad3b435b51404ee"'
        if enabled_only:
            query += " AND Enabled = 'True'"
        query += ' group by lm_hash'
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def get_usernames_by_pwd_len(self,length,enabled_only=False):
        query = 'SELECT username FROM hash_infos WHERE history_index = -1 AND LENGTH(password) = {0}'.format(length)
        if enabled_only:
            query += " AND Enabled = 'True'"
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def get_num_passwords_by_len(self,enabled_only=False):
        query = 'SELECT COUNT(password) as count, LENGTH(password) as plen FROM hash_infos WHERE plen is not NULL AND '\
            'history_index = -1 and plen is not 0'
        if enabled_only:
            query += " AND Enabled = 'True'"
        query += ' GROUP BY plen ORDER BY count DESC'
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def get_top_used_passwords(self,count=20,enabled_only=False):
        query = 'SELECT password,COUNT(password) as count FROM hash_infos WHERE password is not NULL AND history_index = -1 and '\
            'password is not "" {0} GROUP BY password ORDER BY count DESC LIMIT {1}'.format("AND Enabled = 'True'" if enabled_only else "",count)
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_top_nt_hashes(self,count=20,enabled_only=False):
        self.cursor.execute('SELECT nt_hash, COUNT(nt_hash) as count, password FROM hash_infos WHERE nt_hash is not "31d6cfe0d16ae931b73c59d7e0c089c0" AND '\
            'history_index = -1  {0} GROUP BY nt_hash ORDER BY count DESC LIMIT {1}'.format("AND Enabled = 'True'" if enabled_only else "",count))
        return self.cursor.fetchall()

    def get_usernames_by_nt_hash(self,hash):
        self.cursor.execute('SELECT username FROM hash_infos WHERE nt_hash = \"' + hash + '\" AND history_index = -1')
        return self.cursor.fetchall()

    def get_max_password_history(self):
        self.cursor.execute('SELECT MAX(history_index) FROM hash_infos;')
        return self.cursor.fetchone()[0]

    def get_password_history_stats(self,max_password_history=None):
        password_history_headers = ["Username", "Current Password"]
        if None == max_password_history:
            max_password_history = self.get_max_password_history()
        column_names = ["cp"]
        command = 'SELECT * FROM ( '
        command += 'SELECT history_base_username'
        for i in range(-1,max_password_history + 1):
            if i == -1:
                column_names.append("cp")
            else:
                password_history_headers.append("History " + str(i))
                column_names.append("h" + str(i))
            command += (', MIN(CASE WHEN history_index = ' + str(i) + ' THEN password END) ' + column_names[-1])
        command += (' FROM hash_infos GROUP BY history_base_username) ')
        command += "WHERE coalesce(" + ",".join(column_names) + ") is not NULL"
        self.cursor.execute(command)
        return password_history_headers, self.cursor.fetchall()

    def create_all_hashes_report(self,filename="all hashes.html",summary_label="Password Hashes",enabled_only=False):
        result_list = self.get_all_hashes(enabled_only=enabled_only)
        num_hashes = len(result_list)
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)

        # all users, cracked or not
        hbt.add_table_to_html(result_list, self.columns)
        filename = hbt.write_html_report(filename)
        return (num_hashes, summary_label,"<a href=\"" + filename + "\">Details</a>"), filename

    def create_group_members_report(self,group):
        result_list = self.get_group_members_from_db(group)
        num_groupmembers = len(result_list)
        headers = ["Username", "NT Hash", "Users Sharing this Hash",
                "Share Count", "Password", "Non-Blank LM Hash?"]
        new_list = []
        for tuple in result_list:  # the tuple is (username_full, nt_hash, lm_hash)
            # this handles the 'Users Sharing this Hash' column
            users_list = self.get_users_sharing_hash(tuple[1])
            if len(users_list) < 30:
                string_of_users = (', '.join(''.join(elems) for elems in users_list))
                new_tuple = tuple + (string_of_users,)
            else:
                new_tuple = tuple + ("Too Many to List",)
            # this handles the 'Share Count' column
            new_tuple += (len(users_list),)
            # adds the password to the tuple
            result = self.get_password_by_hash(tuple[1])
            new_tuple += (result[0],)
            # Is the LM Hash stored for this user?
            if result[1] != "aad3b435b51404eeaad3b435b51404ee":
                new_tuple += ("Yes",)
            else:
                new_tuple += ("No",)
            headers = ["Username", "NT Hash", "Users Sharing this Hash",
                "Share Count", "Password", "Non-Blank LM Hash?"]
            if self.pullFromAD or self.extended_file_properties:
                result = self.get_user_ad_properties(tuple[0])
                new_tuple += result
                headers.extend(self.ad_properties)
            new_list.append(new_tuple)
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
        hbt.add_table_to_html(new_list, headers)
        filename = hbt.write_html_report(group + " members.html")
        return (num_groupmembers, "Members of \"%s\" group" % group, "<a href=\"" + filename + "\">Details</a>"), filename

    def create_group_members_cracked_report(self,group):
        headers = ["Username of \"" + group + "\" Member",
                    "Password Length", "Password", "Only LM Cracked"]
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
        if self.pullFromAD or self.extended_file_properties:
            headers.extend(self.ad_properties)
        group_cracked_list = self.get_cracked_group_users(group)
        if not isinstance(group_cracked_list,list):
            group_cracked_list = [group_cracked_list]
        num_groupmembers_cracked = len(group_cracked_list)
        hbt.add_table_to_html(group_cracked_list, headers)
        filename = hbt.write_html_report(group + " cracked passwords.html")
        return (num_groupmembers_cracked, "\"%s\" Passwords Cracked" % group, "<a href=\"" + filename + "\">Details</a>"), filename

    def create_lm_noncracked_report(self,filename="lm_noncracked.html",enabled_only=False):
        result_list = self.get_cracked_lm_and_not_nt(enabled_only=enabled_only)
        num_lm_hashes_cracked_where_nt_hash_not_cracked = len(result_list)
        output = "WARNING there were %d unique LM hashes for which you do not have the password." % num_lm_hashes_cracked_where_nt_hash_not_cracked
        output2 = None
        if num_lm_hashes_cracked_where_nt_hash_not_cracked != 0:
            hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
            headers = ["LM Hash", "Left Portion of Password",
                    "Right Portion of Password", "NT Hash"]
            hbt.add_table_to_html(result_list, headers)
            lm_noncracked_filename = hbt.write_html_report(filename)
            output + ' <a href="' + lm_noncracked_filename + '">Details</a>'
            output2 = "</br> Cracking these to their 7-character upcased representation is easy with Hashcat and this tool will determine the "\
                "correct case and concatenate the two halves of the password for you!</br></br> Try this Hashcat command to crack all LM hashes:"\
                "</br> <strong>./hashcat64.bin -m 3000 -a 3 customer.ntds -1 ?a ?1?1?1?1?1?1?1 --increment</strong></br></br> Or for John, try this:"\
                "</br> <strong>john --format=LM customer.ntds</strong></br>"
            return output, output2
        else:
            return False

    def create_only_cracked_thru_lm_report(self,filename="users_only_cracked_through_lm.html",summary_label="Passwords Only Cracked via LM Hash",enabled_only=False):
        result_list = self.get_user_passwords_cracked_bc_lm(enabled_only=enabled_only)
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
        headers = ["Username", "Password", "Password Length", "Only LM Cracked"]
        hbt.add_table_to_html(result_list, headers)
        only_cracked_thru_lm_filename = hbt.write_html_report(filename)
        return (len(result_list), summary_label,"<a href=\"" + only_cracked_thru_lm_filename + "\">Details</a>"), only_cracked_thru_lm_filename

    def create_password_length_report(self,filename="length_usernames.html",summary_label="Password Length Stats",enabled_only=False):
        result_list = self.get_password_length_stats(enabled_only=enabled_only)
        counter = 0
        for tuple in result_list:
            length = str(tuple[0])
            usernames = self.get_usernames_by_pwd_len(length)
            hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
            headers = ["Users with a password length of " + length]
            hbt.add_table_to_html(usernames, headers)
            pwd_len_users_filename = hbt.write_html_report(str(counter) + filename)
            result_list[counter] += ("<a href=\"" + pwd_len_users_filename + "\">Details</a>",)
            counter += 1
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
        headers = ["Password Length", "Count", "Details"]
        hbt.add_table_to_html(result_list, headers, 2)
        result_list = self.get_num_passwords_by_len(enabled_only=enabled_only)
        headers = ["Count", "Password Length"]
        hbt.add_table_to_html(result_list, headers)
        pwd_len_stats_filename = hbt.write_html_report(filename)
        return (None, summary_label, "<a href=\"" + pwd_len_stats_filename + "\">Details</a>"), pwd_len_stats_filename

    def create_top_passwords_report(self,filename="top_password_stats.html",summary_label="Top Password Use Stats",enabled_only=False):
        result_list = self.get_top_used_passwords(count=self.DEFAULT_LIMIT,enabled_only=enabled_only)
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
        headers = ["Password", "Count"]
        hbt.add_table_to_html(result_list, headers)
        top_pwd_stats_filename = hbt.write_html_report(filename)
        return (None, summary_label,"<a href=\"" + top_pwd_stats_filename + "\">Details</a>"), top_pwd_stats_filename

    def create_pwd_reuse_names_report(self,filename="reuse_usernames.html",enabled_only=False):
        result_list = self.get_top_nt_hashes(count=self.DEFAULT_LIMIT,enabled_only=enabled_only)
        counter = 0
        for tuple in result_list:
            usernames = self.get_usernames_by_nt_hash(tuple[0])
            password = tuple[2]
            if password is None:
                password = ""
            hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
            headers = ["Users Sharing a hash:password of " +
                    sanitize(tuple[0],self.sanitize) + ":" + sanitize(password,self.sanitize)]
            hbt.add_table_to_html(usernames, headers)
            reuse_names_filename = hbt.write_html_report(str(counter) + filename)
            result_list[counter] += ("<a href=\"" + reuse_names_filename + "\">Details</a>",)
            counter += 1
        return result_list, reuse_names_filename

    def create_pwd_reuse_stats_report(self,reuse_names,filename="password_reuse_stats.html",summary_label="Password Reuse Stats"):
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
        headers = ["NT Hash", "Count", "Password", "Details"]
        hbt.add_table_to_html(reuse_names, headers, 3)
        password_reuse_stats_filename = hbt.write_html_report(filename)
        return (None, summary_label,"<a href=\"" + password_reuse_stats_filename + "\">Details</a>"), password_reuse_stats_filename

    def create_pwd_history_report(self,filename="password_history.html",summary_label="Password History"):
        max_password_history = self.get_max_password_history()
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)
        if max_password_history < 0:
            hbt.build_html_body_string("There was no history contained in the password files.  If you would like to get the password history, "\
                "run secretsdump.py with the flag \"-history\". <br><br> Sample secretsdump.py command: secretsdump.py -system registry/SYSTEM "\
                "-ntds \"Active Directory/ntds.dit\" LOCAL -outputfile customer -history")
        else:
            password_history_headers, result_list = self.get_password_history_stats()
            headers = password_history_headers
            hbt.add_table_to_html(result_list, headers, 8)
        filename=hbt.write_html_report(filename)
        return (None, summary_label,"<a href=\"" + filename + "\">Details</a>"), filename

    def get_old_password_users(self,years_old,enabled_only=False):
        query = "SELECT username_full,password,LENGTH(password) as plen,nt_hash,only_lm_cracked,{0}"\
            " FROM hash_infos WHERE date(PwdLastSet) < date('now','-{1} years') AND PwdLastSet != \"{2}\"".format(\
            ",".join(self.ad_properties),years_old,self.windows_epoch)
        if enabled_only:
            query += " And Enabled = 'True'"
        query += ' ORDER BY date(PwdLastSet) DESC, plen, password'
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def get_unset_password_users(self,enabled_only=False):
        query = "SELECT username_full,password,LENGTH(password) as plen,nt_hash,only_lm_cracked,{0}"\
            " FROM hash_infos WHERE PwdLastSet = \"{1}\"".format(\
            ",".join(self.ad_properties),self.windows_epoch)
        if enabled_only:
            query += " And Enabled = 'True'"
        query += ' ORDER BY plen DESC, password'
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def create_old_password_report(self,filename,summary_label,years_old,enabled_only=False):
        old_password_users = self.get_old_password_users(years_old=years_old,enabled_only=enabled_only)
        num_users = len(old_password_users)
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)

        hbt.add_table_to_html(old_password_users, self.columns)
        filename = hbt.write_html_report(filename)
        return (num_users, summary_label,"<a href=\"" + filename + "\">Details</a>"), filename

    def create_unset_password_report(self,filename="Unchanged Password Users.html",summary_label="Users With Unchanged Password",enabled_only=False):
        unset_users = self.get_unset_password_users(enabled_only=enabled_only)
        num_users = len(unset_users)
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)

        hbt.add_table_to_html(unset_users, self.columns)
        filename = hbt.write_html_report(filename)
        return (num_users, summary_label,"<a href=\"" + filename + "\">Details</a>"), filename

    def get_password_never_expires_users(self,enabled_only=False):
        query = "SELECT username_full,password,LENGTH(password) as plen,nt_hash,only_lm_cracked,{0}"\
            " FROM hash_infos WHERE PasswordNeverExpires = 'True'".format(",".join(self.ad_properties))
        if enabled_only:
            query += " And Enabled = 'True'"
        query += ' ORDER BY plen DESC, password'
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def create_password_never_expires_report(self,filename="pwd_never_expires.html",summary_label="Users With No Password Expiration",enabled_only=False):
        pwd_never_exp_users = self.get_password_never_expires_users(enabled_only=enabled_only)
        num_users = len(pwd_never_exp_users)
        hbt = HtmlBuilder(self.folder_for_html_report,self.sanitize)

        hbt.add_table_to_html(pwd_never_exp_users, self.columns)
        filename = hbt.write_html_report(filename)
        return (num_users, summary_label,"<a href=\"" + filename + "\">Details</a>"), filename

    def create_audit_report(self):
        hb = HtmlBuilder(self.folder_for_html_report,self.sanitize)
        summary_table = []
        summary_table_headers = ("Count", "Description", "More Info")

        # Total number of hashes in the NTDS file
        all_hashes_summary, all_hashes_filename = self.create_all_hashes_report()
        summary_table.append(all_hashes_summary)        

        if self.pullFromAD and "Enabled" in self.ad_properties: 
            all_enabled_hashes_summary, all_enabled_hashes_filename = self.create_all_hashes_report(filename="all enabled hashes.html",\
                summary_label="Enabled Password Hashes",enabled_only=True)
            summary_table.append(all_enabled_hashes_summary)

        #build the summary html

        # Total number of UNIQUE hashes in the NTDS file
        num_unique_nt_hashes = self.get_num_unique_hashes()
        summary_table.append((num_unique_nt_hashes, "Unique Password Hashes", None))

        if self.pullFromAD and "Enabled" in self.ad_properties:
            num_enabled_unique_nt_hashes = self.get_num_unique_hashes(enabled_only=True)
            summary_table.append((num_enabled_unique_nt_hashes, "Enabled Unique Password Hashes", None))

        # Number of users whose passwords were cracked
        num_passwords_cracked = self.get_num_cracked_users()
        summary_table.append((num_passwords_cracked, "Passwords Discovered Through Cracking", None))

        if self.pullFromAD and "Enabled" in self.ad_properties:
            num_enabled_passwords_cracked = self.get_num_cracked_users(enabled_only=True)
            summary_table.append((num_enabled_passwords_cracked, "Enabled Passwords Discovered Through Cracking", None))

        # Number of UNIQUE passwords that were cracked
        num_unique_passwords_cracked = self.get_num_unique_passwords()
        summary_table.append((num_unique_passwords_cracked,"Unique Passwords Discovered Through Cracking", None))

        if self.pullFromAD and "Enabled" in self.ad_properties:
            num_unique_enabled_passwords_cracked = self.get_num_unique_passwords(enabled_only=True)
            summary_table.append((num_unique_enabled_passwords_cracked,"Enabled Unique Passwords Discovered Through Cracking", None))

        # Percentage of current passwords cracked and percentage of unique passwords cracked
        num_hashes = len(self.get_all_hashes_from_db())

        percent_cracked_unique = num_unique_passwords_cracked / \
            float(num_unique_nt_hashes)*100
        percent_all_cracked = num_passwords_cracked/float(num_hashes)*100
        summary_table.append(("%0.1f" % percent_all_cracked,
                            "Percent of Current Passwords Cracked", "<a href=\"" + all_hashes_filename + "\">Details</a>"))
        summary_table.append(("%0.1f" % percent_cracked_unique,
                            "Percent of Unique Passwords Cracked", "<a href=\"" + all_hashes_filename + "\">Details</a>"))

        if self.pullFromAD and "Enabled" in self.ad_properties:
            # Percentage of current passwords cracked and percentage of unique passwords cracked
            # think this is better as a metric of the total number of accounts, not just out of the enabled accounts
            # num_enabled_hashes = len(self.get_all_hashes_from_db(enabled_only=True))
            #percent_enabled_cracked_unique = num_unique_enabled_passwords_cracked / \
            #    float(num_enabled_unique_nt_hashes)*100
            #percent_all_enabled_cracked = num_enabled_passwords_cracked/float(num_enabled_hashes)*100
            percent_enabled_cracked_unique = num_unique_enabled_passwords_cracked / \
                float(num_unique_nt_hashes)*100
            percent_all_enabled_cracked = num_enabled_passwords_cracked/float(num_hashes)*100
            summary_table.append(("%0.1f" % percent_all_enabled_cracked,
                                "Enabled Percent of Current Passwords Cracked", "<a href=\"" + all_enabled_hashes_filename + "\">Details</a>"))
            summary_table.append(("%0.1f" % percent_enabled_cracked_unique,
                                "Enabled Percent of Unique Passwords Cracked", "<a href=\"" + all_enabled_hashes_filename + "\">Details</a>"))

        # Don't think it's worth including the enabled metrics for groups...
        # Group Membership Details and number of passwords cracked for each group
        for group in self.compare_groups:
            # this list contains the username_full and nt_hash of all users in this group
            group_member_summary, _ = self.create_group_members_report(group[0])
            summary_table.append(group_member_summary)
            group_member_cracked_summary, _ = self.create_group_members_cracked_report(group[0])
            summary_table.append(group_member_cracked_summary)

        # Number of LM hashes in the NTDS file, excluding the blank value
        summary_table.append((self.get_lm_hash_count(), "LM Hashes (Non-blank)", None))

        if self.pullFromAD and "Enabled" in self.ad_properties:
            summary_table.append((self.get_lm_hash_count(enabled_only=True), "Enabled LM Hashes (Non-blank)", None))

        # Number of UNIQUE LM hashes in the NTDS, excluding the blank value
        summary_table.append((self.get_lm_hash_count(unique=True), "Unique LM Hashes (Non-blank)", None))

        if self.pullFromAD and "Enabled" in self.ad_properties:
            summary_table.append((self.get_lm_hash_count(unique=True,enabled_only=True), "Enabled Unique LM Hashes (Non-blank)", None))

        # Number of passwords that are LM cracked for which you don't have the exact (case sensitive) password.
        lm_noncracked = self.create_lm_noncracked_report()
        if lm_noncracked:
            hb.build_html_body_string(lm_noncracked[0])
            hb.build_html_body_string(lm_noncracked[1])

        if self.pullFromAD and "Enabled" in self.ad_properties:
            lm_noncracked = self.create_lm_noncracked_report(filename="lm_noncracked_enabled.html",enabled_only=True)
            if lm_noncracked:
                hb.build_html_body_string(lm_noncracked[0])
                hb.build_html_body_string(lm_noncracked[1])

        # Count and List of passwords that were only able to be cracked because the LM hash was available, includes usernames
        only_cracked_thru_lm_summary, _ = self.create_only_cracked_thru_lm_report()
        summary_table.append(only_cracked_thru_lm_summary)
        summary_table.append((self.get_num_nt_hashes_lm_only_cracked(), "Unique LM Hashes Cracked Where NT Hash was Not Cracked", None))

        if self.pullFromAD and "Enabled" in self.ad_properties:
            enabled_only_cracked_thru_lm_summary, _ = self.create_only_cracked_thru_lm_report(filename="users_only_cracked_through_lm_enabled.html",\
                summary_label="Enabled Passwords Only Cracked via LM Hash",enabled_only=True)
            summary_table.append(enabled_only_cracked_thru_lm_summary)
            summary_table.append((self.get_num_nt_hashes_lm_only_cracked(), "Enabled Unique LM Hashes Cracked Where NT Hash was Not Cracked", None))

        # Password age statistics
        if self.pullFromAD and "PwdLastSet" in self.ad_properties:
            five_years_old_summary, _ = self.create_old_password_report(filename="5+ Year Old Passwords.html",summary_label="5+ Year Old Passwords",years_old=5)
            summary_table.append(five_years_old_summary)

            ten_years_old_summary, _ = self.create_old_password_report(filename="10+ Year Old Passwords.html",summary_label="10+ Year Old Passwords",years_old=10)
            summary_table.append(ten_years_old_summary)

            fifteen_years_old_summary, _ = self.create_old_password_report(filename="15+ Year Old Passwords.html",summary_label="15+ Year Old Passwords",years_old=15)
            summary_table.append(fifteen_years_old_summary)

            unchanged_user_summary, _ = self.create_unset_password_report()
            summary_table.append(unchanged_user_summary)

            if "Enabled" in self.ad_properties:
                five_years_old_enabled_summary, _ = self.create_old_password_report(filename="Enabled 5+ Year Old Passwords.html",\
                    summary_label="Enabled 5+ Year Old Passwords",years_old=5,enabled_only=True)
                summary_table.append(five_years_old_enabled_summary)

                ten_years_old_enabled_summary, _ = self.create_old_password_report(filename="Enabled 10+ Year Old Passwords.html",\
                    summary_label="Enabled 10+ Year Old Passwords",years_old=10,enabled_only=True)
                summary_table.append(ten_years_old_enabled_summary)

                fifteen_years_old_enabled_summary, _ = self.create_old_password_report(filename="Enabled 15+ Year Old Passwords.html",\
                    summary_label="Enabled 15+ Year Old Passwords",years_old=15,enabled_only=True)
                summary_table.append(fifteen_years_old_enabled_summary)

                unchanged_user_summary, _ = self.create_unset_password_report(filename="Unchanged Password Users_enabled.html",\
                    summary_label="Enabled Users With Unchanged Password",enabled_only=True)
                summary_table.append(unchanged_user_summary)

        # Unexpiring password stats
        if self.pullFromAD and "PasswordNeverExpires" in self.ad_properties:
            pwd_never_expires_summary, _ = self.create_password_never_expires_report()
            summary_table.append(pwd_never_expires_summary)

            if "Enabled" in self.ad_properties:
                pwd_never_expires_summary, _ = self.create_password_never_expires_report(filename="pwd_never_expires_enabled.html",\
                    summary_label="Enabled Users With No Password Expiration",enabled_only=True)
                summary_table.append(pwd_never_expires_summary)

        # Password length statistics
        pwd_len_stats_summary, _ = self.create_password_length_report()
        summary_table.append(pwd_len_stats_summary)

        if self.pullFromAD and "Enabled" in self.ad_properties:
            pwd_len_stats_summary, _ = self.create_password_length_report(filename="length_usernames_enabled.html",summary_label="Enabled Password Length Stats",enabled_only=True)
            summary_table.append(pwd_len_stats_summary)

        # Top N Passwords Used; defined by dpat.DEFAULT_LIMIT; default = 20
        top_passwords_summary, _ = self.create_top_passwords_report()
        summary_table.append(top_passwords_summary)

        if self.pullFromAD and "Enabled" in self.ad_properties:
            top_passwords_summary, _ = self.create_top_passwords_report(filename="top_password_stats_enabled.html",summary_label="Enabled Top Password Use Stats",enabled_only=True)
            summary_table.append(top_passwords_summary)

        # Password Reuse Statistics (based only on NT hash)
        reuse_names, _ = self.create_pwd_reuse_names_report()
        pwd_reuse_stats_summary, _ = self.create_pwd_reuse_stats_report(reuse_names=reuse_names)
        summary_table.append(pwd_reuse_stats_summary)

        if self.pullFromAD and "Enabled" in self.ad_properties:
            enabled_reuse_names, _ = self.create_pwd_reuse_names_report(filename="reuse_usernames_enabled.html",enabled_only=True)
            enabled_pwd_reuse_stats_summary, _ = self.create_pwd_reuse_stats_report(reuse_names=enabled_reuse_names,filename="password_reuse_stats_enabled.html",\
                summary_label="Enabled Password Reuse Stats")
            summary_table.append(enabled_pwd_reuse_stats_summary)

        # skipping enabled history report for the time being
        # Password History Stats
        pwd_history_summary, _ = self.create_pwd_history_report()
        summary_table.append(pwd_history_summary)

        # Write out the main report page
        hb.add_table_to_html(summary_table, summary_table_headers, 2)
        hb.write_html_report(self.filename_for_html_report)
        print("The Report has been written to the \"" + self.filename_for_html_report +
            "\" file in the \"" + self.folder_for_html_report + "\" directory")

def generate_dpat_report(ntds_file,cracked_file,grouplists=None,groupdirectory=None,pullFromAD=False,baseDN=None,machineaccts=False,\
filename_for_html_report="_DomainPasswordAuditReport.html",folder_for_html_report="DPAT Report",writedb=False,sanitize_results=False,\
filename_for_db_on_disk="pass_audit.db",ad_properties=['Enabled','PwdLastSet','PasswordExpired','PasswordNeverExpires','PasswordNotRequired','whenCreated','whenChanged']):
    dpat = DPAT(ntds_file=ntds_file,cracked_file=cracked_file,grouplists=grouplists,groupdirectory=groupdirectory,pullFromAD=pullFromAD,baseDN=baseDN,machineaccts=machineaccts,\
        filename_for_html_report=filename_for_html_report,folder_for_html_report=folder_for_html_report,writedb=writedb,sanitize_results=sanitize_results,\
        filename_for_db_on_disk=filename_for_db_on_disk,ad_properties=ad_properties)

    if not dpat.speed_it_up:
        dpat.ingest_files()

    dpat.create_audit_report()

    # Save (commit) the changes and close the database connection
    dpat.close_db()

def main():
    parser = argparse.ArgumentParser(
        add_help=False,
        description='''
        This script will perform a domain password audit based on an extracted NTDS file and password cracking output such as Hashcat.
        ''',
        formatter_class=lambda prog: argparse.HelpFormatter(prog,max_help_position=40)
    )

    filename_for_db_on_disk = "pass_audit.db"
    filename_for_html_report = "_DomainPasswordAuditReport.html"
    folder_for_html_report = "DPAT Report"

    parser.add_argument('-n', '--ntdsfile',
                        help='NTDS file name (output from SecretsDump.py)', required=True)
    parser.add_argument('-c', '--crackfile',
                        help='Password Cracking output in the default form output by Hashcat, such as hashcat.potfile', required=True)
    parser.add_argument('-o', '--outputfile', help='The name of the HTML report output file, defaults to ' +
                        filename_for_html_report, required=False, default=filename_for_html_report)
    parser.add_argument('-d', '--reportdirectory', help='Folder containing the output HTML files, defaults to ' +
                        folder_for_html_report, required=False, default=folder_for_html_report)
    parser.add_argument('-w', '--writedb', help='Write the SQLite database info to disk for offline inspection instead of just in memory. Filename will be "' +
                        filename_for_db_on_disk + '"', default=False, required=False, action='store_true')
    parser.add_argument('-s', '--sanitize', help='Sanitize the report by partially redacting passwords and hashes. Prepends the report directory with \"Sanitized - \"',
                        default=False, required=False, action='store_true')
    parser.add_argument('-g', '--grouplists', help='The name of one or multiple files that contain lists of usernames in particular groups. \
                        The group names will be taken from the file name itself. The username list must be in the same format as found in the NTDS file such as some.ad.domain.com\\username \
                        or it can be in the format output by using the PowerView Get-NetGroupMember function. Example: -g "Domain Admins.txt" "Enterprise Admins.txt"', nargs='*', 
                        required=False)
    parser.add_argument('-gd', '--groupdirectory', help='The path to the directory containing grouplist files', required=False)
    parser.add_argument('-ad', '--pullFromAD', default=False, required=False, action='store_true', help='Pull the groups specified by grouplists directly from AD w/extended properties.'\
        ' Requires baseDN param.'),
    parser.add_argument('-dn', '--baseDN', default=None, required=False, help='The base DN to search for groups/users if pullFromAD is "true"'),
    parser.add_argument('-m', '--machineaccts', help='Include machine accounts when calculating statistics',
                        default=False, required=False, action='store_true')
    
    
    if len(sys.argv) < 2 or sys.argv[1] in ('-h','--help'):
        parser.print_help()
        sys.exit(0)

    args = parser.parse_args()
    generate_dpat_report(ntds_file=args.ntdsfile,cracked_file=args.crackfile,filename_for_html_report=args.outputfile,\
        folder_for_html_report=args.reportdirectory,writedb=args.writedb,sanitize_results=args.sanitize,grouplists=args.grouplists,\
        groupdirectory=args.groupdirectory,pullFromAD=args.pullFromAD,baseDN=args.baseDN,machineaccts=args.machineaccts)
    
    # prompt user to open the report
    # the code to prompt user to open the file was borrowed from the EyeWitness tool https://github.com/ChrisTruncer/EyeWitness
    print('Would you like to open the report now? [Y/n]')
    while True:
        try:
            response = input().lower().rstrip('\r\n')
            if ((response == "") or (strtobool(response))):
                webbrowser.open(os.path.join("file://" + os.getcwd(),
                                            args.reportdirectory, args.outputfile))
                break
            else:
                break
        except ValueError:
            print("Please respond with y or n")

    sys.exit(0)


if __name__ == "__main__":
    main()