Azure Monitor: Log Analytics – Kusto Query Language – Learning Series: Get All Tables in Log Analytics Workspace

Welcome once again to my blog. I have planned to do series of posts starting today as I learn the all new powerful Kusto Query Language (KQL) and how to use it to extract valuable data out of Log Analytics Workspace. To start with, I would recommend you to bookmark this URL where you find complete set of documentation including samples, data types and functions.

First things first, I hope you all know what Azure Monitor and Log Analytics is and how to get the data into Log Analytics Workspace. You can check this out here and here for brief introduction and capabilities.

We will start with basic query to get list of all tables in a Workspace. This is very much essential as all your future work depend on extracting data from one of these tables and processing it to get meaningful results. Unfortunately, there is no direct keyword/function to enumerate the list of tables in Workspace. Schema Column in Log Analytics Workspace show only tables related to solutions you added and custom logs. So, to achieve that, technically, we must obtain all the data available in our workspace and get table name out of it which at first seems near to impossible.

Technically, search * solves the purpose along with distinct function to get unique table names, sort function to get the results ordered appropriately. Make sure you set the time range on top of query window to 1 day or 7 days or 30 days based on how frequently you think you are collecting all required data.

search *
| distinct $table
| sort by $table asc nulls last

This has returned 26 rows i.e., 26 tables in my workspace where I have written some data in last 1 week.

Alert

AlertHistory

AzureActivity

AzureDiagnostics

AzureMetrics

ComputerGroup

ConfigurationChange

ConfigurationData

Event

Heartbeat

NetworkMonitoring

Operation

Perf

ProtectionStatus

SecurityBaseline

SecurityBaselineSummary

SecurityEvent

ServiceMapComputer_CL

ServiceMapProcess_CL

Update

UpdateSummary

Usage

VMBoundPort

VMConnection

W3CIISLog

WireData

I’m sure you may have many more tables in your workspace depending on solutions and custom logs, and this will kickstart you to dive into so far, unexplored tables.