alerts Archives - DAXRunBase

Add call stack to InfoLog messages

The main communication channel for our ERP users in case we want to tell them something is via the InfoLog messages within Microsoft Dynamics AX. In case we get an error or a warning, the technical staff does not receive the details required to troubleshoot the issue much easier. In this post I would like to show you how to send the X++ or .Net CIL call stack to InfoLog messages.
Microsoft already has an article on this for AX 2009, but that was before the AX 2012 IL code execution for server-side code, so it needs slight adjustments.

https://blogs.msdn.microsoft.com/axsupport/2010/08/02/how-to-send-the-callstack-to-the-infolog/

My changes got the following improvements:

Can handle X++ and CIL execution.
Users only see the call stack when clicking the message line due to filling it with whitespace.
Specific users could be excluded from receiving the call stack, we are doing this for our service accounts such as the batch execution account. We are also excluding our eCommerce portal customers from seeing a call stack, which are the Claims-based users.
Current database server/name and user account is included in the message, in case we are storing the errors in the Event log and would like to know who had the problem.

Here is the example output:

(more…)

By DAXRunBase|2020-03-23T13:50:07+01:00July 20th, 2017|Categories: AX 2012|Tags: alerts, AX 2012, call stack, GitHub, InfoLog, X++, XPO|0 Comments

Slow performing SQL statements in AX – Update

There has been some compatibility improvements and fixes on SQL Server 2014 for the slow performing statements in AX job. The updated version can be accessed from the original article:

E-mail alerts for slow performing SQL statements in AX

By DAXRunBase|2017-09-12T07:59:17+02:00August 16th, 2016|Categories: AX 2012|Tags: alerts, AX 2012, e-mail, performance, SQL, statements|1 Comment

Enable batch e-mail alerts from code

The setup of notifications for batch jobs are quite simple if you know where to look. To enable batch e-mail alerts from code all you need to do is create new records under \\Tables\BatchJobAlerts with the right flags for an AX user, who has their e-mail address set in the User Options correctly.

The following X++ Job is self-explanatory on how to enable batch e-mail alerts from code in case of Cancellation or an Error:

static void WIK_enableBatchJobAlerts(Args _args)
{
    #Batch
    Map             alertsMap;
    Map             alertsEntity;
    BatchJob        batchJob;
    BatchJobAlerts  batchJobAlerts;
    
    while select batchJob
        where (batchJob.Status      == BatchStatus::Executing
            || batchJob.Status      == BatchStatus::Waiting)
            // Optional filtering for dedicated batch service account only
            && batchJob.createdBy   == 'axbatchuserid'
        notexists join batchJobAlerts
            where  batchJobAlerts.BatchJobId    == batchJob.RecId
                // Include all batch jobs where e-mail alert is disabled for our account
                && batchJobAlerts.UserId        == batchJob.createdBy
                && batchJobAlerts.Email         == NoYes::Yes
    {
        // This can be called any number of times to alert multiple users even with different settings
        alertsMap = BatchJobAlerts::addAlertsToMap(
            batchJob.createdBy, // Whom to alert
            NoYes::No,          // Ended
            NoYes::Yes,         // Error
            NoYes::Yes,         // Canceled
            NoYes::No,          // Popup
            NoYes::Yes          // E-mail
            );
        
        BatchJobAlerts::saveAlerts(batchJob.RecId, alertsMap);
        
        alertsEntity = alertsMap.lookup(batchJob.createdBy);
        
        info(strFmt('Enabling alert (Ended=%1 Error=%2 Canceled=%3 Popup=%4 E-mail=%5) for user <%6> and batch <%7>',
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobEnded)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobError)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobCanceled)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#popup)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#email)),
            batchJob.createdBy,
            batchJob.Caption
            ));
    }
}

The output will be something like this:

Enabling alert (Ended=No Error=Yes Canceled=Yes Popup=No E-mail=Yes) for user <sa.axbat> and batch <ADMIN – Due date alerts – every 15m>

By DAXRunBase|2016-02-17T16:03:08+01:00February 17th, 2016|Categories: AX 2012|Tags: alerts, AX 2012, batch, job, X++|1 Comment

E-mail alerts for slow performing SQL statements in AX

There are not that many tools to assist us in proactively monitoring our AX database health. I have created a set of SQL jobs that could generate e-mail alerts for slow performing SQL statements in AX to improve the system predictability.

If you are using DynamicsPerf already, it is very likely that the SQL Trace in the User options got already enabled to capture slow performing statements that take longer than 5 seconds, otherwise you could enable that yourself to start capturing data in SysTraceTableSQL in your AX database.

The information stored in the aforementioned table can be analyzed and aggregated to show what tables have been performing slow since the last execution of my job each day. I am also separating wildcard searches, since the LIKE statements mostly do not reuse query plans and you cannot improve their speed much further. It is however a must to keep an eye on the tables with high number of increments for possible outdated query plans, old statistics, missing indexes or poorly designed AX statements.

These are the requirements and steps for setting alerts for slow performing SQL statements in AX:

Enable SQL Trace with a sensible timeout for the AX users to be monitored.
Create a database where the daily aggregated slow performing statements and the Table name extracting function will be stored, in my sample code as [master].
Make sure there is an e-mail profile set up for sending the mails from under SQL Server Management Studio > SQL instance > Management > Database Mail, in my sample code as MYEXCHANGE.
Create a new SQL Server Agent Job and paste the attached statement as a Transact-SQL body. Make sure you replace the variables and values for the administration database ([master]), the target AX database (MyAXDB), mail profile (MYEXCHANGE) and destination e-mail address values.
Add a daily Recurring schedule to the Job to occur every 2 hours, or any interval to your liking below 24 hours
Run the job manually for the first time to create the table/function and pre-populate the records

Once the setup is successfully completed and a couple of slow statements gets recorded in the SysTraceTableSQL table in AX, the next scheduled run will pick up the values and send out the e-mail alert if you have done everything correctly.

WIK_SlowPerformingStatements_Job

By DAXRunBase|2016-08-16T11:09:44+02:00January 7th, 2016|Categories: AX 2012|Tags: alerts, AX 2012, e-mail, performance, SQL, statements|4 Comments