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.
Search is case sensitive and the query will error, change to lower case and it works great.
Thanks Robert. Corrected.