none
User-defined tabular function that takes datetime parameter RRS feed

  • Question

  • I need to define a Kusto tabular function that takes a datetime that will be be called using the name of an existing datetime column from a customMetrics Application Insights table (its timestamp column.)

    The documentation indicates this:

    let MyFilter = (T:(x:long), v:long) {
      T | where x >= v
    };

    So, I do this, since I only have a single so-called 'tabular' parameter.

    let DateOnly = (customMetrics:(x:datetime)) {
      format_datetime(x,'yyyy-MM-dd')
    };
    customMetrics
    | summarize value = count() by DateOnly(timestamp), name
    | where name == "no_way_jose_gauge" 


    The SYNTAX ERROR message I get is:

    '' operator: Failed to resolve scalar expression named 'x'  

    I've tried numerous other trial-and-error permutations on the syntax, but nothing seems to work.

    I need to summarize by a single date for each day, so I'm beginning by reducing 100's of records for each day to one record that is a count of the "value" column for each day.

    This code let's me do that, but I thought I could write a function to  save an extra call to format_datetime.

    customMetrics 
    | summarize value = count() by format_datetime(timestamp,'yyyy-MM-dd'), name, dateonly = format_datetime(timestamp,'yyyy-MM-dd')
    | where name == "no_way_jose_gauge"
    | order by dateonly asc



    A similar unanswered question involving datetime parameters is:

    How to pass arguments in custom saved function (Log analytics)


    Thanks


    Monday, May 18, 2020 3:50 PM

All replies

  • So I think the main issue here is that you are using a scalar function, not a tabular one. That's why the error message says it can't resolve the scalar expression.

    Tablular Function:

    Takes a table (and possibly some values) and returns a table.

    If you wanted to use your example with a tabular function, it would look something like this:

    let DateOnly = (T: (name: string, timestamp: datetime)) {
        T | summarize value = count() by format_datetime(timestamp,'yyyy-MM-dd'), name
    };
    DateOnly(customMetrics)
    | where name == "no_way_jose_gauge"
    | order by timestamp asc

    Scalar Function:

    Takes one or more values and returns a value. This is usually what you want if you are working on something inside the body of a query statement.

    This is the correct syntax for your query:

    let DateOnly = (x:datetime) {
      format_datetime(x,'yyyy-MM-dd')
    };
    customMetrics
    | summarize value = count() by DateOnly(timestamp), name, dateonly = DateOnly(timestamp)
    | where name == "no_way_jose_gauge"
    | order by dateonly asc

    Alternative - Use Extend

    If you don't want to use custom functions but only want to use the format function once, the extend operator is a good alternative. Pairing it with project lets you bring back just the columns you wanted.

    customMetrics
    | extend dateonly=format_datetime(timestamp,'yyyy-MM-dd')
    | summarize value = count() by dateonly, name
    | where name == "no_way_jose_gauge"
    | project dateonly, name, value
    | order by dateonly asc

    Friday, May 22, 2020 7:47 PM
    Moderator