In this article, I will be sharing a script that will enable you query the BitLocker recovery key from the MBAM reporting services. I have written a guide on how to query MBAM to display the report for BitLocker Recovery for a specified period of time. In this way, you will be able to save the report as a CSV file that we will later query in order to determine the number of times, a devices has been recovered via the MBAM help-desk or Self-service recovery.
If you would like to set up an Microsoft BitLocker Administration and Monitoring, please see this this link.
Upon downloading the BitLocker Recovery CSV file, kindly save it in your desired location. You can also configure the reporting servces to automatically save this report for you. Here is a guide on how to go about this “MBAM reports automatic E-mail notification: How to create MBAM Enterprise and Compliance, and Recovery Audit reports”. The process is similarand you just have to change the destination (delivery method).
When this is done, you have to create a script that will query the CSV file in order to get your desired results. Below is the python script that can be tweaked to your need.
import csv
import datetime
from collections import defaultdict
# Get the current date and time
current_date = datetime.datetime.now()
# Calculate the date six months ago
six_months_ago = current_date - datetime.timedelta(weeks=27)
# Open the CSV file
with open("C:\\Users\\xxx\\Documents\\RecoveryAudit\Recovery Audit Report.csv", 'r') as file:
reader = csv.reader(file)
# Skip the header row
next(reader)
# Keep track of success events by computer name
success_by_computer = defaultdict(int)
total_by_computer = defaultdict(int)
for row in reader:
event_date = datetime.datetime.strptime(row[0], '%m/%d/%Y %H:%M:%S %p')
if event_date >= six_months_ago:
computer_name = row[5]
total_by_computer[computer_name] += 1
if row[2] == "Successful":
success_by_computer[computer_name] += 1
# Calculate the rate of success for each computer
rates = {}
for computer_name, success_count in success_by_computer.items():
total_count = total_by_computer[computer_name]
rates[computer_name] = success_count / total_count
# Print the number of computers that have requested BitLocker recovery keys more than once in the past six months
count = 0
print("Computers that have requested BitLocker recovery keys more than once in the last six months:")
for computer_name, rate in rates.items():
if rate > 0:
count += 1
print("{}: {} times".format(computer_name, int(total_by_computer[computer_name])))
print("Number of computers: ", count)This will display the result in the terminal. But if you wish to send the report to a shared folder, you will have to customise this script. You can also include this in a scheduled task. I have an article I am currently compiling, and when this is done. I will add the link to this post.
