{"id":5025,"date":"2022-08-29T08:30:44","date_gmt":"2022-08-29T07:30:44","guid":{"rendered":"https:\/\/ricardomoinhos.com\/?p=5025"},"modified":"2022-09-09T23:11:05","modified_gmt":"2022-09-09T22:11:05","slug":"exploring-telemetry-in-excel-and-using-a-template-file-2","status":"publish","type":"post","link":"https:\/\/ricardomoinhos.com\/pt\/exploring-telemetry-in-excel-and-using-a-template-file-2\/","title":{"rendered":"Business Central telemetry – Exploring telemetry in Excel and using a template file"},"content":{"rendered":"

Two posts in a row.<\/p>\n

I’ve just shared a post on how to give access to an user that belongs to a different Azure Active Directory to somehow workaround the limitation of only being able to setup one Application Insights in the same environment.<\/p>\n

At LS Retail (an Aptos Company) I’ve been using telemetry to troubleshoot upgrades and performance issues on a SaaS environment and we need to have access to customer telemetry.<\/p>\n

Microsoft has created an excellent Power BI app that give us a lot of insights. Start using it now: BCTech\/samples\/AppInsights\/PowerBI\/Reports\/AppSource at master \u00b7 microsoft\/BCTech (github.com)<\/a><\/p>\n

But if you need some more advanced features like exploring telemetry data using pivot tables in Excel, for example, then take a look at this post.<\/p>\n

I’ve created a sort of template Excel file where I included Power Queries to get the telemetry data from Azure Application Insights.<\/p>\n

And bear in mind that I want it to be dynamic. I want to change some paramenters like Application Insights ID, the Azure AD ID or the Environment Name in a easy and fast way.<\/p>\n

Below it’s a step-by-step guide on how I’ve created an Excel spreadsheet and I’ve included the Long Running AL queries telemetry.<\/p>\n

 <\/p>\n

First, open the BCTech repo by Microsoft. There’s a KQL query example for pretty much all the telemetry events available. I chose the Long Running AL queries KQL query for this example.<\/p>\n

https:\/\/github.com\/microsoft\/BCTech\/blob\/master\/samples\/AppInsights\/KQL\/Queries\/ExampleQueriesForEachArea\/LongRunningAL.kql<\/p>\n

Copy the content to be used in Application Insights Logs.<\/p>\n

\"\"<\/p>\n

Open Logs<\/strong> under the Application Insights<\/strong> and paste the query:<\/p>\n

\"\"<\/p>\n

You must run the query once to be able to use the Export<\/strong> button:<\/p>\n

\"\"<\/p>\n

Choose the Export to Power BI (M query) <\/strong>to export the query ready to be used in Power Queries, in Excel. A text file is exported.<\/p>\n

\"\"<\/p>\n

Next, open a new or an existing Excel file and go to Data –<\/strong>\u00a0Get Data – Launch Power Query Editor…<\/strong><\/p>\n

\"\"<\/p>\n

Create a new empty query:<\/p>\n

\"\"<\/p>\n

Edit the query using the Advanced Editor<\/strong>.<\/p>\n

\"\"<\/p>\n

And replace the content of the query with the PowerBI query file downloaded from Application Insights.<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

Right click and rename the query to a more user friendly name:<\/p>\n

\"\"<\/p>\n

Close and load the results to Excel.<\/p>\n

\"\"<\/p>\n

This is how it looks in Excel<\/p>\n

\"\"<\/p>\n

Now you have to repeat this to as many queries as you want\/need.<\/p>\n

I told you earlier that I wanted this to be dynamic. I want to be able\u00a0to change the Application Insights ID, the Azure AD ID or the Environment Name directly in a Excel sheet. Let’s look at this tricky part.<\/p>\n

Create a new sheet and name it Parameters<\/strong>, for example.<\/p>\n

Next, create a table with the parameters to be defined:<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

Time to give a friendly name to this table.<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

Next we need to link these fields with parameters created in Power Query.<\/p>\n

To access the Power Query, go to LongRunningAL sheet, access the Queries & Connections<\/strong>, right click the query and click on Edit.<\/strong><\/p>\n

\"\"<\/p>\n

Open the Advanced Editor<\/strong><\/p>\n

\"\"<\/p>\n

Now this is the trickiest part. We need to change the query to include the parameters in the table.<\/p>\n

So, this is the beginning of the query before:<\/p>\n

let AnalyticsQuery =
\nlet Source = Json.Document(Web.Contents(“https:\/\/api.applicationinsights.io\/v1\/apps\/13c32414-7067-xxxx-xxxx-xxxxxxxxxxxx\/query”,
\n[Query=[#”query”=”
\ntraces
\n| where timestamp > ago(60d)
\nand customDimensions.eventId == ‘RT0018’
\nand customDimensions.alObjectId > 0<\/p>\n

| extend eventId = customDimensions.eventId<\/p><\/blockquote>\n

 <\/p>\n

And here’s the beginning of the query after:<\/p>\n

let
\nSource = Excel.CurrentWorkbook(){[Name=”PowerQueryParameters”]}[Content],
\n#”Changed Type” = Table.TransformColumnTypes(Source,{{“AAD ID”, type any}}),
\n#”Application Insights ID” = #”Changed Type”{0}[Application Insights ID],
\n#”AAD ID” = #”Changed Type”{0}[AAD ID],
\n#”Environment Name” = #”Changed Type”{0}[Environment Name],
\n#”Days of History” = #”Changed Type”{0}[Days of History],<\/p>\n

AnalyticsQuery =
\nlet Source = Json.Document(Web.Contents(“https:\/\/api.applicationinsights.io\/v1\/apps\/” & #”Application Insights ID” & “\/query”,
\n[Query=[#”query”=”
\ntraces
\n| where 1 == 1″
\n& (if #”AAD ID” = null then “” else ” and customDimensions.aadTenantId == “”” & #”AAD ID” & “”””)
\n& (if #”Environment Name” = null then “” else ” and customDimensions.environmentName == “”” & #”Environment Name” & “”””)
\n& (if #”Days of History” = null then “” else ” and timestamp > ago(” & Number.ToText(#”Days of History”) & “d)”)
\n& ” and customDimensions.eventId == ‘RT0018’
\nand customDimensions.alObjectId > 0<\/p>\n

| extend eventId = customDimensions.eventId<\/p><\/blockquote>\n

Just to spot the differences:<\/p>\n

\"\"<\/p>\n

Just Close & Load<\/strong> the Power Query Editor after you’ve done the changes.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

That’s it.<\/strong><\/span><\/p>\n

Now you just need to change the parameters and refresh the sheets. If you have multiple sheets, it’s a good idea to use the Refresh All<\/strong> button, under Data<\/strong> to refresh them all.<\/p>\n

 <\/p>\n

Example 1<\/strong><\/p>\n

Parameters<\/p>\n

10 days of history<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

Example 2<\/strong><\/p>\n

Parameters<\/p>\n

1 day of history (will show the last 24 hours)<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

Please note that\u00a0I’ve created another Excel file (out of scope of this article) where I’ve included all the Pivot tables I need to analyse all connected to the Excel file with the queries so that I just need to update the parameters, hit refresh and all the information will be updated on both files, just like that.<\/p>\n

If you don’t want to start from scratch, you can download this template file that you can use for testing or adding new telemetry events.<\/p>\n

<\/div>
(English) Telemetry Template – App Insights Queries<\/div><\/div>
<\/div>
<\/div>
Version: <\/span>v1.0<\/span><\/div>
Published: <\/span>agosto 29, 2022<\/span><\/div>
Download Now!<\/a><\/span>2005<\/span> Downloads<\/span><\/span><\/div><\/div>
<\/div>\n

The following events have been included here:<\/p>\n