Retrieving data from an audit log

You can create SQL queries to retrieve data from the message or user audit logs. For example, for the OU BANKA and the schema name UDB2ADM1:

To retrieve, from a message audit log, the MQRFH2 header of a message with message ID X'9AF' (in hexadecimal representation):
```
SELECT AM_MQRFH2 FROM UDB2ADM1.DNI_A_MSG_BANKA
 WHERE AM_MSG_ID = X'9AF'
 ORDER BY AM_AUDIT_GMT_TS
```
To retrieve, from a message audit log, the name of the broker for each message that could not be processed successfully:
```
SELECT AM_BROKER FROM UDB2ADM1.DNI_A_MSG_BANKA
 WHERE AM_NODE= DnfBACKOUTBof
 ORDER by AM_AUDIT_GMT_TS
```
To retrieve, from a message audit log, the user ID of each message that corresponds to an LT session operation command (that is, a command issued to the service DNF_ILC_CMD):
```
SELECT AM_USER_ID FROM UDB2ADM1.DNI_A_MSG_BANKA
 WHERE AM_NODE = DnfIlcCmd
 ORDER by AM_AUDIT_GMT_TS
```

To retrieve, from the user audit log, the audit data for all failed attempts to access data:

SELECT * FROM UDB2ADM1.DNI_A_USR_BANKA
 WHERE AU_ACTION_TYPE= AccessFailed
 ORDER by AU_AUDIT_GMT_TS

The retrieved entries are ordered by the date and time that the audit data was recorded.

You can use the view DNI_VW_OU_ou to correlate an entry in a message audit log with the configuration that was used to process the message (see View DNI_VW_OU_ou).

Note: Querying a message audit log can affect the overall performance of your FTM SWIFT instance. A poorly designed query can even lock out other FTM SWIFT services. Therefore, observe the following recommendations:

For your queries, create an index or a small number of indexes on the base table of each message audit log. Otherwise, even a simple query on a message audit log forces Db2® to scan the entire table.