Agresso report queue

Job details

Name:	Agresso report queue
Platform:	Sqlserver
Category:	Agresso
Description:	Monitors and checks pending or running reports. Returns warning or alarm if the elapsed time exceeds defined thresholds.
Long description:	Monitors and checks pending or running reports. Returns warning or alarm if the elapsed time exceeds defined thresholds.
Version:	2.21
Default schedule:	4,9,14,19,24,29,34,39,44,49,54,59 7-17 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2012′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
warning threshold	60	Elapsed time warning threshold for running reports (in minutes).
alarm threshold	180	Elapsed time alarm threshold for running reports (in minutes).
time threshold	24	Only reports started within the “time threshold” (in hours) will be checked.
database name	agrprod	Name of the database where the Agresso application is installed. You can specify a list of databases (separated by comma). You can use % (percent sign) to represent wild card characters.

Job Summary

Purpose: The purpose of this job is to monitor and check for any pending or running reports in the Agresso system. It alerts users if the elapsed time for these reports exceeds the defined thresholds.
Why: This job is crucial to ensure timely execution of reports and manage system performance effectively. By monitoring the queuing and processing times of reports, it helps in identifying and addressing performance bottlenecks promptly.
Manual checking: You can manually check the status of running or pending reports by executing the following SQL command:

select report_name "Name", client "Client", date_started "Started", duration "Duration", server_queue "Server", user_id "User", db_name "Database", status "Status" from agresso_acrrepord_tab order by status desc, duration desc

Key Features

Monitors both pending and running reports in the Agresso application.
Returns warnings if report execution times exceed the ‘warning threshold’ and alarms if they surpass the ‘alarm threshold’.
Operates within a set ‘time threshold’ to only consider recent reports for monitoring.
Provides detailed statuses of Agresso reports and statistical insights.

Dependency Overview

Dependency Type	Object	Description	Cleanup on Fail
Active Reports	agresso_acrrepord_tab	Contains information about active reports with status W	true
Main Procedure	agresso_acrrepord_check	The central procedure that implements the monitoring logic	true
Database Information	agresso_acrrepord_db_info	Holds details about the Agresso database configurations	true

Procedural Implementation

A variety of SQL elements such as tables and procedures are created to support the monitoring functionalities.
The main procedure (agresso_acrrepord_check) involves:
- Preparing the environment by cleaning up and configuring initial settings.
- Iterating through databases that conform to the specified ‘database name’.
- Fetching and evaluating each configured report based on the time elapsed since the start.
- Determining the status (Warning, Alarm) of each report based on elapsed time conditions.
- Updating tracking tables with current statuses for reporting purposes.
- Handling any exceptions during runtime and logging the appropriate details for corrective action.

Reporting Details

Two types of report presentations are prepared:
- “Agresso reports”: Displays information from the ACRREPORD table with details such as Name, Client, Start Time, Duration, Server, User, Database, and Status.
- “Database details”: Shows details of all checked databases.

Installation and Compatibility

The job is configured to replace existing installations upon deployment.
Suitable for database instances of SQL Server versions greater than 2012, including Microsoft SQL Server and Azure SQL Managed Instances.
Utilizes a compatibility query to verify the appropriate environment settings during installation.

With these features, the dbWatch job ensures robust monitoring and management of Agresso report executions, supporting database administrators in maintaining high performance and availability.