<jupyter_start><jupyter_text>Dynamics 365 Business Central Troubleshooting Guide (TSG) - AppSource Submission Results (SaaS)This notebook contains Kusto queries that can help analyzing the results of the technical validation of your AppSource submission.NB! The signal used in this notebook is only available starting from October 2021 when the fully automated technical validation was released.  1\. Get setup: Load up Python libraries and connect to Application InsightsFirst you need to set the notebook Kernel to Python3, load the KQLmagic module (did you install it?) and connect to your Application Insights resource (get appid and appkey from the API access page in the Application Insights portal)<jupyter_code># load the KQLmagic module
%reload_ext Kqlmagic
# Connect to the Application Insights API
%kql appinsights://appid='<add app id from the Application Insights portal>';appkey='<add API key from the Application Insights portal>'<jupyter_output><empty_output><jupyter_text>2\. Define filtersThis workbook is designed for analyzing a single submission. Please provide a value for the validationRequestId. This ID is provided in Partner Center UI if your submission has failed at the automated validation step.<jupyter_code># Validation Request Id
validationRequestId = "<add validation request ID>"

# Date filters for the analysis
# use YYYY-MM-DD format for the dates (ISO 8601)
startDate = "2021-10-01"
endDate = "2021-12-31"

print("Using these parameters for the analysis:")
print("----------------------------------------")
print("validationRequestId " + validationRequestId)
print("startDate           " + startDate)
print("endDate             " + endDate)<jupyter_output><empty_output><jupyter_text>Analyze the technical validation resultsNow you can run Kusto queries to look for your validation results.This notebooks allows you to find:- which extensions have been validated - which country/regions have been validated- which releases of Business Central have been validated- which diagnostics have been reported during the validation- which baseline extensions have been used during the validation- which dependency extensions have been used during the validationEither click **Run All** above to run all sections, or scroll down to the type of analysis you want to do and manually run queries.  Documentation of the AppSource Submission Signals: [https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/telemetry-appsource-submission-validation-trace](https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/telemetry-appsource-submission-validation-trace)  Technical Validation ProcessAs part of the technical validation, all extensions in your submission are validated for all the country/regions and releases targeted.For each combinaison of target country/region and release, your extensions are compiled with the AL compiler and the AppSourceCop analyzer against the dependencies available for the country/region. Additional checks are also performed by the validation service itself (for instance, verifying that the runtime version in the manifest of the extensions is compatible with the releases targeted by your submission).Documentation of the Technical Validation Checklist: [https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-checklist-submission](https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-checklist-submission)Documentation of the AppSourceCop code analyzer: [https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/analyzers/appsourcecop](https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/analyzers/appsourcecop)  Extensions validated for your submissionThe list of extensions validated for your submission is specified when creating the offer in Partner Center.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0028'
| project extensions = customDimensions.extensions<jupyter_output><empty_output><jupyter_text>If the list of extensions does not match your expectations, verify the files you have uploaded on the tab 'Configuration' of your offer in Partner Center.  Countries/regions validated for your submissionThe list of countries/regions validated for your submission is specified when creating the offer in Partner Center.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0028'
| project countryRegions = customDimensions.countryRegions<jupyter_output><empty_output><jupyter_text>If your extension is already available for some countries, it won't be validated for these countries.    The query below shows you for which country regions each extension was validated.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0032'
| project countryRegion = customDimensions.countryRegion
, extensionId = tostring(customDimensions.extensionId)
, extensionName = tostring(customDimensions.extensionName)
, extensionPublisher = tostring(customDimensions.extensionPublisher)
, extensionVersion = tostring(customDimensions.extensionVersion)
| summarize countryRegions = make_set(countryRegion) by extensionId, extensionName, extensionPublisher, extensionVersion
| project extensionId, extensionName, extensionPublisher, extensionVersion, countryRegions
| order by extensionName asc<jupyter_output><empty_output><jupyter_text>If the list of country/regions does not match your expectations, verify the country/regions that you have selected under 'Markets' in the tab 'Availability' of your offer in Partner Center.  Releases validated for your submissionThe list of releases targeted by your submission is computed based on the manifests (app.json) of the extensions in your submission.Read more about the computation of the releases targeted by your submission here: [hhttps://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-checklist-submission](https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-checklist-submissionversions)<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0028'
| project versions = customDimensions.versions<jupyter_output><empty_output><jupyter_text>If the list of releases does not match your expectations, verify the manifest (app.json) of the extensions that you have uploaded in the 'Configuration' tab of your offer in Partner Center.  Result of the submissionIf you see a signal with the eventId **LC0029**, then your submission **passed** the technical validation.If you see a signal with the eventId **LC0035**, then your submission **failed** the technical validation.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId in ("LC0029", "LC0035")
| project validationResult = case(customDimensions.eventId == "LC0029", "The technical validation of your offer succeeded!",
                                  customDimensions.eventId == "LC0035", "The technical validation of your offer failed!",
                                  "Unknown")<jupyter_output><empty_output><jupyter_text>A task is considered as failed when at least one diagnostic with severity Error has been reported. You can analyze the reported diagnostics in the next section.This query shows you which extensions in your submission have **passed** the technical validation and for which country/region and release.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0033'
| project timestamp
, version = tostring(customDimensions.version)
, countryRegion = tostring(customDimensions.countryRegion)
, extensionId = tostring(customDimensions.extensionId)
, extensionName = tostring(customDimensions.extensionName)
, extensionPublisher = tostring(customDimensions.extensionPublisher)
, extensionVersion = tostring(customDimensions.extensionVersion)
| distinct extensionId, extensionName, extensionPublisher, extensionVersion, countryRegion, version
| order by extensionName, countryRegion, version asc<jupyter_output><empty_output><jupyter_text>This query shows you which extensions in your submission have **failed** the technical validation and for which country/region and release.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0037'
| project timestamp
, version = tostring(customDimensions.version)
, countryRegion = tostring(customDimensions.countryRegion)
, extensionId = tostring(customDimensions.extensionId)
, extensionName = tostring(customDimensions.extensionName)
, extensionPublisher = tostring(customDimensions.extensionPublisher)
, extensionVersion = tostring(customDimensions.extensionVersion)
| distinct extensionId, extensionName, extensionPublisher, extensionVersion, countryRegion, version
| order by extensionName, countryRegion, version asc<jupyter_output><empty_output><jupyter_text>Diagnostics reported for failed tasksThere are three types of diagnostics reported during the technical validation:| Diagnostic Source | Diagnostic Prefix | Example || --- | --- | --- || AL Compiler | AL | AL0001 || AppSourceCop | AS | AS0001 || Validation Service | AVS | AVS0001 | You can specify additional filters if you want to look for a specific task.<jupyter_code># Optional parameters, if you want to investigate the results for a specific task
extensionId   = ""      # The app ID of the extension that failed validation
countryRegion = ""      # The country/region for which the extension failed validation
version       = ""      # add version of BC for which the extension failed validation

print("Using additional optional parameters for the analysis:")
print("----------------------------------------")
print("extensionId         " + extensionId)
print("countryRegion       " + countryRegion)
print("version             " + version)<jupyter_output><empty_output><jupyter_text>The query below returns all the diagnostics reported for tasks that failed the technical validation and are matching your optional filers if any.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
let _extensionId = extensionId;
let _countryRegion = countryRegion;
let _version = version;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0034'
    and (customDimensions.extensionId == _extensionId or _extensionId == "")
    and (customDimensions.countryRegion == _countryRegion or _countryRegion == "")
    and (customDimensions.version == _version or _version == "")
| project timestamp
, version = tostring(customDimensions.version)
, countryRegion = tostring(customDimensions.countryRegion)
, extensionName = tostring(customDimensions.extensionName)
, diagnosticCode = tostring(customDimensions.diagnosticCode)
, diagnosticSource = case(customDimensions.diagnosticCode startswith "AL", "AL Compiler",
                          customDimensions.diagnosticCode startswith "AS", "AppSourceCop",
                          customDimensions.diagnosticCode startswith "AVS", "Validation Service",
                          "Unknown")
, diagnosticDocumentation = case(customDimensions.diagnosticCode startswith "AS", strcat("https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/analyzers/appsourcecop-", customDimensions.diagnosticCode),
                                 "N/A")
, diagnosticSeverity = tostring(customDimensions.diagnosticSeverity)
, diagnosticMessage = tostring(customDimensions.diagnosticMessage)
, diagnosticSourcePath = tostring(customDimensions.diagnosticSourcePath)
, diagnosticSourceLocation = tostring(customDimensions.diagnosticSourceLocation)
| project extensionName, countryRegion, version, diagnosticCode, diagnosticSeverity, diagnosticMessage, diagnosticSourcePath, diagnosticSourceLocation, diagnosticSource, diagnosticDocumentation
| order by extensionName, countryRegion, version asc<jupyter_output><empty_output><jupyter_text>If an extension failed the validation because the **AppSourceCop reported a breaking change**, you can **investigate which baseline versions have been used** during the validation in the dedicated section below.If an extension failed the validation because the **AL Compiler reported a compilation error**, you can **investigate which dependencies have been used** during the validation in the dedicated section below.  Baseline versions used during breaking change validationDuring the validation, the baseline versions used during breaking change validation are computed by taking the highest available version of the extensions in your submission for each targeted country/region.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0030'
| project countryRegion = tostring(customDimensions.countryRegion)
, version = tostring(customDimensions.version)
, extensions = customDimensions.extensions
, baselineExtensions = customDimensions.baselineExtensions
| order by countryRegion, version asc<jupyter_output><empty_output><jupyter_text>Dependency versions used during the validationDuring the validation, the dependency versions are computed by taking the highest available version of the depenencies for each targeted country/region and release.<jupyter_code>%%kql
let _validationRequestId = validationRequestId;
let _startDate = startDate;
let _endDate = endDate;
traces
| where timestamp between (todatetime(_startDate) .. todatetime(_endDate))   
    and customDimensions.validationRequestId == _validationRequestId
    and customDimensions.eventId == 'LC0030'
| project countryRegion = tostring(customDimensions.countryRegion)
, version = tostring(customDimensions.version)
, extensions = customDimensions.extensions
, allExtensions = customDimensions.allExtensions
| order by countryRegion, version asc<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07490308865824684, 'fraction_numerical': 0.008065524571714392, 'mean_word_length': 5.400560224089636, 'pattern_counts': {'":': 0, '<': 56, '<?xml version=': 0, '>': 56, 'https://': 9, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '391510', 'license': 'MIT', 'n_tokens_mistral': 4114, 'n_tokens_neox': 3964, 'n_words': 1355, 'path': 'samples/AppInsights/TroubleShootingGuides/D365BC Troubleshooting Guides (TSG)/content/AppSource-Submission-TSG.ipynb', 'repo_name': 'tino-teuber/BCTech', 'size': '15994'}