Creating functions in Kusto Queries

In the previous blog, I illustrated how to create sub-queries in Kusto.

However, sometimes we may face even more complex situations and we may need to create not only a sub-query, but a function.

Another way to think about a function inside a Kusto query is like a parameterized sub-query.

Let’s review the query from the previous blog:

let outliers=
    AppServiceHTTPLogs
        |summarize Total=count() by CIp,bin(TimeGenerated,1d)
        |where Total > 100
        |project CIp;
AppServiceHTTPLogs
    |where CIp !in (outliers)
    |summarize Total=count() by dayofweek(TimeGenerated),Week=bin(TimeGenerated,7d)
    |project Day=format_timespan(Column1,’d’),Week,Total
    |order by Day asc
    |project Day,Week,Total

This query has one problem: The Day field is numeric. This makes the result a bit more difficult to understand.

Creating the Function

As developers, create a function to translate the day to the name of the day in the week is something natural. There may be other way to solve this problem, but in my opinion this keep the query more organized as well.

A function in Kusto to translate the day will be like this:

let weekday = (day:int) {
        case(day == 0, “Sun“,
        day == 1, “Mon“,
        day == 2, “Tue“,
        day == 3, “Wed“,
        day == 4, “Thu“,
        day == 5, “Fri“,
        “Sat”)
    };

The function is not much different than a sub-query:

1) The function has an input parameter with type defined
2) The function uses curly brackets
3) The function needs to return a value, but if we have a single calculation inside the function, it will be automatic

 

Using the Function

The bad news: There is no function library or anything similar in Log Analytics. The function needs to be together the query. Our final query using the function will be like this:

let weekday = (day:int) {
        case(day == 0, “Sun“,
        day == 1, “Mon“,
        day == 2, “Tue“,
        day == 3, “Wed“,
        day == 4, “Thu“,
        day == 5, “Fri“,
        “Sat”)
    };
let outliers=
    AppServiceHTTPLogs
        |summarize Total=count() by CIp,bin(TimeGenerated,1d)
        |where Total > 100
        |project CIp;
AppServiceHTTPLogs
    |where CIp !in (outliers)
    |summarize Total=count() by dayofweek(TimeGenerated),Week=bin(TimeGenerated,7d)
    |project Day=format_timespan(Column1,’d’),Week,Total
    |order by Day asc
    |project Day=weekday(Day),Week,Total

The post Creating functions in Kusto Queries appeared first on Simple Talk.

from Simple Talk https://ift.tt/3mbWaWe
via