PowerBI-KQL - Governance.kql.pbit - Payload size error

[l3hMichaelPeterson]

The "VirtualMachines" query in this report gives me - "Response payload size is 10722006, and has exceeded the limit of 8388608. Please consider querying less data at a time and make paginated call if needed." Anyone familiar with how to paginate this query ? I've tried various ways using metadata, skipToken, limits but nothing seems to work.
let
query = "
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| extend
provisioningState = properties.provisioningState,
timeCreated = properties.timeCreated,
networkProfile = properties.networkProfile,
osProfile = properties.osProfile,
storageProfile = properties.storageProfile,
hardwareProfile = properties.hardwareProfile,
vmId = properties.vmId,
diagnosticsProfile = properties.diagnosticsProfile,
extended = properties.extended,
licenseType = properties.licenseType
| extend
['osProfile.computerName'] = osProfile.computerName,
['osProfile.requireGuestProvisionSignal'] = osProfile.requireGuestProvisionSignal,
['osProfile.allowExtensionOperations'] = osProfile.allowExtensionOperations,
['osProfile.adminUsername'] = osProfile.adminUsername,
['osProfile.secrets'] = osProfile.secrets,
['osProfile.linuxConfiguration'] = osProfile.linuxConfiguration,
['osProfile.windowsConfiguration'] = osProfile.windowsConfiguration,
networkInterfaces = networkProfile.networkInterfaces
| mv-expand ['networkInterfaces']
| extend
['networkInterfaces.properties'] = networkInterfaces.properties,
['networkInterfaces.id'] = networkInterfaces.id,
['networkInterfaces.properties.deleteOption'] = networkInterfaces.properties.deleteOption,
['storageProfile.imageReference'] = storageProfile.imageReference,
['storageProfile.dataDisks'] = storageProfile.dataDisks,
['storageProfile.osDisk'] = storageProfile.osDisk,
['storageProfile.imageReference.publisher'] = storageProfile.imageReference.publisher,
['storageProfile.imageReference.sku'] = storageProfile.imageReference.sku,
['storageProfile.imageReference.version'] = storageProfile.imageReference.version,
['storageProfile.imageReference.exactVersion'] = storageProfile.imageReference.exactVersion,
['storageProfile.imageReference.offer'] = storageProfile.imageReference.offer
| mv-expand ['storageProfile.dataDisks']
| extend
['storageProfile.dataDisks.name'] = storageProfile.dataDisks.name,
['storageProfile.dataDisks.createOption'] = storageProfile.dataDisks.createOption,
['storageProfile.dataDisks.deleteOption'] = storageProfile.dataDisks.deleteOption,
['storageProfile.dataDisks.managedDisk'] = storageProfile.dataDisks.managedDisk,
['storageProfile.dataDisks.caching'] = storageProfile.dataDisks.caching,
['storageProfile.dataDisks.toBeDetached'] = storageProfile.dataDisks.toBeDetached,
['storageProfile.dataDisks.writeAcceleratorEnabled'] = storageProfile.dataDisks.writeAcceleratorEnabled,
['storageProfile.dataDisks.lun'] = storageProfile.dataDisks.lun,
['storageProfile.dataDisks.managedDisk.id'] = storageProfile.dataDisks.managedDisk.id,
['storageProfile.osDisk.name'] = storageProfile.osDisk.name,
['storageProfile.osDisk.createOption'] = storageProfile.osDisk.createOption,
['storageProfile.osDisk.osType'] = storageProfile.osDisk.osType,
['storageProfile.osDisk.deleteOption'] = storageProfile.osDisk.deleteOption,
['storageProfile.osDisk.managedDisk'] = storageProfile.osDisk.managedDisk,
['storageProfile.osDisk.caching'] = storageProfile.osDisk.caching,
['storageProfile.osDisk.managedDisk.id'] = storageProfile.osDisk.managedDisk.id,
['hardwareProfile.vmSize'] = hardwareProfile.vmSize,
['diagnosticsProfile.bootDiagnostics'] = diagnosticsProfile.bootDiagnostics,
['diagnosticsProfile.bootDiagnostics.enabled'] = diagnosticsProfile.bootDiagnostics.enabled,
['diagnosticsProfile.bootDiagnostics.storageUri'] = diagnosticsProfile.bootDiagnostics.storageUri
| extend
instanceView = extended.instanceView,
powerState = extended.instanceView.powerState
| extend
['powerState.displayStatus'] = powerState.displayStatus
| mv-expand ['zones']
" & ftk_DemoFilter(),
Source = AzureResourceGraph.Query(query, "Tenant", null, null, [resultTruncated = false]),
NullHandling =
if Table.HasColumns(Source, "Results") and Table.RowCount(Source) = 1 then
#table(
{ "id", "name", "provisioningState", "timeCreated", "networkProfile", "osProfile", "storageProfile", "hardwareProfile", "vmId", "diagnosticsProfile", "extended", "licenseType", "osProfile.computerName", "osProfile.requireGuestProvisionSignal", "osProfile.allowExtensionOperations", "osProfile.adminUsername", "osProfile.secrets", "osProfile.linuxConfiguration", "osProfile.windowsConfiguration", "networkInterfaces", "networkInterfaces.properties", "networkInterfaces.id", "networkInterfaces.properties.deleteOption", "storageProfile.imageReference", "storageProfile.dataDisks", "storageProfile.osDisk", "storageProfile.imageReference.publisher", "storageProfile.imageReference.sku", "storageProfile.imageReference.version", "storageProfile.imageReference.exactVersion", "storageProfile.imageReference.offer", "storageProfile.dataDisks.name", "storageProfile.dataDisks.createOption", "storageProfile.dataDisks.deleteOption", "storageProfile.dataDisks.managedDisk", "storageProfile.dataDisks.caching", "storageProfile.dataDisks.toBeDetached", "storageProfile.dataDisks.writeAcceleratorEnabled", "storageProfile.dataDisks.lun", "storageProfile.dataDisks.managedDisk.id", "storageProfile.osDisk.name", "storageProfile.osDisk.createOption", "storageProfile.osDisk.osType", "storageProfile.osDisk.deleteOption", "storageProfile.osDisk.managedDisk", "storageProfile.osDisk.caching", "storageProfile.osDisk.managedDisk.id", "hardwareProfile.vmSize", "diagnosticsProfile.bootDiagnostics", "diagnosticsProfile.bootDiagnostics.enabled", "diagnosticsProfile.bootDiagnostics.storageUri", "instanceView", "powerState", "powerState.displayStatus" },
{}
)
else Source
in
NullHandling

