APM Insight Report Functions - Via Custom Queries

Ready-made reports for APM Insight: HTTP errors, transaction health and SLA breaches.
For Applications Manager servers that use the PostgreSQL (pgsql) database.

Available reports

Report	What it shows	Function
Errors per application	4xx and 5xx error counts for each application	`apm_app_error_split`
Top error transactions	The transactions causing the most errors	`apm_txn_error_split`
Transaction health	How many transactions are Critical or Clear	`apm_txn_health`
SLA breaches	How many transactions are slower than 2 s, 4 s or 10 s	`apm_txn_sla`
SLA breached transactions	Which transactions are slower than your limit	`apm_txn_sla_breaches`

Get the reports

Contact Applications Manager support to get the script file that adds these reports. It comes as a zip with a Windows script and a Linux script.

Run the script on each server that holds APM Insight data: in a Central setup, that's each Probe Server. Applications Manager must have been started at least once on that server.

Windows

Extract InstallAPMInsightReportFunctions.bat from the zip into <APM home>\bin.
Open a command prompt as Administrator.
Go to that folder and run the script:

cd C:\ManageEngine\AppManager\bin
InstallAPMInsightReportFunctions.bat

Example path. Use your own Applications Manager installation folder.

Linux

Extract InstallAPMInsightReportFunctions.sh from the zip into <APM home>/bin.
Open a terminal as the user that runs Applications Manager.
Go to that folder and run the script:

cd /opt/ManageEngine/AppManager/bin
sh InstallAPMInsightReportFunctions.sh

Example path. Use your own Applications Manager installation folder.

How to run a report

Run reports from the Query Tool, or save them as a Custom Query Report (see Save, schedule or monitor below).

Each report is a function. You choose what it reports by passing values inside its brackets:

SELECT * FROM apm_txn_health('30m', 2000, 5)

Part of the query	What it means
`apm_txn_health`	The report to run: Transaction health
`'30m'`	1st value: the time period, the last 30 minutes
`2000`	2nd value: transactions are slow from 2000 ms (2 seconds)
`5`	3rd value: transactions have too many errors from 5%

Each report below lists the values it accepts, in order.

Time periods

The first value of every report is the time period, in single quotes. Only these periods are supported:

Value	Means
`'5m'`	last 5 minutes
`'15m'`	last 15 minutes
`'30m'`	last 30 minutes default
`'1h'`	last hour
`'3h'`	last 3 hours
`'6h'`	last 6 hours
`'12h'`	last 12 hours
`'1d'`	last day
`'3d'`	last 3 days
`'7d'`	last 7 days

If you leave the time period out, the last 30 minutes are used. Any other value gives an error that lists the supported periods.

Choose applications

Reports cover all applications by default. To report on specific ones, pass their IDs as the last value:

SELECT * FROM apm_app_error_split('1d', ARRAY[10000364])

To list your application IDs:

SELECT resourceid AS applicationid, application_name FROM apm_applications ORDER BY 2

applicationid	application_name
10000364	Ecommerce App

Example result.

Errors per application

The number of 4xx and 5xx errors for each application.

SELECT * FROM apm_app_error_split('30m')

Values

Pass these values inside the brackets, separated by commas, in this order:

apm_app_error_split(time_period, applications)

Position	Value	Default	Example
1	Time period	`'30m'`	`'1d'`
2	Applications	all	`ARRAY[10000364]`

You can leave out values from the end; they use their defaults. You can't skip one in the middle.

Columns

Column	Meaning
`applicationid`	Application ID
`displayname`	Application name
`count_4xx`	4xx Client errors, such as a bad request or page not found
`count_5xx`	5xx Server errors
`total_errors`	Both together

Result

applicationid	displayname	count_4xx	count_5xx	total_errors
10000364	Ecommerce App	2	0	2

Example: Ecommerce App had two 4xx errors and no 5xx errors.

Top error transactions

The transactions with the most 4xx and 5xx errors, per application.

SELECT * FROM apm_txn_error_split('30m', 10)

Values

Pass these values inside the brackets, separated by commas, in this order:

apm_txn_error_split(time_period, how_many, applications)

Position	Value	Default	Example
1	Time period	`'30m'`	`'1d'`
2	How many transactions to show per application	`10`	`5`
3	Applications	all	`ARRAY[10000364]`

You can leave out values from the end; they use their defaults. You can't skip one in the middle.

Columns

Column	Meaning
`applicationid`, `displayname`	The application
`transactionid`	Transaction ID
`transactionname`	The transaction, for example a URL
`txn_type`	Transaction type, such as HTTP
`iskeytxn`	1 if it's a business transaction
`count_4xx`, `count_5xx`	Its 4xx and 5xx errors
`total_errors`	Both together

Result

applicationid	displayname	transactionid	transactionname	txn_type	iskeytxn	count_4xx	count_5xx	total_errors
10000364	Ecommerce App	10000062	/checkout/payment	HTTP	0	2	0	2

Example: one transaction, /checkout/payment, returned two 4xx errors.

Transaction health

Shows how many transactions are Critical and how many are Clear. A transaction is Critical when it's slow or has too many errors.

