Extracting Azure Analysis Services Metrics from your Log Analytics workspace into PowerBI

We have numerous Azure Analysis Services servers running in the cloud and needed a way to have near-real time monitoring of the queries being run. This KQL query will enable you to pull all the user/query information necessary to manage these servers, into a PowerBI report.

/* 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/subscriptions/your subscription here/query", [Query=[#"query"="AzureDiagnostics | where ResourceProvider == 'MICROSOFT.ANALYSISSERVICES' and Resource in ('server1','server2') and OperationName =='QueryEnd' and NTDomainName_s == 'AzureAD' | order by StartTime_t",#"x-ms-app"="AzureFirstPBI",#"timespan"="PT1H",#"scope"="hierarchy",#"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

The highlighted section requires you to input your values. In the above example, this will monitor 2 servers. You can add more or split this into individual servers/query.