How can i export Azure policy controls output

Question

I am trying to export the output of azure policy controls output into an excel file so that they can be tracked in the form of a report that i can present, if i copy and paste from the portal the formatting is all over the place.

I was wondering if there is a script or an automated way to get the output of azure policy onto a spreadsheet or into json format.

Thanks in advance.

Answer 1

Hi @MrFlinstone ,

The InvalidOrderByColumnInQueryString error mentioned here is because of providing an invalid column name called 'table'. I was able to reproduce the error by running below command.

Get-AzPolicyState -SubscriptionId 'xxxxxxxxxxxxxxxxxxxxxxxx' -Filter " ComplianceState eq 'NonCompliant' and ResourceLocation ne 'eastus' and PolicyDefinitionReferenceId eq 'firewallshouldbeenabledonkeyvaultmonitoringeffect'" -OrderBy "table asc"

To resolve the error, provide a valid column name like Timestamp, PolicyAssignmentName, etc. For more information, refer this Azure document.

On the other hand, if you are looking for a way to export the output to csv, use Export-Csv cmdlet as shown below.

Get-AzPolicyState -SubscriptionId 'xxxxxxxxxxxxxxxxxxxxxxxx' -Filter "ComplianceState eq 'NonCompliant'" | Select-Object PolicyDefinitionReferenceId, PolicyAssignmentName, PolicyDefinitionName, PolicySetDefinitionName, ResourceId, ResourceGroup, SubscriptionId | Export-Csv .\noncompliant_resources_output.csv

Let me know if you have any further questions with regards to this topic.