Posted on 6/11/2021
Azure Resource Graph was introduced a while ago already. It provides the opportunity to explore your Azure resources using the Kusto query language (K-SQL).
But the reason I am writing this post is not to introduce Resource Graph. I recently had a question regarding Azure Policy. The question was "How can we extract the compliant and noncompliant resources to excel/csv". By default, there is no option (at least I am aware of) in the Azure Portal (if you know a way, please let me know 😉).
I'll focus here on the Resource Graph Explorer. Of course, you can also use the API to execute the query and retrieve the data. When you open the Portal you can navigate to the Azure Resource Graph Explorer using the Search or using the "All Services" button in the navigation. You can also use this direct link.
The Explorer provides you the option to write and execute Kusto Queries (if you are not familiar with KSQL, check out the Microsoft documentation for more information)
To start with a query you can search for "policy" in the side panel. This will provide you the following query
policyresources
| where type == "microsoft.policyinsights/policystates" This will list all "policyresources" of the type "microsoft.policyinsights/policystates". If you run the query, you'll get a list of policystates assuming you have some Policy assignments already in place.
The interesting part of the output is contained in the properties field. To extract information out of this field we need to extend it. For example, we can output the complianceState using the following addition:
| extend complianceState=properties.complianceStateThis will populate a new field with the content of the properties.complianceState. If you run the query now, you will see another field in the output. To reduce the amount of information shown you can limit the output using the project syntax.
| project complianceStateThis will limit the output to the field complianceState we just expanded. If you execute the query now, you'll see only this single field. We can now also order the list if needed. Similar to SQL we can do this by using the order by keyword.
| order by tostring(complianceState)The tostring operator is needed to convert the dynamic type "complianceState" into a string. The Graph Explorer doesn't know the field type as it was extracted from the properties field.
You might want to add additional fields by providing them in the extend part of the query by separating the fields with a comma (,). The final query might look like this:
policyresources
| where type == "microsoft.policyinsights/policystates"
| extend complianceState=properties.complianceState, policyDefinitionAction=properties.policyDefinitionAction, policyAssignmentName=properties.policyAssignmentName, policyAssignmentScope=properties.policyAssignmentScope, policyDefinitionName=properties.policyDefinitionName, policyAssignmentId=properties.policyAssignmentId, policyDefinitionReferenceId=properties.policyDefinitionReferenceId
| project complianceState, policyDefinitionAction, policyAssignmentName, policyAssignmentScope, policyDefinitionName, policyAssignmentId, policyDefinitionReferenceId
| order by tostring(complianceState)If you are happy, you can use the "Download as CSV" button to download the output :)