{"id":91877,"date":"2021-08-16T17:00:25","date_gmt":"2021-08-16T17:00:25","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=91877"},"modified":"2021-08-13T15:37:51","modified_gmt":"2021-08-13T15:37:51","slug":"creating-functions-kusto-queries","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/creating-functions-kusto-queries\/","title":{"rendered":"Creating functions in Kusto Queries"},"content":{"rendered":"

In the previous blog, I illustrated how to create sub-queries in Kusto<\/strong>.<\/p>\n

However, sometimes we may face even more complex situations and we may need to create not only a sub-query, but a function.<\/p>\n

Another way to think about a function inside a Kusto<\/strong> query is like a parameterized sub-query.<\/p>\n

Let’s review the query from the previous blog:<\/p>\n

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

\nThis query has one problem: The Day field is numeric. This makes the result a bit more difficult to understand.<\/p>\n

Creating the Function<\/h2>\n

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.<\/p>\n

A function in Kusto<\/strong> to translate the day will be like this:<\/p>\n

let weekday = (day:int<\/span>) {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 case(day == 0, “Sun<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 1, “Mon<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 2, “Tue<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 3, “Wed<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 4, “Thu<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 5, “Fri<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 “Sat”)
\n\u00a0\u00a0\u00a0 };<\/p>\n

The function is not much different than a sub-query:<\/p>\n

1) The function has an input parameter with type defined
\n2) The function uses curly brackets
\n3) The function needs to return a value, but if we have a single calculation inside the function, it will be automatic<\/p>\n

 <\/p>\n

Using the Function<\/h2>\n

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

\nlet weekday = (day:int<\/span>) {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 case(day == 0, “Sun<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 1, “Mon<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 2, “Tue<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 3, “Wed<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 4, “Thu<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day == 5, “Fri<\/span>“,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 “Sat”)
\n\u00a0\u00a0\u00a0 };
\nlet outliers=
\n\u00a0\u00a0\u00a0 AppServiceHTTPLogs
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |summarize<\/span> Total=count<\/span>() by CIp,bin(TimeGenerated,1d)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |where<\/span> Total > 100
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |project<\/span> CIp;
\nAppServiceHTTPLogs
\n\u00a0\u00a0\u00a0 |where<\/span> CIp !in (outliers)
\n\u00a0\u00a0\u00a0 |summarize<\/span> Total=count<\/span>() by<\/span> dayofweek(TimeGenerated),Week=bin(TimeGenerated,7d)
\n\u00a0\u00a0\u00a0 |project<\/span> Day=format_timespan(Column1,’d’),Week,Total
\n\u00a0\u00a0\u00a0 |order by<\/span> Day asc
\n\u00a0\u00a0\u00a0 |project<\/span> Day=weekday(Day),Week,Total<\/p>\n","protected":false},"excerpt":{"rendered":"

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……<\/p>\n","protected":false},"author":50808,"featured_media":92096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[126250,126249,143562],"coauthors":[6810],"class_list":["post-91877","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blogs","tag-kql","tag-kusto","tag-log-analytics"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91877","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=91877"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91877\/revisions"}],"predecessor-version":[{"id":91902,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91877\/revisions\/91902"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/92096"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=91877"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=91877"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=91877"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=91877"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}