microsoft
Duplicate Values in Workload Optimization PowerBI
Refreshing the WorkloadOptimization PowerBI in FinOps 0.6 I get an error on all tables of:
"Load was cancelled by an error in loading a previous table."
Looking down at the Resources section there's an error of:
Column 'id' in Table 'Resources' contains a duplicate value '/subscriptions/SubID/resourceGroups/RGName/providers/Microsoft.HybridCompute/machines/VMName/extensions/AzureMonitorWindowsAgent' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
More than likely this was one of our Arc enabled virtual machines that got rebuilt with the same name and had the Azure Monitor Agent extension reinstall. A pretty common situation. Is there any way I can resolve this myself or can logic be added in future implementations to consider this scenario?
This issue seems to still be present, but for resources other than Virtual Machines and Disks. I am receiving the error in Power BI for other resources, here is an example of one of the errors:
Column 'id' in Table 'Resources' contains a duplicate value '/subscriptions/xxxxx/resourcegroups/xxxxxxx-rg/providers/microsoft.hybridcompute/machines/xxxxxxxx/extensions/mde.windows' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
When I run the resource graph query in the Azure Portal and look for duplicate entries in the ID column, I see the following resource types: microsoft.compute/virtualmachines/extensions microsoft.hybridcompute/machines/extensions microsoft.network/networkwatchers microsoft.managedidentity/userassignedidentities microsoft.sql/servers/xxxx/databases/master
Using v0.8 through storage or KQL I am getting the same on both Workload Optimization and Governance.
However so far the only error I see (or get to) is: "Column 'id' in Table 'Resources' contains a duplicate value '/subscriptions/xxxxx/resourcegroups/xxxxxxx-rg/providers/microsoft.network/privatednszones/privatelink.xxxxxxxxxxxxxxxxxxx' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."
microsoft.network/privatednszones
Since today I am also now getting duplicate errors on: microsoft.compute/virtualmachines/extensions
After some investigation, we realized there's no clean way to fix this without potentially removing valid resources or possibly even including invalid data. We added the suggested workaround but commented it out. If anyone needs to apply it, they can uncomment it and update the output for the Resources function.
If you are facing this issue, please file a support request against the Azure Resource Graph team stating that there's a bug in the resources table that includes multiple rows with the same resource ID. Sometimes the rows are the same and sometimes they have a different resource group -- at least, that's what @nteyan found.
Since for the folks like myself where the issues occurs the alternative is the report being unusable, I'll take the missing resources. Luckily all the usual suspect resources listed in this thread so far don't normally produce a cost so the potential negative impact for the workaround seems lower. Could you point out which line it is that needs to be uncommented in future releases?
We can add a parameter to work around the bug. The problem with defaulting to the workaround is you won't know when data isn't accurate/complete. The only real fix is to file a support request on ARG so they fix the underlying issue. We were hoping to drive more attention to that since fixing that would resolve it for everyone. But I do get your point.
Totally understandable I imagine you'd eventually get a similar issue for the inverse if you defaulted to this, so I'm ok with an opt-in parameter myself.
I'm not sure I necessarily consider it a "bug" in Resource Graph so much as a design flaw\consideration in Azure Resources as a whole. Because the ResourceID is built based off the path similar to an Active Directory Distinguished Name anywhere they allow you to have multiple resources with the same name in the same resource group will have identical ResourceIDs. Usually you'll get a conflict error on posting builds but some things like extensions don't see to have the same restriction.
That being said if this was observed as stated "Sometimes the rows are the same and sometimes they have a different resource group" and a Resource had a ResourceID returned by Graph that didn't correspond to its Resource Group that would definitely be a bug. Never seen that myself.