Extract a report of all non-compliant azure resources using azure policies

MrFlinstone 466 Reputation points

What am I trying to do ?

when I look at Azure policies, I am trying to extract a report showing policy compliance.

For every policy, I am looking to extract information on the total number of non-compliant resources. There will be a one line entry for every non-compliant resource.

Extract the policy name i.e Key vaults should have deletion protection enabled, policy description, resource name for example key vault name, the resource group of the non-compliant resource, the subscription housing the resource.

What have I done ?

Get-AzPolicyState -SubscriptionId 'xxx' -Filter " ComplianceState eq 'NonCompliant' and ResourceLocation ne 'eastus' and PolicyDefinitionReferenceId eq 'firewallshouldbeenabledonkeyvaultmonitoringeffect'"

I played with an example below to filter it down using PolicyDefinitionReferenceId

The way the results was displayed wouldn't work for my report as It will need to be presented using excel or csv, when I tried to pipe the output to a table I get the error below. The azure subscription has a lot of policy items, so ideally I am trying to write the script such that it will export to csv or excel.

Get-AzPolicyState : Operation returned an invalid status code 'BadRequest' (InvalidOrderByColumnInQueryString: The orderby column 'table' is not a valid column name.)

Azure Policy
Azure Policy
An Azure service that is used to implement corporate governance and standards at scale for Azure resources.
714 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. tbgangav-MSFT 9,431 Reputation points

    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.

    0 comments No comments