Custom report templates v10.4

The custom report templates feature in PEM 10.4 introduces a robust framework that enables users to design, manage, and execute tailored diagnostic templates across any server actively bound to a PEM Agent. By consolidating SQL queries and host-level batch or shell scripts into organized logical sections, this feature allows users to extract highly specific insights with results rendered in versatile Table (tabular grid) or Text (scalar value) formats.

Prerequisites

To execute scripts or SQL queries via custom report templates:

• The target server must be bound to a PEM agent (version 10.4 or later).
• For shell/batch scripts, the agent must have the necessary permissions to execute commands on the host operating system.

Managing report templates

To access the management interface, navigate to Management > Reports > Manage Report Templates.

1. Creating a custom report template:

   The top-level container defined by a unique title and functional description.

   • Select Create New Report.
   • Title: Enter a unique name for the report (required).
   • Description: Provide an explanation of the report's purpose (optional).

Defining the report structure

Custom report templates utilize a hierarchical architecture to ensure data is structured and logically categorized:

• Report: The top-level container; contains one or more Sections.

• Section: A logical grouping within a report; contains one or more Items.

• Item: The individual unit of execution.

  Item definitions: Each item within a section is defined by the following attributes.

  Name	Description
  Title	The name of the specific item.
  Description	An optional field providing functional context or an explanation.
  Kind	Specifies the execution engine: SQL Query or Batch/Shell Script.
  Format	Defines the presentation layer: Table (tabular output) or Text (single value).
  Code	The specific SQL statement or script logic to be executed by the agent.

Executing a report

• Select the Target Server in the PEM browser (ensure it is bound to a 10.4 agent).

• Right-click the server and select Run Report.

• Select the desired Report Template and the target Database from the dropdown menus.

• Select Run. This triggers an agent-level job to execute the report.

  Note

  Custom reports cannot be executed on the PEM database itself.

Viewing execution history

To monitor the status of triggered reports and audit historical data, navigate to Management > Reports > Report Execution History.

The execution history provides a centralized audit trail of all custom report templates activities. To effectively manage and locate specific records within a high-volume environment, you can apply the following filters:

• Server Name: Filter by the specific target server where the report was executed.
• Agent Name: Filter by the unique PEM Agent responsible for processing the execution.
• Status: Filter by execution states (e.g., Success, Partially Successful, Failed, In Progress, or Unknown).
• Time Range: Define a specific temporal window to isolate recent or historical data.

Viewing report results

To access the detailed output of a report, select any execution record within the Report Execution History dashboard.

Result organization: Results are structured according to the original template hierarchy defined during the creation phase. Data is partitioned into Sections, with each section containing its respective Items.

Data presentation layers: Depending on the Format attribute assigned to each item within the template, results are rendered in one of two ways:

Handling errors: If an item within a report fails to execute, the system captures and displays the specific error message returned by the SQL engine or the shell environment directly within the item's result block. This ensures that even in a Partially Successful report, you can identify and troubleshoot individual point failures.

Exporting results: For external reporting or further data processing, you can download the executed report in the following formats:

• HTML: A formatted document for easy sharing and browser-based review.
• JSON: A structured data file optimized for integration with automated workflows or third-party tools.

Importing and exporting reports

PEM allows for the seamless migration and backup of report templates via standardized JSON files.

Exporting report templates To backup or share your report configurations:

• Navigate to Management > Reports > Manage Report Templates.

• Select one or more templates from the list.

• Select Export to download the selected configurations as a JSON file.

  Importing report templates To bulk-add templates to a new or existing PEM instance:

• Open the Manage Report Templates interface.

• Select the Import button.

• Upload a valid JSON file containing report template definitions.

  Note

  If a template with the same title already exists, you will be prompted to either skip or overwrite the entry.

Understanding status definitions

The Report execution history tab tracks the lifecycle of each report job. The following table defines the possible states for a custom report templates:

Limiting SQL row counts

To optimize performance and prevent excessive memory consumption on the PEM server, custom report templates enforce a limit on the number of rows returned by SQL queries.

This limit is managed globally via the Server Configuration dashboard, where administrators can modify the value to accommodate larger data sets.

!!! Note Increasing this limit may significantly impact report generation speed and the overall responsiveness of the Report results view when handling large volumes of data.

System defined report templates