[flanakin]

@nteyan Did you ever get a chance to look at this one?

[francesco1119]

This is a bug, it's still open and ongoing:

Is there any workaround?

[francesco1119]

But I think it's wrong to look at just one query.
In your example it's the VirtualMachines query that is failing.
In my example is the NetworkSecurityGroups. (not sure why, we might have tons of them?)

So for me the query that is failing is this:

    resources
    | where type =~ 'microsoft.network/networksecuritygroups'
    | extend
      ['properties.resourceGuid'] = properties.resourceGuid,
      ['properties.securityRules'] = properties.securityRules
    | mv-expand ['properties.securityRules']
    | extend
      ['properties.securityRules.name'] = properties.securityRules.name,
      ['properties.securityRules.id'] = properties.securityRules.id,
      ['properties.securityRules.properties.destinationAddressPrefix'] = properties.securityRules.properties.destinationAddressPrefix,
      ['properties.securityRules.properties.sourceAddressPrefix'] = properties.securityRules.properties.sourceAddressPrefix,
      ['properties.securityRules.properties.destinationPortRange'] = properties.securityRules.properties.destinationPortRange,
      ['properties.securityRules.properties.sourcePortRange'] = properties.securityRules.properties.sourcePortRange,
      ['properties.securityRules.properties.protocol'] = properties.securityRules.properties.protocol,
      ['properties.securityRules.properties.direction'] = properties.securityRules.properties.direction,
      ['properties.securityRules.properties.priority'] = properties.securityRules.properties.priority,
      ['properties.securityRules.properties.access'] = properties.securityRules.properties.access

That same query fails in the Azure Graph Explorer with the same error:

So this means that the problem is not at Power BI level.

It's also worth mentioning that I face this:

Not sure why because the Cost Summary report is working fine and doesn't need extra privileges.
The Governance reports apparently needs it.

[flanakin]

This happens because of the number of resources you have. The issue only surfaces in the Governance and sometimes Workload optimization reports because those are the only ones that query Azure Resource Graph. The fix is to simplify the query. I never dug into this query enough to understand the full details, but I believe there are some property/array expansions that bloat the row size.

As a short-term fix, I would recommend removing any columns you don't need from the query. I don't have enough context in this space to do it, but if you know what could possibly be trimmed, we can definitely take a look and easily filter things out.