Search code examples
azure-data-explorerkqlkusto-explorer

KQL: How to get max(strlen(colname)) for all (string type) columns in general?

I have a table with various string type columns. I want a "summary" of the form (colname | maxcollength).

I see the getschema function gets me the columns with types, but I have no idea how to leverage that to pull maxcollength without explicitly referencing each column. I'm looking for a general (no explicit names in the query) way to achieve this, which will work OOB against any table it runs against.

In sql I would think of this as a dynamic sql use case.

How would one do it in kql?

Solution

  • You can run this query to generate a query that will produce the results you need:

    let TableName = "MyTable";
let TimeFilter = "where Timestamp > ago(1d)";
table(TableName)
| getschema
| where ColumnType == "string"
| summarize Temp = array_strcat(make_list(strcat("max(strlen(", ColumnName, "))")), ", ")
| project strcat(TableName, " | ", TimeFilter, " | summarize ", Temp)

    Just update the first two lines, where you set the table name, and the desired time filter.

    As a result, you'll get a query that looks like this:

    MyTable
| where LastUpdated > ago(1d)
| summarize max(strlen(MyStringColumn)), max(strlen(AnotherStringColumn)), ...

    This is exactly the query you need to run to get the desired result.