如何将Log Analytics中已存储的查询作为数据源接入Excel？

解决方案 #

可以通过动态拉取savedSearch配置+实时执行查询的逻辑实现需求，不需要把查询逻辑固化在Excel配置里，每次刷新都会自动同步最新的存储版本查询逻辑和对应数据。

核心思路 #

将两次请求逻辑封装在Excel的Power Query中：第一次请求拉取你部署的savedSearch的最新查询语句，第二次用拉到的最新语句请求Log Analytics接口获取数据，两次请求在每次刷新时都会执行，完全和存储端的savedSearch版本同步。

具体操作步骤 #

• 提前准备4个必要参数：Log Analytics工作区对应的订阅ID、资源组名称、工作区名称、目标savedSearch的资源ID（部署时定义的savedSearches资源名，也可在Log Analytics工作区「保存的查询」面板中查询获取）
• 打开Excel，依次点击「数据」-「获取数据」-「启动Power Query编辑器」，新建空白查询
• 打开查询的「高级编辑器」，替换为以下M脚本，替换尖括号内的自有参数即可：

// 自定义基础参数
subscriptionId = "<你的订阅ID>"
resourceGroupName = "<你的资源组名称>"
workspaceName = "<你的Log Analytics工作区名称>"
targetSavedSearchId = "<目标savedSearch的资源ID>"
savedSearchApiVersion = "2020-08-01"
logQueryApiVersion = "v1"

// 拉取存储端最新的savedSearch查询逻辑
savedSearchRequestUri = "https://management.azure.com/subscriptions/" & subscriptionId & "/resourceGroups/" & resourceGroupName & "/providers/Microsoft.OperationalInsights/workspaces/" & workspaceName & "/savedSearches/" & targetSavedSearchId & "?api-version=" & savedSearchApiVersion
savedSearchResult = Json.Document(Web.Contents(savedSearchRequestUri))
latestQueryContent = savedSearchResult[properties][query]

// 用最新查询逻辑请求拉取数据
logQueryRequestUri = "https://api.loganalytics.io/" & logQueryApiVersion & "/workspaces/" & workspaceName & "/query"
postBody = Text.FromBinary(Json.FromValue([query = latestQueryContent]))
queryResult = Json.Document(Web.Contents(logQueryRequestUri, [
    Headers = [ #"Content-Type" = "application/json" ],
    Content = postBody
]))

// 解析接口返回结果为标准表格
columns = queryResult[tables]{0}[columns][name]
rows = queryResult[tables]{0}[rows]
finalTable = Table.FromRows(rows, columns)

• 第一次运行查询时，根据Power Query提示选择「组织账户」登录方式，用有对应Log Analytics访问权限的Azure账号完成授权
• 保存查询并加载到Excel工作表即可。

效果验证 #

你重新部署更新savedSearch的查询逻辑后，直接点击Excel「数据」选项卡下的「全部刷新」，就能自动拉取到最新版本查询对应的结果，无需修改Excel内的任何配置。

注意：用于授权的Azure账号需要至少拥有Log Analytics工作区的「Log Analytics读者」权限，以及savedSearch资源的读取权限。

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