# 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/](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/](https://api.loganalytics.io/v1/subscriptions//query)**<mark>your subscription here</mark>**[/query](https://api.loganalytics.io/v1/subscriptions//query)", \[Query=\[#"query"="AzureDiagnostics | where ResourceProvider == 'MICROSOFT.ANALYSISSERVICES' and Resource in ('*<mark>server1','server2')</mark>* 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) =&gt; { 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.