Have you tried to get data on exemptions in your environment? Only to find they’re not in Azure Resource Graph, like policies, assignments and their states. Previously you would have to queried the API, which is limited to querying one subscription at a time. Not exactly “cloud scale.” Sometime in the last few weeks “microsoft.authorization/policyexemptions” showed up under the “policyresources” table in Azure Resource Graph. Unfortunately for me, I had already worked out pulling the data from the API and joining it with ARG data with KQL.
But because ARG also pulls from the same APIs, my KQL queries were mostly exactly the same. I’ll post some here and on my ARG queries github.
Queries
The basic properties we can extract are the Policy Assignment ID the exemption is assigned to. Definition references, these are the policy definitions the exemption affects. Exemption Category, description, and expiry.
policyresources
| where type =~ 'microsoft.authorization/policyexemptions'
| extend policyAssignmentId = tolower(properties.policyAssignmentId),
DefRecs = properties.policyDefinitionReferenceIds,
exemptionCategory = tostring(properties.exemptionCategory),
displayName = tostring(properties.displayName),
exemptionDescription = tostring(properties.description),
exemptionExpires = todatetime(properties.expiresOn)
This query looks at the exemption time and provides an amount of time till an expiration expires and provides that in seconds, minutes, hours or days, depending on which is longest. If no expiration is set it calls that out as well. Then provides a summary count of exemptions, assignments with Exemptions, Exemptions expiring in less than 30 days, expired exemptions, and count of exemptions with no expiry set.
policyresources
| where type =~ 'microsoft.authorization/policyexemptions'
| extend policyAssignmentId = tolower(properties.policyAssignmentId),
DefRecs = properties.policyDefinitionReferenceIds,
exemptionCategory = tostring(properties.exemptionCategory),
displayName = tostring(properties.displayName),
exemptionDescription = tostring(properties.description),
exemptionExpires = todatetime(properties.expiresOn)
| extend Time = exemptionExpires - now()
| extend WaiverStatus = iff(exemptionExpires > now(),
strcat('🕒', " Exemption Expires ",
case(Time < 2m, strcat(toint(Time / 1m), ' seconds'), //begin case, if iff is true convert time
Time < 2h, strcat(toint(Time / 1m), ' minutes'),
Time < 2d, strcat(toint(Time / 1h), ' hours'),
strcat(toint(Time / 1d), ' days')), ' from now'),//end case
iff(isnull(Time), "No Expiration Set","Exemption Expired")) //second iff for null values //end first iff
| summarize Expires30Days = countif(Time < 30d),
Expired= countif(WaiverStatus == "ExemptionExpired"),
['Assignments with Exemptions']=dcount(policyAssignmentId),
Exemptions = dcount(id),
['No Expiration Set'] = countif(WaiverStatus == "No Expiration Set")
This next one gets all exemptions. Then expands the PolicyDefinition array. Makes array sets of the exemption names and Ids that are applied to each Definition, with a count of exemptions for each definition.
policyresources
| where type =~ 'microsoft.authorization/policyexemptions'
| extend policyAssignmentId = tolower(properties.policyAssignmentId),
DefRecs = properties.policyDefinitionReferenceIds,
exemptionCategory = tostring(properties.exemptionCategory),
exemptionId = tolower(id),
exemptionName = tostring(name)
| mv-expand policyDefGUID = DefRecs
| summarize Exemptions = make_set(exemptionName),
ExemptionIds = make_set(exemptionId)
by policyAssignmentId
| extend Count = array_length(ExemptionIds)
This one is exactly the same as above, but instead of definition, it provides the same information by assignment
policyresources
| where type =~ 'microsoft.authorization/policyexemptions'
| extend policyAssignmentId = tolower(properties.policyAssignmentId),
DefRecs = properties.policyDefinitionReferenceIds,
exemptionCategory = tostring(properties.exemptionCategory),
exemptionId = tolower(id), exemptionName = tostring(name)
| mv-expand policyDefGUID = DefRecs
| summarize Exemptions = make_set(exemptionName),
ExemptionIds = make_set(exemptionId)
by policyDefinitionId = strcat("/providers/microsoft.authorization/policydefinitions/",policyDefGUID)
| extend Count = array_length(ExemptionIds)
Note: I don’t put anything on my blog I haven’t personally verified as working. So if one of these queries doesn’t work please let me know. Its likely formatting got messed up going from azure portal -> notepad -> wordpress blog
The Beast
This query combines policy states with exemptions and provides a comprehensive view of resources, policies, and definitions that have exemptions applied. As well as provides a timeframe in which the policy exemption will expire or call out if no expiry is set.
policyresources
| where type =~ 'microsoft.policyinsights/policystates'
| extend
resourceId = tostring(properties.resourceId),
resourceType = tolower(tostring(properties.resourceType)),
policyAssignmentId = tolower(properties.policyAssignmentId),
policyDefinitionId = tostring(properties.policyDefinitionId),
policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),
policyAssignmentScope = tostring(properties.policyAssignmentScope),
ComplianceState = tostring(properties.complianceState),
assignmentName = tostring(properties.policyAssignmentName)
| where ComplianceState =~ "Exempt"
| join kind = leftouter(
policyresources
| where type =~ 'microsoft.authorization/policyexemptions'
| extend policyAssignmentId = tolower(properties.policyAssignmentId),
DefRecs = properties.policyDefinitionReferenceIds,
exemptionCategory = tostring(properties.exemptionCategory),
displayName = tostring(properties.displayName),
exemptionDescription = tostring(properties.description),
exemptionId = tolower(id),
exemptionName = name,
exemptionExpires = todatetime(properties.expiresOn))
on policyAssignmentId
| extend Time = exemptionExpires - now()
| extend WaiverStatus = iff(exemptionExpires > now(),
strcat('🕒', " Exemption Expires ",
case(Time < 2m, strcat(toint(Time / 1m), ' seconds'), //begin case, if iff is true convert time
Time < 2h, strcat(toint(Time / 1m), ' minutes'),
Time < 2d, strcat(toint(Time / 1h), ' hours'),
strcat(toint(Time / 1d), ' days')), ' from now'),//end case
iff(isnull(Time), "No Expiration Set","Exemption Expired")) //second iff for null values //end
| sort by Time asc
| extend ExpiresSoon = case(WaiverStatus == "ExemptionExpired", 'AlreadyExpired',
Time < 30d,'true',
Time > 30d, 'false',
WaiverStatus == "No Expiration Set", "No Expiration Set",
'unknown')
| project-away Time, policyAssignmentId1
| extend Details = pack_all()
| project exemptionId, exemptionName,
WaiverStatus, ExpiresSoon,
assignmentName, resourceId,
policyAssignmentId, policyDefinitionId,
Details
I’m not entirely sure there isn’t a better way to accomplish the time and waiver stuff in the query. Its just what I came up with in the moment. Like Taravangian from the Stormlight Archive, this could be from one of my brilliant days, or one of my stupid days.