KB Article #183116

Monitoring the Event Queue with SQL queries

Updated: November 2026

Problem

Server-initiated transfers in ST are coordinated by events in the Event table in the database. In the past, most of the valuable information was hidden in two binary large object fields, data and sessionData, which are difficult to use with standard SQL queries to aggregate events for monitoring purposes.


SecureTransport 5.5-20220127 introduced a new feature for events queue management, which changes the Event table to help with better aggregation of events. The feature works only via the REST API 2.0 resource /events. In ST 5.5-20231130, the feature was further extended with a dedicated page in the Admin UI. Originally designed to handle individual events, it has no ordering (sorting) or grouping capabilities yet, only filtering and deletion. This works for managing events but is not sufficient for monitoring.


Resolution

For monitoring purposes, an SQL query is the best option for now, until Events Queue Management is extended for monitoring. The SQL query has a different syntax for each currently supported database type (PostgreSQL, Oracle, MSSQL) because of the different ways of converting timestamps to a human-readable format and of extracting UPLOAD_FOLDER from FULLTARGET.


SecureTransport 5.5-20240829 is the last version that uses MariaDB as the embedded database. SQL queries for MariaDB can be found in the attached file. If the SQL queries below for your database type do not work, you are probably running an ST version prior to SecureTransport 5.5-20220127, where the accountname column is missing from the Event table. In such a case, use the SQL queries from the attached file that correspond to your database type.


Note that this article applies mainly to Server-Initiated Transfers (SITs). Client-Initiated Transfers (CITs) insert only transfer end events in the event queue. Any CIT becomes a SIT when a file arrives in a subscription folder. The SQL queries presented below are constructed for Linux paths.


The full set of events with the maximum meaningful agregation.

PostgreSQL

SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
LEFT(fulltarget, LENGTH(fulltarget) - POSITION('/' in REVERSE(fulltarget))) as targetFolder, accountName, 
to_timestamp(TRUNC(MIN(event.arrivaltime)/1000)) as oldestArrivalTime, 
Subscription.folder as subscriptionFolder 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, LEFT(fulltarget, LENGTH(fulltarget) - POSITION('/' in REVERSE(fulltarget))), accountName, Subscription.folder
ORDER BY 8 ASC, 2 DESC;


Oracle

SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
substr(fulltarget,1, instr(fulltarget,'/',-1)) as targetFolder, accountName, 
TO_CHAR(DATE '1970-01-01' + MIN(arrivaltime) / 86400000, 'YYYY-MM-DD HH24:MI:SS') as oldestArrivalTime, 
Subscription.folder as subscriptionFolder 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, substr(fulltarget,1, instr(fulltarget,'/',-1)), accountName, Subscription.folder
ORDER BY 8 ASC, 2 DESC;


MSSQL

SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
LEFT(fulltarget, LEN(fulltarget) - CHARINDEX('/', REVERSE(fulltarget), 0)) as targetFolder, accountName, 
DATEADD(second, cast(MIN(arrivalTime)/1000 as int), convert(datetime, '01/01/1970', 101)) as oldestArrivalTime, 
Subscription.folder as subscriptionFolder 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, LEFT(fulltarget, LEN(fulltarget) - CHARINDEX('/', REVERSE(fulltarget), 0)), accountName, Subscription.folder
ORDER BY 8 ASC, 2 DESC;


The full set of events with details for each event type.

PostgreSQL

SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
CASE 
    WHEN (agentType = 3) THEN 'incoming'
    WHEN (agentType = 4) THEN 'outgoing'
    WHEN (agentType = 10) THEN 'servertransfer'
    WHEN (agentType = 11) THEN 'schedule'
    WHEN (agentType = 12) THEN 'transformation'
    WHEN (agentType = 13) THEN 'Application'
    WHEN (agentType = 20) THEN 'Resubmit'
    WHEN (agentType = 28) THEN 'AdvancedRouting'
    WHEN (agentType = 29) THEN 'ICAPScan'
    else agentType::text
END as agentType,
CASE
    WHEN (agentTrigger = 0) THEN 'start'
    WHEN (agentTrigger = 1) THEN 'end'
    WHEN (agentTrigger = 2) THEN 'abort'
    WHEN (agentTrigger = 3) THEN 'error'
    WHEN (agentTrigger = 23) THEN 'none'
    WHEN (agentTrigger = 26) THEN 'pull'
    WHEN (agentTrigger = 27) THEN 'push'
    WHEN (agentTrigger = 81) THEN 'ar_execute'
    else agentTrigger::text
