If you are moving to Microsoft Azure you immediately have to deal with governance and compliance topics. In Microsoft Azure there is the Cloud Adaption Framework (CAF) which guides you through the journey of moving to Azure. One very important part is cloud governance with all its flavors. One single piece of this governance cake are Azure Policy for sure. If you don’t know the Azure Policy service in short it is…
Azure Policy is a service in Azure that you use to create, assign, and manage policies. These policies enforce different rules and effects over your resources, so those resources stay compliant with your corporate standards and service level agreements. Azure Policy meets this need by evaluating your resources for non-compliance with assigned policies. For example, you can have a policy to allow only a certain SKU size of virtual machines in your environment. Once this policy is implemented, new and existing resources are evaluated for compliance. With the right type of policy, existing resources can be brought into compliance.
If you have been working in IT for some time you might know Group Policy Objects (GPOs) in Active Directory and in a way you could think of Azure Policy in a similar way. Although, both are entire different technologies, but in a sense we can adapt certain “thoughts” and implementation rules. Like the GP Objects in Active Directory, in Azure we are dealing with Azure Policy definitions. These are JSON files, which contain some meta data like name, description etc. and a rule part. The rule defines, what the Azure Policy definition is actually doing. For example auditing, if a virtual machine has a BitLocker encrypted drive or not. If not, the policy will pop up in the Azure portal as not compliant. As you can imagine there is whole set of things we would want to check if they are compliant or not. Maybe, we even want to disallow certain things, like too beefy virtual machines SKU or let the user deploy their resources only in West Europe. All those things we are able to control with the Azure Policy service. Luckily, Microsoft is so kind and provides already ~230 built-in policy definition, which can be used immediately or you could use as a guideline for your own custom policies. Because, the set of Azure Policy definitions is growing very quickly and they undergo also a lifecycle controlled by Microsoft, it can be a challenge to have an overview of the policies. Imagine, there is a growing set of policies and you need to know what policy you need to implement or not. Maybe you even need to check with your internal security and compliance office which policy you need to implement in your infrastructure. Wouldn’t it be nice to have some sort of database where the data get’s stored and could be extended the way you would need it? Because of this situation, I just played around and created some export logic using Azure Logic Apps and Azure SQL DB. Well this example is not perfect in anyway, but I think it gives you a good starting point.
SQL Database
In the Azure portal I created an Azure SQL database and connected with the SQL Server management studio. There are two tables…
If you want to create the Policies table, execute the following code in the SQL Server Management Studio…
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Policies](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PolicyName] [varchar](50) NOT NULL,
[PolicyDisplayName] [varchar](256) NULL,
[PolicyDescription] NULL,
[PolicyID] [varchar](256) NULL,
[PolicyCategory] [varchar](256) NULL,
[PolicyEffect] [varchar](256) NULL,
[PolicyMode] [varchar](256) NULL,
[PolicyPolicyType] [varchar](256) NULL,
[PolicyType] [varchar](256) NULL,
[PolicyInfoID] [int] NULL,
[PolicyImplement] [bit] NULL,
[PolicyNotes] NULL,
[PolicyDateInserted] [datetime] NULL,
[PolicyDateUpated] [datetime] NULL,
CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Policies] WITH CHECK ADD CONSTRAINT [FK_Policies_PolicyInfo] FOREIGN KEY([PolicyInfoID])
REFERENCES [dbo].[PolicyInfo] ([ID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_PolicyInfo]
GO
Create PolicyInfo table…
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PolicyInfo](
[ID] [int] NOT NULL,
[RiskMitigation] NULL,
[RiskDescription] NULL,
CONSTRAINT [PK_PolicyInfo] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Next we need to create a stored procedure, which then we are able to call from the Logic Apps to insert the new policies or update existing policies. This behavior is also called “upsert” a combination of update and insert process.
The UpsertPolicy stored procedure looks like this…
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpsertPolicy]
@PolicyName VARCHAR(50),
@PolicyDisplayName VARCHAR(256) NULL,
@PolicyDescription TEXT NULL,
@PolicyID VARCHAR(256),
@PolicyCategory VARCHAR(256) ,
@PolicyEffect VARCHAR(256) ,
@PolicyMode VARCHAR(256) ,
@PolicyPolicyType VARCHAR(256) ,
@PolicyType VARCHAR(256) ,
@PolicyNotes TEXT
AS
BEGIN
IF EXISTS (SELECT * FROM Policies WHERE PolicyName = @PolicyName)
BEGIN
DECLARE @PolicyDateUpdated DateTime
Set @PolicyDateUpdated = CURRENT_TIMESTAMP
UPDATE Policies SET PolicyDisplayName = @PolicyDisplayName,
PolicyDescription = @PolicyDescription,
PolicyID = @PolicyID,
PolicyCategory = @PolicyCategory,
PolicyEffect = @PolicyEffect,
PolicyMode = @PolicyMode,
PolicyPolicyType = @PolicyPolicyType,
PolicyType = @PolicyType,
PolicyDateUpated = @PolicyDateUpdated
WHERE PolicyName = @PolicyName
END
ELSE
BEGIN
Declare @PolicyDateInserted DateTime
Set @PolicyDateInserted = CURRENT_TIMESTAMP
Declare @PolicyImplement BIT
Set @PolicyImplement = 'false'
Declare @PolicyInfoID INT
Set @PolicyInfoID = 1
INSERT INTO Policies VALUES (@PolicyName, @PolicyDisplayName,@PolicyDescription,@PolicyID,@PolicyCategory,@PolicyEffect,@PolicyMode,@PolicyPolicyType,@PolicyType, @PolicyInfoID,@PolicyImplement,@PolicyNotes,@PolicyDateInserted,@PolicyDateUpdated)
END
END
GO
If you execute these scripts in your SQL database you should have built the foundation for our Policy database. Of course, change the code to whatever needs you have, I am just providing a sample solution.
Logic App
Building the logic app is also not that hard. from a high-level view it looks like this…
…the Recurrence triggers on a set schedule. Then it will call the Azure management endpoint to call the API to list all built-in policies. Next we need to parse / format the result and finally loop through the result and call a stored procedure to either insert or update the policy in the SQL DB. That’s it 
. Well yes it may sound easy, but it takes some time to have it running smoothly. Here I will deliver the details of each step. The Recurrence step is the easy one…
…so I will trigger the workflow every three hours. Next I call the API, but bevor we can do that make sure you enable the logic app to use Managed Service Identity (MSI)…
…as soon we enabled the logic app to use an MSI, we will be able to set the permission for the logic app on the subscription (just for testing) level. I gave it Contributor permission, in a real world scenario we just assign the minimal permission – of course. In my demo scenario it looks like this… 
…after that we can set the next HTTP GET request as shown in this screenshot. The API is documented here…
…the Parse JSON action is easy, if you now the schema of the result returned. I had to play with it until I didn’t receive any errors. The action looks like this…
…and the schema I used, looks like this…
{
"properties": {
"value": {
"items": {
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"properties": {
"properties": {
"description": {
"type": "string"
},
"displayName": {
"type": "string"
},
"metadata": {
"properties": {
"category": {
"type": "string"
}
},
"type": "object"
},
"mode": {
"type": "string"
},
"parameters": {
"properties": {},
"type": "object"
},
"policyRule": {
"properties": {
"if": {
"properties": {
"field": {
"type": "string"
},
"in": {
"items": {
"type": [
"string",
"array"
]
},
"type": [
"array",
"string"
]
}
},
"type": "object"
},
"then": {
"properties": {
"details": {
"properties": {
"existenceCondition": {
"properties": {
"field": {
"type": "string"
},
"like": {
"type": "string"
}
},
"type": "object"
},
"type": {
"type": "string"
}
},
"type": [
"object",
"array"
]
},
"effect": {
"type": "string"
}
},
"type": "object"
}
},
"type": "object"
},
"policyType": {
"type": "string"
}
},
"type": "object"
},
"type": {
"type": "string"
}
},
"required": [
"properties",
"id",
"type",
"name"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
…and finally the For each step looks like this, which calls the Execute stored procedure action…
…if the schema works and you set the connection correctly, you should be able to drag and drop the items into the logic app action. Now after the first successful run, we will end up with a table like this…
…this means we have a simple data model to store our policy data and classification. From here we can use Excel or Power Apps to use this data to build either an Excel workbook or an app 
. Too me it sounds very cool – have fun!
