Azure Synapse Analytics使用追踪：求Log Analytics可用KQL查询

1. 统计高频使用的视图/表 #

这个查询会提取所有查询中引用的表或视图，按使用次数排序，帮你找出最热门的数据源：

LAQueryLogs
| where TimeGenerated > ago(30d) // 可按需调整时间范围，比如7d、90d
| extend TablesReferenced = extract_all(@"['`]?([a-zA-Z0-9_]+)['`]?", 1, QueryText)
| mv-expand TablesReferenced to typeof(string)
| where TablesReferenced !in ('', 'union', 'where', 'summarize', 'extend') // 排除KQL关键字干扰
| summarize UsageCount = count() by TablesReferenced
| sort by UsageCount desc

2. 找出高频使用的用户 #

统计每个用户的查询执行次数，快速定位活跃用户或高频查询使用者：

LAQueryLogs
| where TimeGenerated > ago(30d)
| summarize QueryCount = count() by UserPrincipalName
| sort by QueryCount desc
| extend UserPrincipalName = iff(UserPrincipalName == '', '匿名用户', UserPrincipalName) // 处理无用户信息的匿名查询

3. 查看针对特定表的所有查询详情 #

替换示例中的SecurityEvent为你关注的表名，就能看到所有操作过该表的查询，包括执行用户、时间和查询内容：

LAQueryLogs
| where TimeGenerated > ago(7d)
| where QueryText contains "SecurityEvent" // 替换为目标表名
| project TimeGenerated, UserPrincipalName, QueryText, DurationMs, ResultCount
| sort by TimeGenerated desc

4. 按表统计常用查询操作 #

想知道针对每个表大家常用哪些KQL操作？这个查询会统计每个表对应的操作类型（比如筛选、聚合、关联等）的使用次数：

LAQueryLogs
| where TimeGenerated > ago(30d)
| extend TablesReferenced = extract_all(@"['`]?([a-zA-Z0-9_]+)['`]?", 1, QueryText)
| mv-expand TablesReferenced to typeof(string)
| where TablesReferenced !in ('', 'union', 'where', 'summarize', 'extend')
| extend QueryOperations = extract_all(@"(\bwhere\b|\bsummarize\b|\bjoin\b|\bextend\b|\bproject\b)", 1, QueryText)
| mv-expand QueryOperations to typeof(string)
| summarize OperationCount = count() by TablesReferenced, QueryOperations
| sort by TablesReferenced, OperationCount desc

内容的提问来源于stack exchange，提问作者Chris