Get Azure Policy Compliance Report in Excel file using Python
- Arlan Nugara
- Nov 17, 2024
- 3 min read
Getting Started
To work on this project, you should have the git client and an editor (VS Code is free and capable)
If this is your first time using git, you will need to tell it your name and email address. This can be done using the following two commands (making the obvious changes)
git config --global user.name "Your Name"git config --global user.email you@example.comIntroduction
This project mainly focused on generating Azure Policy Compliance Report in Excel file stored in Azure Storage Account.
Why choose Python over Powershell
Faster and efficient
A full fledged language
One of the most used programming language
Lesser amount of code
Dynamic when working with excels or Data frames
Human friendly language to work with
Azure Governance Management Reporting
This process creates an excel file which contains information about Policy Compliance for a given Management Group scope. The excel file is then pushed to a storage container in Azure using the same Azure DevOps Pipeline which generates the excel file. The scope can be changed to Subscriptions if needed with little modification.
The process in summary
This is the general idea how the process works within the Pipeline and Python. Start the pipeline selecting the Management group name which triggers the python scripts to do following:-
Login to Azure using the Service Principle credentials to generate the API tokens.
Query Azure API to get information about Policy and Compliance
Format and inserts the data into Excel Sheet.
Upload the excel file to Azure Storage Account Container.
Prerequisites
The bootstrap Resources include Key Vault and Storage Account. The Key Vault holds sensitive values which are required by Python to authenticate with Azure for Rest API Query. The Storage Account holds the Excel file which is generated from Python.
Azure Storage Account
Please create an Azure Storage Account and a container named policyreports to store generated Excel Files. Please note the Service Principle should have access to the Storage Account. Note the Access Key and Storage Account name from Azure Portal.
Key Name | Value |
ARM-ACCESS-KEY | Azure Storage Account Access Key |
SA-NAME | Azure Storage Account Name |
Service Connection
Azure DevOps Pipeline requires Service Connection to run tasks. The Service Principle should have access to Key Vault Secrets (Get and List Permission) to retrieve Key Vault Secret Values required during running the task. Please refer to this official article for creating the Service Connection from a Service Principle. Note the following values for a Service Principle from Azure Portal.
Key Name | Value |
ARM-CLIENT-ID | Application ID of the Service Principle |
ARM-CLIENT-SECRET | Client Secret of the Service Principle |
ARM-TENANT-ID | Azure Tenant ID |
Key Vault
An Azure Key Vault is required to store Secrets which are used by the pipeline to authenticate against Azure and Azure DevOps to perform its desired operation. Please note the Service Principle mentioned above must have GET and LIST for the Key Vault Secrets. Please create the secrets in Azure Key Vault. You may refer to the Service Connection section for values.
Secrets to be created in Azure Key Vault:
ARM-CLIENT-ID
ARM-CLIENT-SECRET
ARM-TENANT-ID
ARM-ACCESS-KEY
SA-NAME
Variable Groups
The code needs an Azure DevOps Pipeline Variable Group linked to an existing Azure Key Vault containing the Secrets. Please refer to this official article for more details.
Excel File
Azure Storage Account Container is used to safe keep Excel files which are generated during the process. The reports are generated in a sub directory inside the Container with naming conventions as Azure_Policy_Compliance-YYYY-MM-DD.
Pipeline
Creating the Pipeline
Please follow this instruction to create the deploy pipeline
Go to Pipelines in Azure DevOps
Click on New Pipeline from right top corner
Select Azure Repos Git
Select your repository containing this code
Select Existing Azure Pipelines YAML file
Select the branch and select path as /.pipelines/generate_report.yaml
Click on Continue
Click on Save from Run drop down menu on top right corner
You may rename the pipeline by choosing Rename/move from top right corner Kebab menu
Running the Pipeline
Please follow the instruction to run the pipelines
Go to Pipelines in Azure DevOps and select the pipeline
Click on Run Pipeline from top right corner
Select Management Group ID and click on Run button
Follow the Pipeline Status
Recommendation
We will recommend to schedule the Pipeline to run everyday at specific time for all Management Group instead of running it manually. This will ensure continuous availability of data without missing. We can do that.