Kusto: How to convert table value to scalar and return from user defined function

Question

I have the following user-defined functions with the intention of using a case conditional to output a table of 0s or 1s saying whether or not an account is active.

case needs scalar values as it's arguments, ie pro_account_active(account) and basic_account_active(account) need to be scalar values.

I'm struggling to get around the limitation of toscalar:

User-defined functions can't pass into toscalar() invocation information that depends on the row-context in which the function is called.

I think if there was a function I can use in place of the "??????" that would convert active to a scalar and return it from the function it would work.

Any help greatly appreciated

let basic_account_active=(account:string) {
    basic_check_1(account) // returns 0 or 1 row only
    | union basic_check_2(account)
    | summarize result_count = count()
    | extend active = iff(result_count == 2, 1, 0)
    | ??????
};
let pro_account_active=(account:string) {
    pro_check_1(account) // returns 0 or 1 row only
    | union pro_check_2(account)
    | summarize result_count = count()
    | extend active = iff(result_count == 2, 1, 0)
    | ??????
};
let is_active=(account_type:string, account:string) {
    case(
        account_type == 'pro', pro_account_active(account),
        account_type == 'basic', basic_account_active(account),
        -1
    )
};
datatable(account_type:string, account:string)
[
 'pro', '89e5678a92',
 'basic', '9d8263da45',
 'pro', '0b975f2454a',
 'basic', '112a3f4753',
]
| extend result = is_active(account_type, account)

Avnera Avnera · Accepted Answer · 2020-09-27T06:18:56

From your example it looks that you have two tables per each account type and if both have entrees for a specific account, then the account is considered active. Is that correct? If so, I would use the "join" operator to find all the entrees in the applicable tables and count them. Here is an example of one way to do it (there are other ways as well).

let basicAccounts1 = datatable(account_type:string, account:string)[ 'basic', '9d8263da45', 'basic', '111111'];
let basicAccounts2 = datatable(account_type:string, account:string)[ 'basic', '9d8263da45', 'basic', '222222'];
let proAccounts1 = datatable(account_type:string, account:string)[ 'pro', '89e5678a92', 'pro', '111111'];
let proAccounts2 = datatable(account_type:string, account:string)[ 'pro', '89e5678a92', 'pro', '222222'];
let AllAccounts = union basicAccounts1, basicAccounts2, proAccounts1, proAccounts2
| summarize count() by account, account_type;
datatable(account_type:string, account:string)
[
 'pro', '89e5678a92',
 'basic', '9d8263da45',
 'pro', '0b975f2454a',
 'basic', '112a3f4753',
]
| join kind=leftouter hint.strategy=broadcast (AllAccounts) on account, account_type
| extend IsActive = count_ >=2
| project-away count_, account1, account_type1

The results are:

Kusto: How to convert table value to scalar and return from user defined function

2 Answers