Base-36 to Base-10 conversion in ADX KQL

Question

Is there a built-in function in ADX that will convert from base-36 to base-10 and back?

We have Azure VM names with a suffix in base36, i.e., '00055k' and I want to be able to turn the suffix into its base 10 equivalent: '6680'.

I have written a user function to do this, but I am unable to call the function inline from a ADX query. The function uses a range call: range index from 0 to (strlen(reversed) - 1) step 1, and this seems to stop the function being able to be called from another query.

Thanks.

This is my existing function:

.create-or-alter function 
    with (folder = "utils", docstring = "converts from base36 to base10 (xxx-xxx-vmss000001 -> xxx-xxx-vmss_1)", skipvalidation = "true") 
    convert_to_base10(['node']:string) 
{
    let hextra_length = 6;
    let hexatridecimal = substring(node, strlen(node) - hextra_length, strlen(node));
    let ss_name = tostring(substring(node, 0, indexof(node, 'vmss') + 4));
    let reversed = reverse(hexatridecimal);
    let charList = "0123456789abcdefghijklmnopqrstuvwxyz";
    range index from 0 to (strlen(reversed) - 1) step 1
    | extend value = substring(reversed, index, 1)
    | extend index_of_char = toint(indexof(charList, value))
    | extend power = index_of_char * pow(36, index)
    | summarize strcat(ss_name, '_', toint(sum(power)))
} 

When I try and call this inline in an "extend' I get the following error:

Semantic error: Tabular expression is not expected in the current context

So, I might be either doing something wrong in my function definition, or it is not possible, in which case I wondered if there was already something that can perform this operation for me internal to ADX already.

Thanks.

Answer 1

A possible solution is the usage of a table parameter and extend the result column inside the function.

let convert_to_base10=(T: (node: string)) {
    let hextra_length = 6;
    let charList = "0123456789abcdefghijklmnopqrstuvwxyz";
    T
    | extend hexatridecimal = substring(node, strlen(node) - hextra_length, strlen(node))
    | extend ss_name = tostring(substring(node, 0, indexof(node, 'vmss') + 4))
    | extend reversed = reverse(hexatridecimal)
    | mv-apply index=range(0, strlen(reversed) - 1, 1) to typeof(int) on (
        extend value = substring(reversed, index, 1)
        | extend index_of_char = toint(indexof(charList, value))
        | extend power = index_of_char * pow(36, index)
        | summarize N=toint(sum(power))
        )
    | extend node10=strcat(ss_name, '_', N)
    | project-away hexatridecimal, ss_name, reversed, N
};
let Test=
    datatable (node: string)[
    'xxx-xxx-vmss00055k'
];
Test
| invoke convert_to_base10()