END as agentTrigger,
LEFT(fulltarget, LENGTH(fulltarget) - POSITION('/' in REVERSE(fulltarget))) as targetFolder, accountName, 
to_timestamp(TRUNC(MIN(event.arrivaltime)/1000)) as oldestArrivalTime, 
Subscription.folder as subscriptionFolder 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, agentType, agentTrigger, LEFT(fulltarget, LENGTH(fulltarget) - POSITION('/' in REVERSE(fulltarget))), accountName, Subscription.folder
ORDER BY 8 ASC, 2 DESC;


Oracle

SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
CASE 
    WHEN (agentType = 3) THEN 'incoming'
    WHEN (agentType = 4) THEN 'outgoing'
    WHEN (agentType = 10) THEN 'servertransfer'
    WHEN (agentType = 11) THEN 'schedule'
    WHEN (agentType = 12) THEN 'transformation'
    WHEN (agentType = 13) THEN 'Application'
    WHEN (agentType = 20) THEN 'Resubmit'
    WHEN (agentType = 28) THEN 'AdvancedRouting'
    WHEN (agentType = 29) THEN 'ICAPScan'
    else CAST(agentType AS CHAR)
END as agentType,
CASE
    WHEN (agentTrigger = 0) THEN 'start'
    WHEN (agentTrigger = 1) THEN 'end'
    WHEN (agentTrigger = 2) THEN 'abort'
    WHEN (agentTrigger = 3) THEN 'error'
    WHEN (agentTrigger = 23) THEN 'none'
    WHEN (agentTrigger = 26) THEN 'pull'
    WHEN (agentTrigger = 27) THEN 'push'
    WHEN (agentTrigger = 81) THEN 'ar_execute'
    else CAST(agentTrigger AS CHAR)
END as agentTrigger,
substr(fulltarget,1, instr(fulltarget,'/',-1)) as targetFolder, accountName, 
TO_CHAR(DATE '1970-01-01' + MIN(arrivaltime) / 86400000, 'YYYY-MM-DD HH24:MI:SS') as oldestArrivalTime, 
Subscription.folder as subscriptionFolder 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, agentType, agentTrigger, substr(fulltarget,1, instr(fulltarget,'/',-1)), accountName, Subscription.folder
ORDER BY 8 ASC, 2 DESC;


MSSQL

SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
CASE 
    WHEN (agentType = 3) THEN 'incoming'
    WHEN (agentType = 4) THEN 'outgoing'
    WHEN (agentType = 10) THEN 'servertransfer'
    WHEN (agentType = 11) THEN 'schedule'
    WHEN (agentType = 12) THEN 'transformation'
    WHEN (agentType = 13) THEN 'Application'
    WHEN (agentType = 20) THEN 'Resubmit'
    WHEN (agentType = 28) THEN 'AdvancedRouting'
    WHEN (agentType = 29) THEN 'ICAPScan'
    else CAST(agentType AS CHAR)
END as agentType,
CASE
    WHEN (agentTrigger = 0) THEN 'start'
    WHEN (agentTrigger = 1) THEN 'end'
    WHEN (agentTrigger = 2) THEN 'abort'
    WHEN (agentTrigger = 3) THEN 'error'
    WHEN (agentTrigger = 23) THEN 'none'
    WHEN (agentTrigger = 26) THEN 'pull'
    WHEN (agentTrigger = 27) THEN 'push'
    WHEN (agentTrigger = 81) THEN 'ar_execute'
    else CAST(agentTrigger AS CHAR)
END as agentTrigger,
LEFT(fulltarget, LEN(fulltarget) - CHARINDEX('/', REVERSE(fulltarget), 0)) as targetFolder, accountName, 
DATEADD(second, cast(MIN(arrivalTime)/1000 as int), convert(datetime, '01/01/1970', 101)) as oldestArrivalTime, 
Subscription.folder as subscriptionFolder 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, agentType, agentTrigger, LEFT(fulltarget, LEN(fulltarget) - CHARINDEX('/', REVERSE(fulltarget), 0)), accountName, Subscription.folder
ORDER BY 8 ASC, 2 DESC;


Comments:

• The status field can have the following values:


0 – Ready. The event is in the queue, waiting for a condition to be met or for resources to become available before it is executed.

1 – Active. The event has an assigned thread and is in progress.

2 – Recovering. Upon a TM restart, events are recovered for reprocessing.


Under normal conditions, only the active (1) and ready (0) statuses are present.


• The eventgroup field represents a subscription and can have a value in one of the following formats:


