Query status of transfers from Gateway database

Products

• XCOM Data Transport - Windows

Issue/Introduction

Currently, when a file is received via SFTP into the XCOM Gateway and then transmitted via Onward Delivery with XCOM to the Mainframe, the notifications about the status of those transfers are producing an email. We would like to gather the status of each of the transfers using an SQL query in order to create a audit report instead of delivering it via email. How can we accomplish that? 

 

Environment

Release : 12.0

XCOM Data Transport Gateway for Windows

Resolution

1. Setting the Audit option in the Gateway. You would:

 - Logon to the Gateway

- Click the Administration tab

- Click the Global Parameters tab

- Click the Gateway Audits link. 

- In the "File Audit Configuration" section you can move Onward Complete and Onward Details to the right of the screen so that it will audit those records.

2. Create a SQL query to gather the information you need from the following Gateway database tables:

- xcom_gateway_activity -- provides you with the info about the file transfer

- xcom_gateway_file_activity -- provides you with status of the transfer.

Here is a sample SQL query you can use:

Note: The below sample queries work for MySQL. If using other databases, then you may require minor SQL syntax adjustments based on the setup of the database.

use database;

select FILE_NAME, POLICY_ID, ACTIVITY_STATUS from XCOM_GATEWAY_FILE_ACTIVITY, XCOM_GATEWAY_ACTIVITY where XCOM_GATEWAY_FILE_ACTIVITY.FILE_ACTIVITY_DETAIL_ID = XCOM_GATEWAY_ACTIVITY.FILE_ACTIVITY_DETAIL_ID and XCOM_GATEWAY_ACTIVITY.ACTIVITY_STATUS=0;

Note:

ACTIVITY_STATUS = 0 will provide the failed transfers.

ACTIVITY_STATUS = 1 will provide the successful transfers

 

Additional Information

Some further checking on the queries: 

When both ONWARD DETAILS and ONWARD COMPLETE Audits are enabled, there will be duplicate results in the query. You will see two rows for each failure. There are two options to address this: 

1. ONWARD DETAILS Audit is sufficient, so you can remove the ONWARD COMPLETE from Audit. 
2. Modify the query to add an additional check for TRANSFER_NUMBER as below:

Note: The below sample queries work for MySQL. If using other databases, then you may require minor SQL syntax adjustments based on the setup of the database.

select FILE_NAME, POLICY_ID, ACTIVITY_STATUS, FAILURE_REASON, TRANSFER_NUMBER from XCOM_GATEWAY_FILE_ACTIVITY, XCOM_GATEWAY_ACTIVITY where XCOM_GATEWAY_FILE_ACTIVITY.FILE_ACTIVITY_DETAIL_ID= XCOM_GATEWAY_ACTIVITY.FILE_ACTIVITY_DETAIL_ID and XCOM_GATEWAY_ACTIVITY.ACTIVITY_STATUS=0 and XCOM_GATEWAY_FILE_ACTIVITY.TRANSFER_NUMBER!=0;

To further extend the query to filter the time range, you need to add a filter on ACTIVITY_TIME. For example, to get all failures since 1st March 2023, see below: 

select FILE_NAME, POLICY_ID, ACTIVITY_STATUS from XCOM_GATEWAY_FILE_ACTIVITY, XCOM_GATEWAY_ACTIVITY where XCOM_GATEWAY_FILE_ACTIVITY.FILE_ACTIVITY_DETAIL_ID= XCOM_GATEWAY_ACTIVITY.FILE_ACTIVITY_DETAIL_ID and XCOM_GATEWAY_ACTIVITY.ACTIVITY_STATUS=0 and XCOM_GATEWAY_FILE_ACTIVITY.TRANSFER_NUMBER!=0 and XCOM_GATEWAY_ACTIVITY.ACTIVITY_TIME >= '2023-03-01';