Share via

How can i export Azure policy controls output

MrFlinstone 501 Reputation points
2023-11-02T16:21:12.98+00:00

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.

Azure Policy
Azure Policy
An Azure service that is used to implement corporate governance and standards at scale for Azure resources.
815 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. tbgangav-MSFT 10,406 Reputation points
    2023-11-16T04:03:37.5133333+00:00

    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"

    User's image

    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

    User's image

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