AFT_LOG Table
The AFT_LOG table, contains information about file transfers that occur when using the Cisco Unified Communications Manager IM and Presence Service managed file transfer feature.
Indexes: "aft_log_pkey" PRIMARY KEY, btree (aft_index)
|
Column Name |
Postgres Datatype |
Oracle Datatype |
Microsoft SQL Datatype |
Not Null |
Description |
|---|---|---|---|---|---|
|
AFT_INDEX |
BIGINT |
NUMBER (19) |
bigint |
Yes |
The sequence number that identifies the transaction. |
|
JID |
VARCHAR (3071) |
VARCHAR2 (3071) |
nvarchar (3071) |
Yes |
The Jabber ID (JID) of the user who uploaded or downloaded a file. The contents of this column depend on the contents of the METHOD column.
|
|
TO_JID |
VARCHAR (3071) |
VARCHAR2 (3071) |
nvarchar (3071) |
Yes |
The JID of the user, group chat, or persistent room that is the intended recipient of the file transfer. |
|
METHOD |
VARCHAR (63) |
VARCHAR2 (63) |
nvarchar (63) |
Yes |
This column can contain either POST, which indicates a user has uploaded a file, or GET, which indicates a user has downloaded a file. |
|
FILENAME |
VARCHAR (511) |
VARCHAR2 (511) |
nvarchar (511) |
Yes |
The resource name for the file that was uploaded or downloaded. The resource name identifies the file in HTTP requests. It is autogenerated by the IM and Presence Service. |
|
REAL_FILENAME |
VARCHAR (511) |
VARCHAR2 (511) |
nvarchar (511) |
Yes |
The actual name of the file that was uploaded by a user. |
|
FILE_TYPE |
VARCHAR (10) |
VARCHAR2 (10) |
nvarchar (10) |
Yes |
The file extension, for example jpg, txt, pptx, docx, and so on. |
|
CHAT_TYPE |
VARCHAR (10) |
VARCHAR2 (10) |
nvarchar (10) |
Yes |
"im" if the file was transferred during a one-to-one IM conversation. "groupchat" if the file was transferred during an ad hoc group chat conversation. "persistent" if the file was transferred to a persistent chat room. |
|
FILE_SERVER |
VARCHAR (511) |
VARCHAR2 (511) |
nvarchar (511) |
Yes |
The hostname or IP address of the file server where the file is stored. |
|
FILE_PATH |
VARCHAR (511) |
VARCHAR2 (511) |
nvarchar (511) |
Yes |
The absolute path to the file (including the file name) on the file server. The file name as stored on the repository is unique and is auto-generated by the IM and Presence Service. |
|
FILESIZE |
BIGINT |
NUMBER (19) |
bigint |
Yes |
The size of the file in bytes. |
|
BYTES_ TRANSFERRED |
BIGINT |
NUMBER (19) |
bigint |
Yes |
The number of bytes that were transferred. This number differs from FILESIZE, only when an error occurred during the transfer. |
|
TIMESTAMPVALUE |
TIMESTAMP |
TIMESTAMP |
timestamp |
Yes |
The date and time (UTC) the file was uploaded or downloaded. |
Sample SQL Queries for the AFT_LOG Table
This section contains some sample SQL queries that you can run on the AFT_LOG table to extract specific information.
All Uploaded Files
The following SQL query returns records of all the files and screen captures that were uploaded using the manged file transfer feature:
SELECT file_path
FROM aft_log
WHERE method = 'Post';
All Files That Were Uploaded to a Specific Recipient
 Note |
Records of downloaded files and screen captures do not contain any data in the to_jid field. |
SELECT file_path
FROM aft_log
WHERE to_jid = '<userid>@<domain>';
All Files That Were Uploaded by a Specific Sender
The following SQL query returns the records of all the files and screen captures that were uploaded by the user <userid> using the managed file transfer feature.
SELECT file_path
FROM aft_log
WHERE jid LIKE '<userid>@<domain>%' AND method = 'Post';
All Files That Were Downloaded by a Specific User
The following SQL query returns the records of all the files and screen captures that were downloaded by the user <userid> using the manged file transfer feature.
SELECT file_path
FROM aft_log
WHERE jid LIKE '<userid>@<domain>%' AND method = 'Get';
All Files That Were Uploaded and Downloaded During IM Conversations
The following SQL query returns the records of all the files and screen captures that were uploaded and downloaded in IM conversations using the managed file transfer feature.
SELECT file_path
FROM aft_log
WHERE chat_type = 'im';
All Files That Were Uploaded by a Specific User After a Specific Time
The following SQL query returns the records of all the files and screen captures that were uploaded by the user <userid> after a specific time using the managed file transfer feature.
SELECT file_path
FROM aft_log
WHERE jid LIKE '<userid>@<domain>%' AND method = 'Post' AND timestampvalue > '2014-12-18 11:58:39';
Sample Output for SQL Queries for the AFT_LOG Table
Sample output from any of these queries looks like this:
/opt/mftFileStore/node_1/files/im/20140811/15/file_name1
/opt/mftFileStore/node_1/files/im/20140811/15/file_name2
/opt/mftFileStore/node_1/files/im/20140811/15/file_name3
/opt/mftFileStore/node_1/files/im/20140811/15/file_name4
...
/opt/mftFileStore/node_1/files/im/20140811/15/file_name99
/opt/mftFileStore/node_1/files/im/20140811/15/file_name100
Using the Output to Clean Up the External File Server
You can use this output with the rm command to remove unwanted files from the external file server. For example, you can run the following commands on the external file server:
rm /opt/mftFileStore/node_1/files/im/20140811/15/file_name1
rm /opt/mftFileStore/node_1/files/im/20140811/15/file_name2
rm /opt/mftFileStore/node_1/files/im/20140811/15/file_name3
and so on.
Feedback