Azure Policy Compliance Report Into CSV format

Question

Dear Team,

I want to prepare a report for resource which are non compliant as per the policies or initiatives assigned against scope i want to extract the policy compliance report into CSV and prepare the report by mentioning the non compliance reason for policy or initiative so how to get the policy compliance report into CSV it should include the policy name and policies under initiatives and the resource which are non compliant with respective policies or initiatives assigned against the subscription or management group or tenant root group

Answer 1

Hi Naresh,

Let me share you a graph query to get the resources that are not compliant and also you can export to csv.

policyresources
| where ['kind'] == 'policystates' 
| extend compliant = properties.complianceState
| extend timestamp = properties.timestamp
| extend resourceType = properties.resourceType
| extend resourceId = properties.resourceId
| extend policyDefinitionId = properties.policyDefinitionId
| project name, policyDefinitionId, compliant, timestamp, resourceType, resourceId
| where compliant == 'NonCompliant'

To use to go to Azure portal > Azure Graph Explorer and run the query

If the information helped address your question, please Accept the answer.

Luis

Answer 2

Hi @Naresh kumar Petalo

To get a policy compliance report in CSV format, you can use Azure PowerShell or Azure CLI.

Here are the steps to get the report using Azure PowerShell:

1. Open Azure PowerShell and connect to your Azure account using the Connect-AzAccount cmdlet.
2. Set the scope of the report using the Set-AzContext cmdlet. For example, to set the scope to a subscription, use the following command: Set-AzContext -SubscriptionId
3. Run the following command to get the policy compliance report in CSV format: powershell Get-AzPolicyState | Export-Csv -Path -NoTypeInformation This command will export the policy compliance report to a CSV file at the specified path. The report will include the policy name, policy definition ID, policy assignment ID, resource ID, resource type, and compliance state.

To include the non-compliance reason for each policy, you can modify the command as follows: powershell Get-AzPolicyState | Select-Object PolicyDefinitionName, PolicySetDefinitionName, PolicyDefinitionId, PolicySetDefinitionId, PolicyAssignmentId, ResourceId, ResourceType, ComplianceState, @{Name='NonComplianceReason';Expression={$_.NonComplianceMessage}} | Export-Csv -Path -NoTypeInformation

This command will include the non-compliance reason for each policy in the report. I hope this helps! Let me know if you have any further questions.