Using Azure Log Analytics on older Dynamics NAV versions

Sometimes there are topics that I could swear I wrote about and then someone makes you realise this is not the case.

This week that happened with my blog about what Page 9599 means when you see it popping up in Azure Telemetry.

Some folks on twitter started asking how it was possible that Super Users were changing data by running tables. I understand the confusion because in newer versions this is blocked by Microsoft.

But… older versions don’t support analyzing performance telemetry using KQL right? So this girl must be seriously confused.

Although the latter happens from time to time, this is not the case here. Because it is possible to analyse performance telemetry for older NAV versions with Azure Log Analytics and KQL.

I created some documentation around this when I created readiness materials for QBS Group earlier this year. Since not all of you are following their blog I figured it made sense to repost it on my own blog.

The Trick – Windows Event Log

To make this work, the trick is simply to enable writing content of the Windows Event Log to Azure Log Analytics and to create a few simple KQL Queries with regular expressions to analyse the data.

The result is this:

And here is an example query

Event
| where ParameterXml contains "AppObjectType"
| extend object = strcat(extract("AppObjectType:\s{1,}([^\ ]+)\s", 1, ParameterXml), extract("AppObjectId:\s{1,}([^\ ]+)\s", 1, ParameterXml))
| extend executionTime = toint(executionTime = extract("Execution time:\s{1,}([^\ ]+)\s", 1, ParameterXml))
| extend query = strcat(extract("SELECT\s.FROM\s.WHERE\s.", 0, ParameterXml), extract("DELETE\s.FROM\s.WHERE\s.", 0, ParameterXml), extract("UPDATE\s.SET\s.WHERE\s.", 0, ParameterXml), extract("BeginTransaction\s.", 0, ParameterXml), extract("Commit\s.", 0, ParameterXml), extract("Rollback\s.", 0, ParameterXml), extract("INSERT\s.VALUES\s.", 0, ParameterXml), extract("SELECT\s.FROM\s.", 0, ParameterXml), extract("DECLARE\s.INSERT\s.", 0, ParameterXml))
| where ParameterXml contains "Message: Long running SQL statement"
| order by TimeGenerated desc

WARNING!!

Microsoft made small changes in different versions of NAV. You may need to change the regular expressions from version to version.

More Details can be found on my new github.

Enjoy,

Marije