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

MrFlinstone 581 Reputation points
2023-11-03T00:13:55.37+00:00

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.
911 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. tbgangav-MSFT 10,416 Reputation points
    2023-11-16T04:02:56.28+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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.