Using column_ifexists in KQL user function

Question

I am attempting to create a function off of pivoted content which has dynamic content. An example of my scenario looks something like this:

let countActivities = (col: string) {
    array_length(column_ifexists(col, dynamic([])))
};
let testdata = datatable(ActivityId: string, Message: string, SessionId: string)
    ["1", "abc", "100",
     "2", "def", "100",
     "2", "ghi", "100",
     "4", "jkl", "100",
     "1", "mno", "200"];
testdata
| extend iActivityId = toint(ActivityId)
| evaluate pivot(iActivityId, make_set(Message), SessionId)
| project SessionId,
          Has1 = countActivities("1"),
          Has2 = countActivities("2"),
          Has3 = countActivities("3"),
          Has4 = countActivities("4");

I'd like the above countActivities to return the number of elements in the array of a dynamic column, however the above causes the error columnifexists(): cannot be used in the function without tabular argument. I'd expect this to be a scalar function, so not certain how to provide a tabular argument in this scenario.

How should the above countActivities be written to allow this?

Yoni L. Yoni L. · Accepted Answer · 2020-09-08T21:53:08

a column can only exist in a tabular expression (e.g. a table). your original function doesn't have such (i.e. where would you expect the columns to exist?), hence the error message you're seeing.

how about the following alternative?

let f = (arr:dynamic, col: string) {
    array_index_of(arr, col) != -1
};
let testdata = datatable(ActivityId: string, Message: string, SessionId: string)
    ["1", "abc", "100",
     "2", "def", "100",
     "2", "ghi", "100",
     "4", "jkl", "100",
     "1", "mno", "200"];
testdata
| summarize make_set(ActivityId) by SessionId
| project SessionId,
          Has1 = f(set_ActivityId, "1"),
          Has2 = f(set_ActivityId, "2"),
          Has3 = f(set_ActivityId, "3"),
          Has4 = f(set_ActivityId, "4")

-->

SessionId   Has1    Has2    Has3    Has4
100         TRUE    TRUE    FALSE   TRUE
200         TRUE    FALSE   FALSE   FALSE

Using column_ifexists in KQL user function

1 Answers