Is there a way to Query all Table Schemas to count How many Columns every Table in Sentinel has using KQL

Question

I am Trying to return a list of tables where they have more than a certain amount of columns, get schema works but it would be a painful task to run it for every table. The Table name is also not maintained when you run getSchema so I tried to union all the tables but you cannot distinguish which fields belong to which table.

I am thinking a logic app is probably the best solution to just loop over the list of tables and call a function with get schema for everyone and just output that in a report.

Any other ideas?

Answer 1

Hi @Andrew Ryan , maybe you can try something like this?

let tables = materialize(database('your-database').list_tables());
tables
| project TableName = tostring(name)
| join kind=leftouter (
    tables
    | mv-expand schema=extract_all(@"\[(\w+)\]", tostring(schema))
    | project TableName = tostring(name), ColumnName = tostring(schema)
) on TableName
| summarize ColumnCount = count(ColumnName) by TableName

This retrieves a list of all tables in your database, then joins it with a list of all columns in each table. Then it summarizes the results to count the number of columns in each table.

If this doesn't work or is a little too convoluted, then I do believe that a logic app is a good way to go.

Please let me know if you have any questions and I can help you further.

If this answer helps you please mark "Accept Answer" so other users can reference it.

Thank you,

James