Using Powershell to retrieve your Dynamics 365 Business Central telemetry

Telemetry, telemetry, telemetry… this is an hot topic in your developer’s and admin’s life in the cloud. We have talked a lot about using Application Insights for handling the telemetry data of your Dynamics 365 Business Central tenant running in the cloud and in the last events Microsoft, me and others (like my friend Duilio) explained how to use some important tools for querying your telemetry data, like:

• Log Analytics and KQL queries directly from the Azure Portal
• Jupyter Notebooks with Azure Data Studio and KQL queries directly from your desktop
• Power BI reports (see some samples here)

I don’t know what tool do you prefer, but sometimes I’m quite lazy and I have the needs to perform on-demand KQL queries for retrieving telemetry data without connecting to the Azure Portal and maybe to retrieve telemetry data with a query on a scheduled timeline. This is where the old and good Powershell, together with the Application Insights APIs, comes in help.

Yes, you can directly use Powershell to quickly retrieve your telemetry data from Application Insights and also to perform a KQL query, without using other tools. This is extremely quick and useful for on-demand tasks or for scheduled operations.

How to do that?

At first, from your Application Insights instance you need to select the API Access blade (from the menu on the left) and here you need to:

• retrieve the Application ID
• create an API key and retrieve it

You can create an API Key as follows:

Remember to copy the generated key, because when you close the window you will not be able to see the key anymore.

Now we’re ready to create a Powershell script that uses the Application Insights REST APIs. More specifically, I will use the Query REST API that allows you to execute the same queries you run in Application Insights powerful Analytics search experience, so that you can consume the results programmatically. 

This API permits you to execute a query on your telemetry data by using the following endpoint:

GET https://api.applicationinsights.io/v1/apps/{app-id}/query?query=

like for example:

?query=traces | where timestamp > ago(30d)" 

To call the API, you need to use the application ID in the URL and you need to pass the API Key in the request header as an X-Api-Key parameter.

This is the Powershell script that sends a query to Application Insights and reads the response from the telemetry data:

$key = "YOUR_API_KEY"
$appId = "YOUR_APPLICATION_ID"

$Query=[uri]::EscapeUriString("?query=traces | where timestamp > ago(30d)")

$headers = @{ "X-Api-Key" = $key; "Content-Type" = "application/json" }

$response = Invoke-WebRequest -uri  "https://api.applicationinsights.io/v1/apps/$appId/query$Query" -Headers $headers -Method Get

$json = ConvertFrom-Json $response.Content

Now the $json variable contains the JSON response of the query. You can format the $json object to a PSObject by using the following piece of code:

$headerRow = $null
$headerRow = $json.tables.columns | Select-Object name
$columnsCount = $headerRow.Count
$logData = @()
foreach ($row in $json.tables.rows) {
   $data = new-object PSObject
   for ($i = 0; $i -lt $columnsCount; $i++) {
      $data | add-member -membertype NoteProperty -name $headerRow[$i].name -value         $row[$i]
   }
   $logData += $data
   $data = $null
}

Now $logData is a more readable object that contains your log and that you can query.

Obviously, you can also direclty read a file that contains a previously created KUSTO query (KQL) and then execute that query from Powershell (this is what normally I do). You can do that simply by using the following piece of Powershell:

$filename = "C:\Users\StefanoDemiliani\KQL\KQLTest.txt"
$queryText = Get-Content $filename
$Query=[uri]::EscapeUriString("?query=$queryText")

and use that with the above script.

As an example, I have a file with the following KQL query:

traces
| summarize CountByOperationName=count() by operation_Name
| top 10 by CountByOperationName

and this is the output directly from a Powershell prompt if I read that file and execute it:

I love the Azure Portal, but sometimes this is more quick and easy 🙂