Azure App insights logs externaldata operator to function

Question

Guys In app insight logs, I have in Custom metrics customDimensions a column that stores performance counters that come from different processes, I use custom service for the export to insight and the process, I use the command line tag, and the record in app insight is "D:\SvcFab_App\Services.AppType_App3058\Services .MyServicePkg.Code.1.0.0.20231208.3\Services.City.MyService.exe".The problem is that I have about 40 service fabric app types and I want to make a query to read a blob storage JSON file that I use as a mapping to know for example that App3058 is TenantName/MyService.exe. Look at my sample query but something I can't figure out how to do.

let LookupTargetApp = (cmdline: string) {
    toscalar(
        externaldata(targetApp: string, testline: string)
        [ 
            h@'https://mytest-blob.blob.core.windows.net/test/test.json?somesastoken'
        ]
        with(format='multijson', ingestionMapping='[{"Column":"targetApp","Properties":{"Path":"$.targetApp"}},{"Column":"testline","Properties":{"Path":"$.testline"}}]')
        | where testline == cmdline
        | project targetApp
    )
};

customMetrics
| where customDimensions != "" and name contains "procstat_memory_usage"
| extend cmdLineJson = parse_json(customDimensions).cmdline
| extend appType = trim('"' , tostring(split(cmdLineJson, '\\')[3]))
| extend appName = trim('"' , tostring(split(cmdLineJson, '\\')[4]))
| extend parts = split(appName, ".")
| extend result = strcat(parts[0], ".", parts[1], ".", parts[2])
| extend applicationName = strcat(appType, '\\', result)
| extend TargetApp = LookupTargetApp(applicationName)
| where name == "procstat_memory_usage"
| project TargetApp,value,valueMax,customDimensions

In the above example I receive error can't use '' as it is defined outside its row-context scope. It's okay i guess it is a limitation by design.

The idea is that I have a Json file in which there are two properties, one is testline:Services.AppType_App3058\Services.City.MyService.exe targetapp:MyTenant/MyService.exe .Is it possible I want to have the function that expects a string input and then reads the file from the blob maps the columns and compares and outputs the given targetApp.Tried with case() in app insight and replacement work as well but imagine I have 40 apptypes and each one has 7 services the query will be so hard to maintenance. Thank you!

Answer 1

There is limitation in toscalar() function. You are trying to call the user defined function which has toscalar() in your table using extend operator. But this function can be called only constant number of times during query execution.

toscalar() can't be applied on a scenario that applies the function on each row

Workaround is to use join operator and join both the tables and instead of using extend operator.

Code:

let LookupTargetApp =
        externaldata(targetApp: string, testline: string)
        [ 
            h@'https://mytest-blob.blob.core.windows.net/test/test.json?somesastoken'
        ]
        with(format='multijson', ingestionMapping='[{"Column":"targetApp","Properties":{"Path":"$.targetApp"}},{"Column":"testline","Properties":{"Path":"$.testline"}}]')
        | where testline == cmdline
        | project targetApp;

customMetrics
| where customDimensions != "" and name contains "procstat_memory_usage"
| extend cmdLineJson = parse_json(customDimensions).cmdline
| extend appType = trim('"' , tostring(split(cmdLineJson, '\\')[3]))
| extend appName = trim('"' , tostring(split(cmdLineJson, '\\')[4]))
| extend parts = split(appName, ".")
| extend result = strcat(parts[0], ".", parts[1], ".", parts[2])
| extend applicationName = strcat(appType, '\\', result)
| join kind = inner (LookupTargetApp) on $left.applicationName = $right.testline
| where name == "procstat_memory_usage"
| project TargetApp,value,valueMax,customDimensions

In the above code, replaced extend operator with join operator.

Refer the MS document which has sample codes.