Using Python with SQL Server 2017 SSRS should not be an obstacle, since the framework and the technology are the same as with the R language. With SQL Server 2017, Python got full and functional support for native SSRS. But in this guide, we will not be querying the SSRS with Python. Rather, we will create a subscription as discussed below to generate the MBAM Recovery Audit CSV files. This is easier. Why introduces much complexity? The goal of this task is actually similar to what we have here “MBAM reports automatic E-mail notification: How to create MBAM Enterprise and Compliance, and Recovery Audit reports“. Therefore, in this article, I will show you how to visualize MBAM Recovery Audit Report with Python. Please see how to run Python Automation in Windows with Visual Studio Code
What is a Report Builder?
In subsequent guides, I will be showing you how to create reports via the report builder. Then this brings us to the question. What is a Report Builder? A Report Builder provides data visualizations that include charts, maps, sparklines, and data bars that can help produce new insights well beyond what can be achieved with standard tables and charts.
You can use Report Builder to create reports and shared datasets. Publish report parts, and then browse the Report Part Gallery to reuse existing report parts as building blocks for creating new reports quickly with a “grab and go” experience. This download provides a stand-alone installer for Report Builder.
Please here are some exciting guides: Ansible: No Python interpreters found for the host, how to fix you are not allowed to view this folder on SSRS: MBAM reports cannot be accessed because it could not load the folder content, and
What are the differences between SSRS and Report Builder?
SQL Server Reporting Services (SSRS) provides a set of on-premises tools and services that enables you to create, deploy, and manage mobile and paginated reports. That is, it allows you to produce formatted reports with tables in the form of data, graphs, images, and charts.
You could say, it is a server-based platform for hosting reports. While Report Builder is a client tool that may be used to create reports to deploy to SSRS. As such, you would use SSRS to manage security for the reports that you may have deployed with Report Builder. You can create reports also in the following ways in SQL Server Reporting Services:
- By using BIDS (Business Intelligence Development Studio), a subset of Microsoft’s Visual Studio development environment; or
- By using Report Builder, a standalone application downloadable from the Internet.
Furthermore, with the Reporting Services subscription, you can deliver reports at a specific time or in response to an event in a file format that you wish (specify). You can use either SQL Server Management Studio or the web portal to manage Reporting Services reports.
Therefore, we will be creating a subscription in order to generate the MBAM Recovery Audit CSV files.Visualize MBAM Recovery Audit Report from SQL Server Reporting Services with Python
Step 1: Create a Network Share to save the CSV file
A CSV (Comma Separated Values) file is a form of plain text document which uses a specific format to organize tabular information. CSV file format is a bounded text document that uses a comma to distinguish the values. Every row in the document is a data log. Each log is composed of one or more fields, divided by commas. It is the most popular file format for importing and exporting spreadsheets and databases.
First, you will have to create a new share where the MBAM Recovery Audit CSV files will be saved unto. To do this, we have to first create a new folder on our server. See the image below for how to do this.
Now that the share has been created, we will insert the UNC, path into the subscription window. Simply copy the UNC path as discussed below.
Step 2: Create a subscription
Proceed to this guide for steps to create a new SSRS subscription. In the example below, I will be configuring first for the “Recovery Audit Report”. Therefore, I will be clicking on the three horizontal dots (…)/ellipsis.
And then click on Subscribe. You may also want to learn how to query MBAM to display the report for BitLocker Recovery for a specified period of time.
This will open the subscription window. Click on Subscriptions. Next, click on new subscriptions. Enter the following information the way you want it. Please see how to run Python Script via Windows Task Scheduler.
Enter more details on the subscription wizard
More information is below and click on Create a subscription. You can choose any of the overwrite options. Since I will be using a specific name, I will be chosen the first or last option. This is specifically up to you.
Kindly take a look at these information guides: How to delete and create MySQL Databases, Unable to access the image: Make sure that the image path and the Windows directory for the image exist and that you have Read permissions on the folder, Error unregistering runner from GitLab forbidden with docker executor: Fatal failed to unregister runner, how to Install Kubectl on Windows 11, and Microsoft recommends a fix for WDAC vulnerabilities by updating PowerShell.
Step 3: Create a Python Script to visualize MBAM Recovery Audit Report with Python
See this article for a similar script: Run Python Script via Windows Task Scheduler. I have created lots of scripts which are available on my GitHub account. In this guide, I have got a script for a similar task.
I will be adding a few more scripts in here just incase you wish to reproduce these tasks or reuse them in your environments. Scrip 1: Successful BitLocker Key Recovery Events in the Last Three Months into PDF output
PDF is an abbreviation that stands for Portable Document Format. It’s a versatile file format created by Adobe that gives people an easy, reliable way to present and exchange documents – regardless of the software, hardware, or operating systems being used by anyone who views the document. The PDF format is now an open standard, maintained by the International Organization for Standardization (ISO). PDF docs can contain links and buttons, form fields, audio, video, and business logic. They can be signed electronically and can easily be viewed on Windows or MacOS using the free Adobe Acrobat Reader software.
There are various ways to read a CSV file that uses either the CSV module or Panda’s library. CSV Module: The CSV module is one of the modules in Python which provides classes for reading and writing tabular information in CSV file format. In this article, we will be using the CSV Library.
This script will query the CSV file and get the number of successful recoveries in the last three months. It will also generate the output in the PDF.
import csv
import datetime
import matplotlib.pyplot as plt
from collections import defaultdict
# Get the current date and time
current_date = datetime.datetime.now()
# Calculate the date three months ago
three_months_ago = current_date - datetime.timedelta(weeks=13)
# Open the CSV file
with open("C:\\Users\\mac\\Desktop\\RecoveryAudit\Recovery Audit Report.csv", 'r') as file:
reader = csv.reader(file)
# Skip the header row
next(reader)
# Keep track of success events by month and year
success_by_month_year = defaultdict(int)
for row in reader:
if row[2] == "Successful":
event_date = datetime.datetime.strptime(row[0], '%m/%d/%Y %H:%M:%S %p')
if event_date >= three_months_ago:
success_by_month_year[(event_date.month, event_date.year)] += 1
# Get the month names
month_names = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
# Plot a bar graph of the number of BitLocker recovery events in the last six months
x = [month_names[month-1] for (month, year) in success_by_month_year.keys()]
y = [success_count for (month, year), success_count in success_by_month_year.items()]
plt.bar(x, y, align='center', alpha=0.5)
plt.xlabel("Month")
plt.xticks(rotation=90)
plt.ylabel("Number of BitLocker Key Recovery Events")
plt.title("BitLocker Key Recovery Events (Successful) in the Last Three Months")
# Save the results to a text file
filename = "\\tester\admin\results\BitLocker_Key_Recovery_Events_Three_Months.txt"
with open(filename, 'w') as file:
for (month, year), success_count in success_by_month_year.items():
file.write("{} {}: {}\n".format(month_names[month-1], year, success_count))
# Save the results to a pdf file
filename = "\\tester\admin\results\BitLocker_Key_Recovery_Events_Three_Months.pdf"
plt.savefig(filename)
# Output the results in code
print("Successful BitLocker Key Recovery Events in the Last Three Months:")
print(success_by_month_year)
Script 2: For a script to determine the number of devices recovered in the last two weeks
This script will output the rate Rate of BitLocker key recovery success by computer in the last two weeks to a text file (txt)
import csv
import datetime
from collections import defaultdict
# Get the current date and time
current_date = datetime.datetime.now()
# Calculate the date two weeks ago
two_weeks_ago = current_date - datetime.timedelta(weeks=2)
# Open the CSV file
with open("C:\\Users\\mac\\Desktop\\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 >= two_weeks_ago:
computer_name = row[4]
total_by_computer[computer_name] += 1
if row[2] == "Successful":
success_by_computer[computer_name] += 1
# Calculate the rate of success for each computerv
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
# Save the output to a text file
with open("\\tester\\admin\\results\\result.txt", 'w') as file:
file.write("Rate of BitLocker key recovery success by computer in the last two weeks:\n")
for computer_name, rate in rates.items():
file.write("{}: {:.2f}%\n".format(computer_name, rate * 100))Step 4: Create a Scheduled task
If you wish to automate the running of these scripts in order to generate new reports whenever a new CSV file is pulled, you will need to create a Scheduled task.
To create a Scheduled Task. Launch Windows Task Scheduler. Click on Create Task as shown below.
Note: But if you would like to create a dedicated folder to house your my scripts, you are also welcome to do so.Follow these guides for more on these steps. Please see this article on how to run Python Script via Windows Task Scheduler, Failed to create a new scheduled task name RemoteException: Cannot create the file when that file already exists, how to Create an Advanced Scheduled Task in Windows, how to create an Advanced Scheduled Task in Windows, and how to Export and Import Scheduled Tasks in Windows.
You could also create a scheduled task with Windows Admin Center, and how to Run, Edit, and Delete Tasks via Task Scheduler in Windows. Also, see How to monitor Windows server backup via PowerShell script.
I hope you found this blog post helpful on how to visualize MBAM Recovery Audit Report with Python. If you have any questions, please let me know in the comment section.