SELECT * FROM apm_txn_health('30m')

By default, slow means an average response time of 0.5 seconds or more, and too many errors means 1% or more.

Values

Pass these values inside the brackets, separated by commas, in this order:

apm_txn_health(time_period, slow_from_ms, errors_from_pct, critical_when, business_only, applications)

Position	Value	Default	Example
1	Time period	`'30m'`	`'1d'`
2	Slow from this response time, in milliseconds	`500`	`2000` = 2 seconds
3	Too many errors from this error %	`1`	`5`
4	Critical when (see below)	`'or'`	`'rt'`
5	Business transactions only	`false`	`true`
6	Applications	all	`ARRAY[10000364]`

You can leave out values from the end; they use their defaults. You can't skip one in the middle.

Value 4 decides which checks count:

Value	Meaning
`'or'`	Critical if slow or too many errors
`'and'`	Critical only if slow and too many errors
`'rt'`	Critical if slow; errors are ignored
`'err'`	Critical if too many errors; response time is ignored

Examples

Slower than 2 seconds, or more than 5% errors

SELECT * FROM apm_txn_health('30m', 2000, 5)

Slower than 2 seconds, ignoring errors

SELECT * FROM apm_txn_health('30m', 2000, 1, 'rt')

With 'rt' the error value (1 here) is ignored, but it still has to be there.

Business transactions only, default limits

SELECT * FROM apm_txn_health('30m', 500, 1, 'or', true)

Result

applicationid	displayname	critical	clear	total
10000364	Ecommerce App	9	110	119

Example: 119 transactions had traffic in the time period. 9 were Critical and 110 Clear.

SLA breaches

Shows how many transactions are slower than each limit you give.

SELECT * FROM apm_txn_sla('30m', ARRAY[2000,4000,10000])

Values

Pass these values inside the brackets, separated by commas, in this order:

apm_txn_sla(time_period, limits_ms, business_only, applications)

Position	Value	Default	Example
1	Time period	`'30m'`	`'1d'`
2	Limits, in milliseconds	`ARRAY[2000,4000,10000]`	`ARRAY[1000,3000]`
3	Business transactions only	`false`	`true`
4	Applications	all	`ARRAY[10000364]`

You can leave out values from the end; they use their defaults. You can't skip one in the middle.

Result

applicationid	displayname	threshold_ms	breached	total	breach_pct
10000364	Ecommerce App	2000	1	119	0.84
10000364	Ecommerce App	4000	1	119	0.84
10000364	Ecommerce App	10000	1	119	0.84

Example: one row per limit. 1 of 119 transactions was slower than 2 s, and the same one was slower than 10 s.

SLA breached transactions

Lists the transactions that are slower than a response-time limit, slowest first.

SELECT * FROM apm_txn_sla_breaches('30m', 2000)

Values

Pass these values inside the brackets, separated by commas, in this order:

apm_txn_sla_breaches(time_period, limit_ms, business_only, applications)

Position	Value	Default	Example
1	Time period	`'30m'`	`'1d'`
2	Limit, in milliseconds	`2000`	`5000` = 5 seconds
3	Business transactions only	`false`	`true`
4	Applications	all	`ARRAY[10000364]`

You can leave out values from the end; they use their defaults. You can't skip one in the middle.

Columns

Column	Meaning
`applicationid`, `displayname`	The application
`transactionid`	Transaction ID
`transactionname`	The transaction, for example a URL
`txn_type`	Transaction type, such as HTTP
`iskeytxn`	1 if it's a business transaction
`avg_response_ms`	Average response time in the time period, in milliseconds
`requests`	Number of requests in the time period

Result

applicationid	displayname	transactionid	transactionname	txn_type	iskeytxn	avg_response_ms	requests
10000364	Ecommerce App	10000087	/orders/export	HTTP	0	12480	36

Example: one transaction averaged about 12.5 seconds over 36 requests.

Save, schedule or monitor

Save as a Custom Query Report

Help: Custom Query Report

Go to Settings > Reporting > Custom Query Report and click Create New Report.
Fill in the fields:

Field	What to enter
Report Name	A name, for example Transaction health, last 30 minutes
Description	What the report shows
Query	One of the queries on this page
Execute query on	On a Central Server only: choose the Probe Servers to run it on

Save the report. To see the results, open it and click Process.

Note: APM Insight raw data is stored only on Probe Servers, so choose Probe Servers, not the Central Server. The reports must be set up on each Probe Server you choose.

Schedule it

Help: Schedule Reports

Go to Settings > Reporting > Schedule Reports and create a new schedule.
Choose Custom Query Report as the report type and select your report.
Set when it runs (daily, weekly or monthly) and choose the format.

Scheduled reports are sent by email.

Monitor it

Help: Database Query Monitor

Add a Database Query Monitor for the Applications Manager PostgreSQL database and use the same query. The results are then collected on every poll.

Good to know

No results? There may have been no traffic in that period. Try '7d'.
Health and SLA use averages. One slow request in a busy transaction won't mark it Critical.
To remove the reports, contact Applications Manager support.