"[BadRequest] Failed to execute script on database 'Ingestion'." with fresh install of v0.9

[philipstreet]

🐛 Problem

Clean install of v0.9 results in following error in "[ade name/Ingestion/SetupScript" step of dataExplorer;

{
    "code": "ScriptExecutionFailed",
    "message": "
        [BadRequest] Failed to execute script on database 'Ingestion'. Here is a list of the commands that failed: Command: 
        
        .create-or-alter function
        with (docstring = \"All recommendations transformed to FOCUS 1.0.\",folder = \"Recommendations\")
        Recommendations_transform_v1_0()
        {
            // NOTE: All open issues and questions are tracked @ https://github.com/microsoft/finops-toolkit/issues/1111
            let isoMonths = (duration: string) {
                let number = toint(replace_regex(duration, @'[PMY]', ''));
                toint(case(
                    duration == '', toint(''),
                    duration endswith \"Y\", number * 12,
                    duration endswith \"M\", number,
                    -1
                ))
            };
            Recommendations_raw
            | extend x_IngestionTime = ingestion_time()
            //
            // Set ProviderName
            | extend ProviderName = 'Microsoft'
            //
            // Set source columns
            | extend x_SourceName     = coalesce(x_SourceName, iff(ProviderName == 'Microsoft', 'Cost Management', ProviderName))
            | extend x_SourceProvider = coalesce(x_SourceProvider, ProviderName)
            | extend x_SourceType     = coalesce(x_SourceType, iff(ProviderName == 'Microsoft', 'ReservationRecommendations', ''))
            | extend x_SourceVersion  = coalesce(x_SourceVersion, iff(ProviderName == 'Microsoft', '2023-05-01', ''))
            //
            // Convert JSON cost columns to decimal
            | extend CostWithNoReservedInstances    = case(isnotempty(CostWithNoReservedInstances),    CostWithNoReservedInstances,    isnotempty(CostWithNoReservedInstancesJson),    todecimal(extract(@'\"value\":([0-9\\.]+)', 1, CostWithNoReservedInstancesJson)),    CostWithNoReservedInstances)
            | extend NetSavings                     = case(isnotempty(NetSavings),                     NetSavings,                     isnotempty(NetSavingsJson),                     todecimal(extract(@'\"value\":([0-9\\.]+)', 1, NetSavingsJson)),                     NetSavings)
            | extend TotalCostWithReservedInstances = case(isnotempty(TotalCostWithReservedInstances), TotalCostWithReservedInstances, isnotempty(TotalCostWithReservedInstancesJson), todecimal(extract(@'\"value\":([0-9\\.]+)', 1, TotalCostWithReservedInstancesJson)), TotalCostWithReservedInstances)
            //
            // Build recommendation details
            | lookup kind=leftouter (database('Ingestion').Regions | summarize RegionName = make_set(RegionName)[0] by Location = RegionId) on Location
            | extend x_RecommendationDetails = case(
              x_SourceType == 'ReservationRecommendations', bag_pack(
                'CommitmentDiscountNormalizedGroup', InstanceFlexibilityGroup,
                'CommitmentDiscountNormalizedRatio', InstanceFlexibilityRatio,
                'CommitmentDiscountNormalizedSize', NormalizedSize,
                'CommitmentDiscountResourceType', ResourceType,
                'CommitmentDiscountScope', coalesce(Scope, scope),
                'LookbackPeriodDuration', case(
                    LookBackPeriod matches regex @'^Last([0-9]+)Days$', replace_regex(LookBackPeriod, @'^Last([0-9]+)Days$', @'P\\1D'),
                    LookBackPeriod matches regex @'^[0-9]+$',           strcat('P', LookBackPeriod, 'D'),
                    ''
                ),
                'LookbackPeriodStart', FirstUsageDate,
                'RecommendedQuantity', RecommendedQuantity,
                'RecommendedQuantityNormalized', RecommendedQuantityNormalized,
                'RegionId', Location,
                'RegionName', RegionName,
                'SkuMeterId', coalesce(MeterId, MeterID),
                'SkuPriceDetails', SkuProperties,
                'SkuSize', coalesce(SKU, SkuName),
                'SkuTerm', isoMonths(Term)
              ),
              dynamic({})
            )
            //
            // Sort columns and apply final transforms
            | extend x_RecommendationDate = FirstUsageDate + (toint(extract(@'^P([0-9]+)D$', 1, tostring(x_RecommendationDetails.LookbackPeriodDuration))) * 1d)
            | extend x_RecommendationDate = iff(x_RecommendationDate > now(), startofday(now()), x_RecommendationDate)
            | project
                ProviderName,
                SubAccountId = iff(isnotempty(SubscriptionId), strcat('/subscriptions/', SubscriptionId), ''),
              x_IngestionTime,
                x_EffectiveCostAfter = TotalCostWithReservedInstances,
                x_EffectiveCostBefore = CostWithNoReservedInstances,
                x_EffectiveCostSavings = NetSavings,
                x_RecommendationDate,
                x_RecommendationDetails,
                x_SourceName,
                x_SourceProvider,
                x_SourceType,
                x_SourceVersion
        }
        
        Reason: Request is invalid and cannot be processed: Semantic error: SEM0100: 'extend' operator: Failed to resolve scalar expression named 'scope'"
}

👣 Repro steps

Perform installation of v0.9 using ARM template https://aka.ms/finops/hubs/deploy with default settings plus ADE cluster name (dec-finops).

On deployment page, the "dataExplorer" resource fails with status "Conflict". Clicking into that resource shows that "[ade name]/Ingestion/SetupScript" has Conflict with the above error details.

🤔 Expected

Deployment to complete without errors.

ℹ️ Additional context

I have Owner role assignment on the subscription.

🙋‍♀️ Ask for the community

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

[shubhamagarwalMSFT]

+1, I am getting the same

@flanakin

[flanakin]

@philipstreet / @shubhamagarwalMSFT Can y'all redeploy with the latest? We fixed a bug in the deployment a few days after the release.

[philipstreet]

Will do, when I return from vacation. :)