• For SIT pull <subscriptionId>_subscription_PARTNER-IN

• For SIT push <subscriptionId>.

• REST API events and some PeSIT events do not have an eventgroup assigned.


• The retryCount field is applicable to Server Pulls, Basic Application Pushes, and REST API calls. The global retry mechanism is used.


• The targetFolder field represents the full system path where the file resides for a pull or push. It can be any subscription folder or a subfolder of a subscription folder under the account home folder. It is extracted from fulltarget by removing the last element, which can be a filename or a folder. For Linux, the query uses "/" as the path delimiter. For Windows, change it to "\\".


• If you remove the extraction of the filename/last folder and use the fulltarget field directly in the SELECT and GROUP BY clauses, the grouping is partially removed and some individual events are shown.


• The oldestArrivalTime is calculated from the time when events were generated and inserted into the Event table. This time never changes until the event is processed and removed from the Event table. The value represents the earliest (oldest) event in the list of grouped events.


• The SQL query sorts the results by oldestArrivalTime (oldest first) and then by the number of events (highest first).


• The agentType and agentTrigger fields are decoded for the most common codes. Decoding can be extended in the future if needed. Only specific combinations of the two fields exist, such as incoming start, incoming end, incoming abort, incoming error, outgoing start, outgoing end, outgoing abort, outgoing error, servertransfer pull, servertransfer push, schedule none, AdvancedRouting ar_execute, and etc. This is not a complete list of combinations, only the most common ones.


Sample output of the SQL queries


The full set of events with the maximum meaningful agregation.



The full set of events with details for each event type.




Even better solution

The SQL query above shows all events in the Event table at the time of execution. However, the Event Queue is very dynamic, and events are created and removed very quickly. When events stay in the queue longer, they become subjects of interest. The SQL query above can be enhanced to filter only events older than a specific number of seconds (calculated from the current DB server time). Here are versions of the enhanced SQL query that show events older than a minute (60 seconds).


Filtered set of events with maximum meaningful aggregation.

PostgreSQL

SELECT e.*, TO_CHAR(justify_hours(interval '1 sec' * (CAST(EXTRACT(epoch FROM NOW()) AS INT) - e.oldestArrivalUnixTime)), 'DD" days "HH24:MI:SS') AS arrivedSince 
FROM (
SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
LEFT(fulltarget, LENGTH(fulltarget) - POSITION('/' in REVERSE(fulltarget))) as targetFolder, accountName, 
to_timestamp(TRUNC(MIN(arrivaltime)/1000)) as oldestArrivalTime, 
Subscription.folder as subscriptionFolder, 
MIN(arrivalTime)/1000 AS oldestArrivalUnixTime 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, LEFT(fulltarget, LENGTH(fulltarget) - POSITION('/' in REVERSE(fulltarget))), accountName, Subscription.folder
) e
WHERE (CAST(EXTRACT(epoch FROM NOW()) AS INT) - e.oldestArrivalUnixTime) > 60
ORDER BY 8 ASC, 2 DESC;


Oracle

SELECT e.*, trunc(trunc(sysdate) + numtodsinterval(CAST((CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - DATE'1970-01-01') * 86400 AS INTEGER) - e.oldestArrivalUnixTime, 'second')) - trunc(sysdate) || ' days ' || to_char(trunc(sysdate) + numtodsinterval(CAST((CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - DATE'1970-01-01') * 86400 AS INTEGER) - e.oldestArrivalUnixTime, 'second'), 'hh24:mi:ss') AS arrivedSince 
FROM (
SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
substr(fulltarget,1, instr(fulltarget,'/',-1)) as targetFolder, accountName, 
TO_CHAR(DATE '1970-01-01' + MIN(arrivaltime) / 86400000, 'YYYY-MM-DD HH24:MI:SS') as oldestArrivalTime, 
Subscription.folder as subscriptionFolder, 
MIN(arrivaltime / 1000) as oldestArrivalUnixTime 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, substr(fulltarget,1, instr(fulltarget,'/',-1)), accountName, Subscription.folder
) e
WHERE (CAST((CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - DATE'1970-01-01') * 86400 AS INTEGER) - e.oldestArrivalUnixTime) > 60
ORDER BY 8 ASC, 2 DESC;


MSSQL

