Passing colum as function parameter in Kusto Azure Log Analytics doesn't works

Question

I want to calculate in Kusto (Azure Log Analytics), based on a date, the number of days without weekends in a month.

This works (using now() as paremeter in the daysOfMonthNoWeekends function call):

let daysOfMonthNoWeekends=(_event_date_t:datetime) {
toscalar(range days from startofmonth(_event_date_t) to endofmonth(_event_date_t) step 1d
| where dayofweek(days) between(1d .. 5d)
| count)
};
//
MyTable_CL
| extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(now())

And this doesn't works:

let daysOfMonthNoWeekends=(_event_date_t:datetime) {
toscalar(range days from startofmonth(_event_date_t) to endofmonth(_event_date_t) step 1d
| where dayofweek(days) between(1d .. 5d)
| count)
};
//
MyTable_CL
| extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(TimeGenerated)

//or with another column of MyTable like event_date_t fails too
//| extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(event_date_t)

Error:

Semantic error: '' has the following semantic error: Unresolved reference binding: 'TimeGenerated'.

For the record I pretend to add a column with the number of days without weekends in a month based on a column which is a date to use it in another calculation.

Any idea why this doesn't works?

score 3 · Answer 1 · answered May 11 '20 at 15:46

the reason this doesn't work is documented here: User-defined functions usage restrictions

specifically:

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

you should be able to achieve your intention using a join/lookup.

for example (caveat: test this actually works with your data. i 'complied' it in my head at an early morning hour):

let T = datatable(TimeGenerated:datetime)
[
    datetime(2020-02-11 11:20),
    datetime(2020-04-11 11:30),
    datetime(2020-05-12 19:20),
    datetime(2020-05-13 19:20),
    datetime(2020-04-13 19:20),
    datetime(2020-01-11 17:20),
]
;
let daysOfMonthNoWeekends =
    range dt from startofmonth(toscalar(T | summarize min(TimeGenerated))) to endofmonth(toscalar(T | summarize max(TimeGenerated))) step 1d 
    | summarize countif(dayofweek(dt) between(1d .. 5d)) by month = startofmonth(dt)
;
T
| extend month = startofmonth(TimeGenerated)
| lookup daysOfMonthNoWeekends on month
| project-away month

Passing colum as function parameter in Kusto Azure Log Analytics doesn't works

1 Answers1