[shubhamagarwalMSFT]

@flanakin , it worked for me, I was able to deploy it

[philipstreet]

One step forward but two steps back...

I now get the following error when I use both the ARM template and the latest PowerShell module (I uninstalled all versions and then installed the latest).

At least one | resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-deployment-operations for usage | details. (Code: DeploymentFailed) - The template deployment failed with error: 'Authorization failed for template resource | 'f3e254c1-f00e-5f37-9b8d-2081d910f3aa' of type 'Microsoft.Authorization/roleAssignments'. The client '[my-email-address]' with object id | '[guid]' does not have permission to perform action 'Microsoft.Authorization/roleAssignments/write' at scope | '/subscriptions/[subscription-id]/resourceGroups/rg-philipstreet-finops-eus2-poc/providers/Microsoft.Storage/storageAccounts/finhubspocezqaff7pt3cqzo/providers/Microsoft.Authorization/roleAssignments/f3e254c1-f00e-5f37-9b8d-2081d910f3aa'.'. (Code:InvalidTemplateDeployment) CorrelationId: 965a7521-c7a5-4127-abfb-4afc613dd94f

This looks like the Authorization issue that I thought was fixed elsewhere, or maybe it is a different one. I am Owner on the subscription (via a PIM request).

[flanakin]

@philipstreet Are there limitations on what roles you can assign? I don't recall if that's the same error as this one, but it might be. We do require access to grant User Access Administrator to support managed exports. Someone else requested we make that optional. If that's what you're hitting, we can try to get that in.

Details about the permissions needed to deploy are @ https://learn.microsoft.com/cloud-computing/finops/toolkit/hubs/template#prerequisites

[philipstreet]

@flanakin I have Owner role assignment on the subscription that I have been attempting the deployment.

To test the above point, I was successfully able to create a new User Assigned Managed Identity (UAMI) and then assign "Storage Blob Data Contributor" for the UAMI on a different / existing Storage Account. BTW My account also has "Enterprise Reader" on the Billing account, added by one of our Enterprise Administrators in the Cost Management | Access control blade.

My understanding was that the managed exports was optional, which is why we are doing it manually in our PoC deployment / environment.

Let me know if there are any other manual tasks I can do to validate the permissions side of things.

[flanakin]

Yes, managed exports is optional, but we currently assign the needed access regardless of whether you end up using it or not. We got feedback last week that we should only assign access when needed, so that's why I was asking if there's overlap here. Sounds like it's not the same.

@philipstreet, can you ping me in Teams with a few times you'd be available for a call? Let's setup some time to go thru this together. I mainly want to know which step in which deployment is failing here. As a CSA, you can also create a UAT to have the CSU Infra team help out.

[idrisskahia]

I am also facing this same error on the "[ade name/Ingestion/SetupScript" step of dataExplorer with the latest deployment of the FinOps hub (v12).

[nbalan4u]

The same issue.. Any advise to fix ?

[MSBrett]

Are you supplying a scope at all with the installation? And what about ADX/ADFS - any chance you could post some screenshots of the inputs from the deployment tab on the resource group?

[idrisskahia]

Hey Brett, here is a screenshot of the inputs tab for the data explorer piece.

I am deploying through the ARM template form and haven't supplied a scope through there.