集成 Log Analytics 和 ExcelIntegrate Log Analytics and Excel

可以使用 M 查询和 Log Analytics API 来集成 Azure Monitor Log Analytics 和 Microsoft Excel。You can integrate Azure Monitor Log Analytics and Microsoft Excel using M query and the Log Analytics API. 进行此集成后,你可以将 500000 条记录发送到 Excel。This integration allows you to send 500,000 records to Excel.

备注

由于 Excel 是本地客户端应用程序,因此本地硬件和软件限制会影响其性能以及其处理大型数据集的能力。Because Excel is a local client application, local hardware and software limitations impact it's performance and ability to process large sets of data.

在 Log Analytics 中创建 M 查询Create your M query in Log Analytics

  1. 像往常一样,在 Log analytics 中创建和运行查询。Create and run your query in Log analytics as you normally would. 如果在用户界面中遇到 10000 条记录的限制,请不要担心。Don’t worry if you hit the 10,000 records limitation in the user interface. 建议使用相对日期(如“ago”函数或 UI 时间选取器),以便 Excel 刷新正确的数据集。We recommend you use relative dates - like the ‘ago’ function or the UI time picker - so Excel refreshes the right set of data.

  2. 导出查询 - 对查询及其结果感到满意后,使用 Log Analytics 的“导出”菜单下的“导出到 Power BI (M 查询)”菜单选项,将查询导出到 M:Export Query - Once you are happy with the query and its results, export the query to M using Log Analytics Export to Power BI (M query) menu choice under the Export menu:

包含数据和导出选项的 Log Analytics 查询

如果选择此选项,则会下载一个 .txt 文件,其中包含可在 Excel 中使用的 M 代码。Choosing this option downloads a .txt file containing the M code you can use in Excel.

上面所示的查询将导出以下 M 代码。The query shown above exports the following M code. 下面是为我们的示例中的查询导出的 M 代码示例:Here’s an example of the M code exported for the query in our example:

/*
The exported Power Query Formula Language (M Language ) can be used with Power Query in Excel
and Power BI Desktop.
For Power BI Desktop follow the instructions below: 
1) Download Power BI Desktop from https://powerbi.microsoft.com/desktop/
2) In Power BI Desktop select: 'Get Data' -> 'Blank Query'->'Advanced Query Editor'
3) Paste the M Language script into the Advanced Query Editor and select 'Done'
*/

let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.loganalytics.io/v1/workspaces/ddcfc599-cae0-48ee-9026-fffffffffffff/query", 
[Query=[#"query"="

Heartbeat 
| summarize dcount(ComputerIP) by bin(TimeGenerated, 1h)    
| render timechart
",#"x-ms-app"="OmsAnalyticsPBI",#"timespan"="P1D",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" }, 
{ 
{ "string",   Text.Type },
{ "int",      Int32.Type },
{ "long",     Int64.Type },
{ "real",     Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool",     Logical.Type },
{ "guid",     Text.Type },
{ "dynamic",  Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]), 
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in AnalyticsQuery

将查询连接到 ExcelConnect Query to Excel

若要导入查询,请执行以下操作:To import the query.

  1. 打开 Microsoft Excel。Open Microsoft Excel.

  2. 在功能区中,转到“数据”菜单。In the ribbon, go to the Data menu. 选择“获取数据”。Select get data. 在“从其他源”中,选择“空查询”:From other sources, select blank query:

    Excel 中用于从空查询进行导入的选项

  3. 在 Power Query 窗口中,选择“高级编辑器”:In the Power query window select Advanced editor:

    Excel 高级查询编辑器

  4. 将高级编辑器中的文本替换为从 Log Analytics 导出的查询:Replace the text in the advanced editor with the query exported from Log Analytics:

    创建空白查询

  5. 选择“完成”,然后选择“加载并关闭”。Select Done, and then Load and close. Excel 将使用 Log Analytics API 执行查询,并且随后会显示结果集。Excel executes the query using the Log analytics API and the result set then shown.

    Excel 中的查询结果

刷新数据Refreshing data

可以直接从 Excel 中刷新数据。You can refresh your data directly from Excel. 在 Excel 功能区中的“数据”菜单组中,选择“刷新”按钮。In the Data menu group in the Excel ribbon, select the Refresh button.

后续步骤Next steps

有关 Excel 与外部数据源的集成的详细信息,请参阅从外部数据源导入数据 (Power Query)For more information about Excel’s integrations with external data sources, see Import data from external data sources (Power Query)