SELECT e.*, CAST(FLOOR((DATEDIFF(s, '1970-01-01', GETUTCDATE()) - e.oldestArrivalUnixTime) / 86400) AS VARCHAR(10)) + ' days ' + CONVERT(VARCHAR(8), DATEADD(SECOND, (DATEDIFF(s, '1970-01-01', GETUTCDATE()) - e.oldestArrivalUnixTime), '19000101'), 8) AS arrivedSince 
FROM (
SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
LEFT(fulltarget, LEN(fulltarget) - CHARINDEX('/', REVERSE(fulltarget), 0)) as targetFolder, accountName, 
DATEADD(second, cast(MIN(arrivalTime)/1000 as int), convert(datetime, '01/01/1970', 101)) as oldestArrivalTime, 
Subscription.folder as subscriptionFolder, 
MIN(arrivalTime)/1000 AS oldestArrivalUnixTime 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, LEFT(fulltarget, LEN(fulltarget) - CHARINDEX('/', REVERSE(fulltarget), 0)), accountName, Subscription.folder
) e
WHERE (DATEDIFF(s, '1970-01-01', GETUTCDATE()) - e.oldestArrivalUnixTime) > 60
ORDER BY 8 ASC, 2 DESC;


Filtered set of events with details for each event type.

PostgreSQL

SELECT e.*, TO_CHAR(justify_hours(interval '1 sec' * (CAST(EXTRACT(epoch FROM NOW()) AS INT) - e.oldestArrivalUnixTime)), 'DD" days "HH24:MI:SS') AS arrivedSince 
FROM (
SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
CASE 
    WHEN (agentType = 3) THEN 'incoming'
    WHEN (agentType = 4) THEN 'outgoing'
    WHEN (agentType = 10) THEN 'servertransfer'
    WHEN (agentType = 11) THEN 'schedule'
    WHEN (agentType = 12) THEN 'transformation'
    WHEN (agentType = 13) THEN 'Application'
    WHEN (agentType = 20) THEN 'Resubmit'
    WHEN (agentType = 28) THEN 'AdvancedRouting'
    WHEN (agentType = 29) THEN 'ICAPScan'
    else agentType::text
END as agentType,
CASE
    WHEN (agentTrigger = 0) THEN 'start'
    WHEN (agentTrigger = 1) THEN 'end'
    WHEN (agentTrigger = 2) THEN 'abort'
    WHEN (agentTrigger = 3) THEN 'error'
    WHEN (agentTrigger = 23) THEN 'none'
    WHEN (agentTrigger = 26) THEN 'pull'
    WHEN (agentTrigger = 27) THEN 'push'
    WHEN (agentTrigger = 81) THEN 'ar_execute'
    else agentTrigger::text
END as agentTrigger,
LEFT(fulltarget, LENGTH(fulltarget) - POSITION('/' in REVERSE(fulltarget))) as targetFolder, accountName, 
to_timestamp(TRUNC(MIN(arrivaltime)/1000)) as oldestArrivalTime, 
Subscription.folder as subscriptionFolder, 
MIN(arrivalTime)/1000 AS oldestArrivalUnixTime 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, agentType, agentTrigger, LEFT(fulltarget, LENGTH(fulltarget) - POSITION('/' in REVERSE(fulltarget))), accountName, Subscription.folder
) e
WHERE (CAST(EXTRACT(epoch FROM NOW()) AS INT) - e.oldestArrivalUnixTime) > 60
ORDER BY 8 ASC, 2 DESC;


Oracle

SELECT e.*, trunc(trunc(sysdate) + numtodsinterval(CAST((CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - DATE'1970-01-01') * 86400 AS INTEGER) - e.oldestArrivalUnixTime, 'second')) - trunc(sysdate) || ' days ' || to_char(trunc(sysdate) + numtodsinterval(CAST((CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - DATE'1970-01-01') * 86400 AS INTEGER) - e.oldestArrivalUnixTime, 'second'), 'hh24:mi:ss') AS arrivedSince 
FROM (
SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
CASE 
    WHEN (agentType = 3) THEN 'incoming'
    WHEN (agentType = 4) THEN 'outgoing'
    WHEN (agentType = 10) THEN 'servertransfer'
    WHEN (agentType = 11) THEN 'schedule'
    WHEN (agentType = 12) THEN 'transformation'
    WHEN (agentType = 13) THEN 'Application'
    WHEN (agentType = 20) THEN 'Resubmit'
    WHEN (agentType = 28) THEN 'AdvancedRouting'
    WHEN (agentType = 29) THEN 'ICAPScan'
    else CAST(agentType AS CHAR)
END as agentType,
CASE
    WHEN (agentTrigger = 0) THEN 'start'
    WHEN (agentTrigger = 1) THEN 'end'
    WHEN (agentTrigger = 2) THEN 'abort'
    WHEN (agentTrigger = 3) THEN 'error'
    WHEN (agentTrigger = 23) THEN 'none'
    WHEN (agentTrigger = 26) THEN 'pull'
    WHEN (agentTrigger = 27) THEN 'push'
    WHEN (agentTrigger = 81) THEN 'ar_execute'
    else CAST(agentTrigger AS CHAR)
END as agentTrigger,
substr(fulltarget,1, instr(fulltarget,'/',-1)) as targetFolder, accountName, 
TO_CHAR(DATE '1970-01-01' + MIN(arrivaltime) / 86400000, 'YYYY-MM-DD HH24:MI:SS') as oldestArrivalTime, 
Subscription.folder as subscriptionFolder, 
MIN(arrivaltime / 1000) as oldestArrivalUnixTime 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, agentType, agentTrigger, substr(fulltarget,1, instr(fulltarget,'/',-1)), accountName, Subscription.folder
) e
WHERE (CAST((CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - DATE'1970-01-01') * 86400 AS INTEGER) - e.oldestArrivalUnixTime) > 60
ORDER BY 8 ASC, 2 DESC;


