Question 286378 in Azure Question posted in
The official documentation to get you started can be found here.

Extracting azure policy report information using Get-AzPolicyState

I have come across the post here and I have found it extremely useful as a first start.

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 wouldnt 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.)

Tags:

2

Answers


  1. 0

    The way the results was displayed wouldnt 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.

    Here is the updated script to fetch the compliance state in Azure Policy and send the same result to Excel.

    $subscriptionId = 'xxxxxxxxxxxxxxxxxx'
$nonCompliantPolicies = Get-AzPolicyState -SubscriptionId $subscriptionId -Filter " ComplianceState eq 'NonCompliant' and ResourceLocation ne 'eastus' and PolicyDefinitionReferenceId eq 'storageaccountshoulduseaprivatelinkconnectionmonitoringeffect'"

# Define a custom object for each non-compliant resource
$customObjects = $nonCompliantPolicies | ForEach-Object {
    [PSCustomObject]@{
        PolicyName = $_.PolicyDefinitionName
        ComplianceState = $_.ComplianceState
        ResourceType = $_.ResourceType
        ResourceGroup = $_.ResourceGroup
        SubscriptionId = $_. subscriptionId
        IsCompliant    = $_.IsCompliant 
        PolicyAssignmentScope =$_.PolicyAssignmentScope
    }
}

# Export the custom objects to a CSV file
$customObjects | Export-Csv -Path 'Azure_policy_compliance_report.csv' -NoTypeInformation

    Result:

    enter image description here

    I have filtered the non-compliant policy states based on criteria. Then, for each non-compliant resources, created a custom object with the desired information, including Policy name, ComplianceState, ResourceType, ResourceGroup, Subscription ID, and Is Compliant, and I export these custom objects to a CSV file using Export-Csv.

    Excel Result:enter image description here

    Login or Signup to reply.
  2. 0

    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"

    enter image description here

    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

    enter image description here

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search