Azure policy information extract using powershell

I am trying trying to get Azure policy result information for a particular named policy, I have been playing with the query and got this far. What is missing is the actual resource, I ca get the resource group, subscription etc but not the underlying non-compliant resource and the reason for non-compliance.

$output_sub = "C:\data\output_$(get-date -f dd-MM-yyyyHHMM).csv"
$output_all = Get-AzPolicyState -ManagementGroupName  'xx' -Filter " ComplianceState eq 'NonCompliant' and PolicySetDefinitionName eq 'xxx' " `
        | Select-Object    Timestamp, `
                    SubscriptionId, `
                    ResourceLocation, `
                    ResourceGroup, `
                    PolicySetDefinitionName, `
                    ManagementGroupIds, `
                    PolicyDefinitionReferenceId, `
                    ComplianceState, `
                    PolicyDefinitionAction, `
                    ResourceTags

   $output_all | Export-Csv  -NoTypeInformation -Path $output_sub

To get to the actual resource from the portal, one has to drill down from one link to the other, what i am missing is the data join to get the underlying resource. To get the specific policy control that is being violated, I run the code below and then use vlookup in excel to link up the description via the PolicyDefinitionReferenceId again, there must be a better way to bring up the data together in one query and not having to use excel to do a vlookup.

Get-AzPolicyDefinition -ManagementGroupName  'xxx' `
    | Select-Object ResourceName,ResourceId,@{n='DisplayName';E={$_.Properties.DisplayName}}, @{n='Description';E={$_.Properties.Description}} `
    | export-csv -NoTypeInformation $definition_file

I am trying trying to get Azure policy result information for a particular named policy, I have been playing with the query and got this far. What is missing is the actual resource, I ca get the resource group, subscription etc but not the underlying non-compliant resource.

Here is an updated PowerShell script to retrieve the Policy compliance results, including non-compliant resources.

    $output_sub = "/home/user/result1_$(get-date -f dd-MM-yyyyHHMM).csv"
$output_all = Get-AzPolicyState -Filter "(PolicyDefinitionName eq '7b33f61f-1499-4a41-81e7-bfa5dc9a68e5') and ComplianceState eq 'NonCompliant'" 

$resourceNames = @{}

foreach ($resource in $output_all) {
    $resourcename = ($resource.ResourceId -split '/')[-1]
    $resourceNames[$resource.ResourceId] = $resourcename
}

$result = $output_all | ForEach-Object {
    [PSCustomObject]@{
        Timestamp               = $_.Timestamp
        ResourceId              = $_.ResourceId
        ResourceLocation        = $_.ResourceLocation
        ResourceType            = $_.ResourceType
        SubscriptionId          = $_.SubscriptionId
        ResourceGroup           = $_.ResourceGroup
        PolicyDefinitionName    = $_.PolicyDefinitionName
        ManagementGroupIds      = $_.ManagementGroupIds
        PolicyAssignmentScope   = $_.PolicyAssignmentScope
        IsCompliant             = $_.IsCompliant
        ComplianceState         = $_.ComplianceState
        PolicyDefinitionAction  = $_.PolicyDefinitionAction
        ResourceTags            = $_.ResourceTags
        ResourceName            = $resourceNames[$_.ResourceId] 
    }
}

# Export the result to CSV
$result | Export-Csv -NoTypeInformation -Path $output_sub

Result

After executing the script, Azure Policy compliance state values, including non-compliant resources, are stored in an Excel file.