MSSQL

SELECT e.*, CAST(FLOOR((DATEDIFF(s, '1970-01-01', GETUTCDATE()) - e.oldestArrivalUnixTime) / 86400) AS VARCHAR(10)) + ' days ' + CONVERT(VARCHAR(8), DATEADD(SECOND, (DATEDIFF(s, '1970-01-01', GETUTCDATE()) - e.oldestArrivalUnixTime), '19000101'), 8) AS arrivedSince 
FROM (
SELECT clusternode, count(status) as numberOfEvents, status, eventgroup, retryCount, 
CASE 
    WHEN (agentType = 3) THEN 'incoming'
    WHEN (agentType = 4) THEN 'outgoing'
    WHEN (agentType = 10) THEN 'servertransfer'
    WHEN (agentType = 11) THEN 'schedule'
    WHEN (agentType = 12) THEN 'transformation'
    WHEN (agentType = 13) THEN 'Application'
    WHEN (agentType = 20) THEN 'Resubmit'
    WHEN (agentType = 28) THEN 'AdvancedRouting'
    WHEN (agentType = 29) THEN 'ICAPScan'
    else CAST(agentType AS CHAR)
END as agentType,
CASE
    WHEN (agentTrigger = 0) THEN 'start'
    WHEN (agentTrigger = 1) THEN 'end'
    WHEN (agentTrigger = 2) THEN 'abort'
    WHEN (agentTrigger = 3) THEN 'error'
    WHEN (agentTrigger = 23) THEN 'none'
    WHEN (agentTrigger = 26) THEN 'pull'
    WHEN (agentTrigger = 27) THEN 'push'
    WHEN (agentTrigger = 81) THEN 'ar_execute'
    else CAST(agentTrigger AS CHAR)
END as agentTrigger,
LEFT(fulltarget, LEN(fulltarget) - CHARINDEX('/', REVERSE(fulltarget), 0)) as targetFolder, accountName, 
DATEADD(second, cast(MIN(arrivalTime)/1000 as int), convert(datetime, '01/01/1970', 101)) as oldestArrivalTime, 
Subscription.folder as subscriptionFolder, 
MIN(arrivalTime)/1000 AS oldestArrivalUnixTime 
FROM Event
LEFT JOIN Subscription ON subscriptionid=Subscription.id
GROUP BY clusternode, status, eventgroup, retryCount, agentType, agentTrigger, LEFT(fulltarget, LEN(fulltarget) - CHARINDEX('/', REVERSE(fulltarget), 0)), accountName, Subscription.folder
) e
WHERE (DATEDIFF(s, '1970-01-01', GETUTCDATE()) - e.oldestArrivalUnixTime) > 60
ORDER BY 8 ASC, 2 DESC;


Comments:


• Increasing the value 60 in the WHERE clause to something like 3600 (1 hour) helps filter stuck subscriptions and slow transfers of huge files.


• The first line contains a complex expression that converts the time difference between the current time and the event's arrival time to a human-readable format. To get the difference in seconds, you can use the expression from the WHERE clause in its place.


Sample output of the enhanced SQL queries


Filtered set of events with maximum meaningful agregation.



Filtered set of events with details for event types.




I hope you enjoy using SecureTransport 5.5!

Created: February 2024 by Evgeni Evangelov -> Updated: November 2026

Still need help?

If this information wasn't helpful to you, just drop us a line. We'll get back to you as soon as possible.