Investigate Business Central API slowdowns and outages by using Telemetry and Azure Application Insights

MONITOR API ON SAAS AND PREMISE ENVIRONMENT

Telemetry is a magic to tool monitor Business Central. It allows to investigate API slowdown reports and claims of users.

Users may have the incorrect perception that SaaS and Premise environments have reliability problems.

Fortunately, Telemetry is a good solution to make it clear whether a tenant is instead stable.

Telemetry allows to have an indisputable tool to monitor HTTP error occurred during API requests (eg: errors 429, 500, 404 and so on).

The final report of this tutorial outputs, for each API requests, all the information needed to investigate slowdown and outages.

These telemetry fields are all it needs to check health and status: http status code, the execution time, the sql rows and executes and the endpoint.

It’s just disappointing the fact only BC16+ (thx Kennie Nybo Pontoppidan corrected my mistake about the version) supports telemetry on Web Services and API, as documented:

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/telemetry-webservices-access-key-trace

STEP 1: CREATE AZURE APPLICATION INSIGHTS

Log to Azure Portal and create a new “Application Insights”. This operation is just as simple as in pictures.

Please check the “Istrumentation Key” and the “Connection String” values because they are needed when configuring Telemetry on Business Central tenants.

ENABLE TELEMETRY ON BUSINESS CENTRAL SAAS

To enable Telemetry I access the Admin Center of Business Central Saas. The admin center is accessible from this url:

https://businesscentral.dynamics.com/{your_tenant_id}/admin

Then select the environment

And copy the “Application Insights Key” inside the textbox.

ENABLE TELEMETRY ON BUSINESS CENTRAL ON PREMISE

For the On Premise version of Business Central the “Application Insights Instrumentation Key” is configurable from the “Administration Center” panel.

ACCESS TELEMETRY DATA

Azure Portal allows to read and query the data but PowerShell looks like a powerful too.

QUERY TELEMETRY DATA IN AZURE PORTAL

By using Log Analytics and KQL queries directly from the Azure Portal it is possibile to filter to search specific events.

Since the investigation involves HTTP errors occurred during HTTP API request all it is necessary is to query the proper KQL

traces
| where operation_Name == ‘Web Services Call’
and message contains “(Api)”

The output is the list of records of API calls:

By expanding a row we can expand the “customDimensions” tag.

Under customDimensions, finally, is shown the HTTP result:

Since customDimensions is a “dynamic type” the KQL query is a little tricky:

 traces 
| extend httpStatusCode = parsejson(tostring(parsejson(tostring(customDimensions.httpStatusCode))))
| extend serverExecutionTime = parsejson(tostring(parsejson(tostring(customDimensions.serverExecutionTime))))
| extend serverExecutionTime = parsejson(tostring(parsejson(tostring(customDimensions.serverExecutionTime))))
| extend totalTime = parsejson(tostring(parsejson(tostring(customDimensions.totalTime))))
| extend category = parsejson(tostring(parsejson(tostring(customDimensions.category))))
| extend sqlRowsRead = parsejson(tostring(parsejson(tostring(customDimensions.sqlRowsRead))))
| extend sqlExecutes = parsejson(tostring(parsejson(tostring(customDimensions.sqlExecutes))))
| extend endpoint = parsejson(tostring(parsejson(tostring(customDimensions.endpoint))))
| where  operation_Name == 'Web Services Call' 
and message  contains "(Api)"
| project httpStatusCode, serverExecutionTime , totalTime , category , sqlRowsRead , sqlExecutes , endpoint , operation_Name

The final result is just what we need to investigate on HTTP outages:

HOW TO QUERY TELEMETRY DATA WITH POWERSHELL

On the “API Access” blade click the “Create API Key” button. Copy the generated and saved it because it will never ever be accessible again. The “Application ID” will be used to for PowerShell.

I find it easy to test with Insomia. The request is:

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

The query language:

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

Here is my PowerShell script:


$key = "{APP_KEY_FROM_AZURE}"
$appId = "{APP_ID_FROM_AZURE}"

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

$filename = "{your_path}/{your_file}.kql"
$queryText = Get-Content $filename
$Query=[uri]::EscapeUriString("?query=$queryText")


$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

$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
}

$logData

The KQL script file is the same as the previous

 traces 
| extend httpStatusCode = parsejson(tostring(parsejson(tostring(customDimensions.httpStatusCode))))
| extend serverExecutionTime = parsejson(tostring(parsejson(tostring(customDimensions.serverExecutionTime))))
| extend serverExecutionTime = parsejson(tostring(parsejson(tostring(customDimensions.serverExecutionTime))))
| extend totalTime = parsejson(tostring(parsejson(tostring(customDimensions.totalTime))))
| extend category = parsejson(tostring(parsejson(tostring(customDimensions.category))))
| extend sqlRowsRead = parsejson(tostring(parsejson(tostring(customDimensions.sqlRowsRead))))
| extend sqlExecutes = parsejson(tostring(parsejson(tostring(customDimensions.sqlExecutes))))
| extend endpoint = parsejson(tostring(parsejson(tostring(customDimensions.endpoint))))
| where  operation_Name == 'Web Services Call' 
and message  contains "(Api)"
| project httpStatusCode, serverExecutionTime , totalTime , category , sqlRowsRead , sqlExecutes , endpoint , operation_Name
| summarize OpNameCount=count() by tostring(httpStatusCode)

PowerShell allows to create your own logic around all the telemetry data and I find it very useful to give a meaning to telemetry data.

REFERENCES

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/telemetry-enable-application-insights

“Dynamics 365 Business Central: handling telemetry with Azure Application Insights” and “Using Powershell to retrieve your Dynamics 365 Business Central telemetry” by Stefano Demiliani:

Query REST API:

https://dev.applicationinsights.io/documentation/Using-the-API/Query

KQL Query language reference:

https://docs.microsoft.com/it-it/azure/data-explorer/kql-quick-reference

NEXT ARTICLE

As Microsoft suggested there are either some other powerful tools and ways to monitor and alert.

We added jupyter notebook troubleshooting guides for exactly this scenario. Get it here: https://github.com/microsoft/BCTech/tree/master/samples/AppInsights/TroubleShootingGuides

We also added a lot of KQL samples here: https://github.com/microsoft/BCTech/tree/master/samples/AppInsights/KQL/Queries Keep up the great work uptaking hashtag#msdyn365bc telemetry. Have you considered adding an alert in Azure Monitor? Read more here https://github.com/microsoft/BCTech/tree/master/samples/AppInsights/Alerts (also with a few sample KQL queries for alerts)

LAST UPDATED

17th of March, 2021

One thought on “Investigate Business Central API slowdowns and outages by using Telemetry and Azure Application Insights

  1. Great post.

    Just a few clarifications:
    1) You write “It’s just disappointing the fact only BC17+ supports telemetry on Web Services and API”. You can track incoming web service requests already from 16.0 and with http headers from 16.3: https://github.com/microsoft/BCTech/tree/master/samples/AppInsights/KQL#signal-overview
    2) We added jupyter notebook troubleshooting guides for exactly this scenario. Get it here: https://github.com/microsoft/BCTech/tree/master/samples/AppInsights/TroubleShootingGuides
    3) We also added a lot of KQL samples here: https://github.com/microsoft/BCTech/tree/master/samples/AppInsights/KQL/Queries

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s