AOE workbook entries other than Resource Inventory throw "failed to resolve scalar expression"

[MohnJadden]

🐛 Problem

After deploying Azure Optimization Engine via the steps described at https://microsoft.github.io/finops-toolkit/optimization-engine#-deployment-instructions and waiting a few days, all the AOE workbook entries are displaying an error: 'where' operator: Failed to resolve scalar expression named 'MeterSubCategory_s'.... I can only view the contents of Resource Inventory.

👣 Repro steps

1) Configure resources to send all diagnostic logs to a Log Analytics workspace 2) Deploy Azure Optimization engine to the same Log Analytics workspace 3) Wait 24 hours 4) Go to the same LA workspace -> Workbooks -> click on Costs Growing, Block Blob Storage Usage, or other workbooks 5) Receive errors

🤔 Expected

The workbook displays any info related to its purpose

📷 Screenshots

ℹ️ Additional context

🙋‍♀️ Ask for the community

We could use your help:

1. Please vote this issue up (👍) to prioritize it.
2. Leave comments to help us solidify the vision.

[helderpinto]

Thanks for reporting this issue, @MohnJadden.

First, what type of Azure subscription did you deploy AOE against? EA? MCA? Other?

The errors you describe can only be attributable to issues in the Azure consumption (billing) ingestion pipeline. Can you please check whether the Export-ConsumptionToBlobStorage Azure Automation runbook is completing successfully? If not, can you check what is the Exception message? If they complete successfully, can you check the job logs and share here the (anonymized) output?

[MohnJadden]

We're an MCA. The runbook is showing that it's completed successfully.

Log output is below - looks like it's successful:

7/17/2024, 2:18:41 PM - Output: Consumption Scope not specified, defaulting to Subscription
7/17/2024, 2:18:42 PM - Output: Logging in to Azure with ManagedIdentity...
7/17/2024, 2:18:45 PM - Output: 
Environments                                                                                           Context          
------------                                                                                           -------          
{[AzureChinaCloud, AzureChinaCloud], [AzureCloud, AzureCloud], [AzureUSGovernment, AzureUSGovernment]} Microsoft.Azur...

7/17/2024, 2:18:45 PM - Output: Getting Storage Account context with login
7/17/2024, 2:18:46 PM - Output: 
Name               : SubscriptionName (SubscriptionGUID) - TenantGUID - 
                     MSINumber
Account            : MSINumber
Environment        : AzureCloud
Subscription       : SubscriptionGUID
Tenant             : TenantGUID
TokenCache         : 
VersionProfile     : 
ExtendedProperties : {}

7/17/2024, 2:18:46 PM - Output: Exporting consumption data from 2024-07-14 to 2024-07-14 for 1 subscriptions...
7/17/2024, 2:18:47 PM - Output: Starting consumption export process from 2024-07-14 to 2024-07-14 for subscription SubscriptionName...
7/17/2024, 2:20:39 PM - Output: Generated 1454 entries...
7/17/2024, 2:20:40 PM - Output: Uploading CSV to Storage
7/17/2024, 2:21:03 PM - Output: 
ICloudBlob                         : Microsoft.Azure.Storage.Blob.CloudBlockBlob
BlobType                           : BlockBlob
Length                             : 1226523
IsDeleted                          : False
BlobClient                         : Azure.Storage.Blobs.BlobClient
BlobBaseClient                     : Azure.Storage.Blobs.Specialized.BlockBlobClient
BlobProperties                     : Azure.Storage.Blobs.Models.BlobProperties
RemainingDaysBeforePermanentDelete : 
ContentType                        : text/csv
LastModified                       : 7/17/2024 6:21:02 PM +00:00
SnapshotTime                       : 
ContinuationToken                  : 
VersionId                          : 
IsLatestVersion                    : 
AccessTier                         : Cool
TagCount                           : 0
Tags                               : 
ListBlobProperties                 : 
Context                            : Microsoft.WindowsAzure.Commands.Storage.AzureStorageContext
Name                               : 2024-07-14-SubscriptionGUID-AmortizedCost.csv

7/17/2024, 2:21:03 PM - Output: [2024-07-17T18:21:03.166Z] Uploaded 2024-07-14-SubscriptionGUID-AmortizedCost.csv to Blob Storage...
7/17/2024, 2:21:03 PM - Output: [2024-07-17T18:21:03.260Z] Removed 2024-07-14-SubscriptionGUID-AmortizedCost.csv from local disk...

[helderpinto]

Can you check what type of MCA is your Azure consumption? Please, run the query below in the AOE Log Analytics workspace and share the value for the QuotaID column:

AzureOptimizationResourceContainersV1_CL
| where ContainerType_s == 'microsoft.resources/subscriptions'
| project SubscriptionName=ContainerName_s, QuotaID=parse_json(ContainerProperties_s).subscriptionPolicies.quotaId

[helderpinto]

Hi, @MohnJadden.

Assuming the QuotaID for your subscription is PayAsYouGo_2014-09-01, I was able to reproduce your issue. Can you please replace the Export-ConsumptionToBlobStorage automation runbook code with the one available in the tentative fix here and then publish the runbook? This should solve the problem on the next daily export.

[MohnJadden]

Confirmed - that solved the issue and I'm not seeing that error anymore. Thank you!