While PEM allows for fully customized reporting, it also includes pre-configured system report templates designed for deep database analysis. These reports are built into the platform to provide immediate value without requiring manual template configuration.

EDB Postgres workload report (PWR)

The EDB Postgres workload report (PWR) provides a comprehensive overview of a server's performance and resource utilization over a specific snapshot duration. It is an essential tool for identifying bottlenecks, high-load SQL statements, and wait events.

Server information

Shows information about the server version:

• Server version — The version number of the Postgres server.
• Architecture — The hardware architecture for which the server is built.
• System identifier — A unique identifier for the database cluster.
• Redwood mode — Indicates whether the cluster was created in Redwood (Oracle compatibility) mode.
• Current user — The user who generated the report.
• Actual start/end snap timestamp — The precise timestamps marking the beginning and end of the data collection.
• Snapshot duration — The total time elapsed during the snapshot.

Shows information about the report timestamps:

• Session counts — The number of connected sessions at both the start and end of the snapshot.
• Database time — The total non-idle time (in seconds) that all sessions spent either working or waiting in the database.
• Uptime — Total time spent since the last server restart.
• Role — Indicates whether the server is a Primary or Standby instance.

Load profile

Shows the average load profile per transaction:

• Blocks fetched — Average number of blocks accessed per transaction.
• WAL records/bytes — Average number of Write-Ahead Log records and bytes generated per transaction.
• Transactions — The total number of transactions processed during the snapshot.
• Abort rate — The percentage of transactions that resulted in an abort.

Top wait events

Displays total wait time spent on top events (in seconds):

• Wait event — The name of the specific wait event. (CPU indicates time spent actively working).
• Wait class — The category or type of the wait event.
• Wait time — Total time in seconds spent on this event.
• Pct database time — The percentage of total Dbtime spent waiting on this event type.

Top SQL statements

Displays the top 10 SQL statements sorted by dbtime:

• Database time — The cumulative database time (in seconds) consumed during the execution of the SQL statement.
• Wait time — The total duration the SQL statement spent waiting for system resources or background events.
• CPU time — The total amount of CPU processing time utilized by the SQL statement.
• Top wait event — The specific wait event identifier where the statement experienced its longest delay.
• Query — The actual SQL text of the executed query.

Transaction stats

Shows transaction stats during the snapshot duration:

• Xact commit — The total number of successfully completed and saved transactions.
• Xact rollback — The total number of reversed or aborted transactions.

WAL stats

Shows stats about WAL record changes during snapshot duration:

• Wal records — The total number of individual WAL records generated during the snapshot.
• Wal fpi — The number of Full Page Images (FPI) created; these occur after the first change to a page following a checkpoint and can significantly increase WAL volume.
• Wal bytes — The total size of all generated WAL data, measured in bytes.

Shared buffers stats

Shows shared buffers stats during the snapshot duration:

• Blocks read — The total number of disk blocks read.
• Blocks hit — The total number of blocks hit.

Tuple stats

Shows stats about the tuples during the snapshot duration:

• Tuples returned — The number of rows returned by sequential scans.
• Tuples fetched — The number of rows fetched specifically via index scans.
• Tuples inserted — The total number of new rows added to the database.
• Tuples updated — The total number of existing rows modified.
• Tuples deleted — The total number of rows removed from the database.

Temporary files stats

Shows stats about the temporary file creation:

• Temp files — The number of temporary files created.
• Temp bytes — The total volume of data written to temporary files.

User session information

Session information connected/running during snapshot period:

• Session ID — The unique ID assigned by edb_wait_states to a specific Postgres session.
• Database name — The name of the database to which the session is currently connected.
• Username — The identity of the database user logged into the session.
• Application name — The name of the application or client tool connected to this session (e.g., psql, pgAdmin, or a custom app service).
• Client hostname — The network host name of the connected client, providing the origin of the connection (where available).
• Session start timestamp — The precise date and time when the session was initially established.

System information

Shows CPU and memory information of the server machine:

• Host name — The network name of the machine where Postgres is running.
• CPU information — Details including the number of processors, vendor, and clock speed.
• Memory information — Total memory information for the host.

Postgres database settings

Shows the settings of the Postgres database:

• Category — The logical functional group of the parameters.
• Parameter — The name and current value of settings within a category. A highlighted entries denotes parameters with custom values.
• Value — The current operational setting for the specified parameter.
• Is custom value — A flag indicating if the parameter has been modified from its default value.