Custom aggregate function on summarize

Marco Apa 1 Reputation point
2020-11-05T17:02:37.693+00:00

Hi at all,
i want calculate a statistic mode on a colummn during summarize a table.
My CalculateMode functions that i try are:

 .create function CalculateMode(Action:int, Asset:string, Start:long, End:long) {
    Event
        | where JsonPath between (Start .. End) and IdAction == Action and IdDevice == Device
        | summarize Count = countif(isnotnull(Result) and isnotempty(Result)) by tostring(Result)
        | top 1 by Count desc
        | project ActionResult
    }

OR

.create function CalculateMode(T:(data:dynamic)) {
T
    | summarize Count = countif(isnotnull(data) and isnotempty(data)) by tostring(data)
    | top 1 by Count desc
    | project data
}

when i using first coding on summarizing:

 Event
        | summarize Result = CalculateMode(toint(IdAction), tostring(IdDevice), Start, End) by Category

Obtain this error No tabular expression statement found and
when i using second coding on summarizing:

Event
    | summarize Result = CalculateMode(Result) by Category

Obtain this error CalculateMode(): argument #1 must be a tabular expression

What can i do?
Where am i wrong?
Thanks

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
502 questions
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
    2020-11-10T22:09:57.36+00:00

    Hello @Marco Apa ,

    Please check mv-apply operator (it allows run a sub-query over data in a dynamic column):
    https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/mv-applyoperator.

    Thanks
    Himanshu
    Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.