| #

## Log Database Schema

The following table stores message information such as the sender, recipient, and message subject.

#### Table [tblMsgEntries]

Field Name | Data Type | Description
--- | --- | ---
msg_entry_id | Auto increment | Primary key
msg_task_id | Number(Long Integer) | The scan task this message belongs to
msg_protocol | Number(Long Integer) | The protocol this message is sent with
msg_found_at | Text(255) | The place where this message was found
msg_source | Text(255) | The semi-colon delimited sender list
msg_destination | Text(255) | The semi-colon delimited recipient list
msg_subject | Text(255) | The subject of this message
msg_delivery_time | Date/Time | The message delivery time
msg_submit_time | Date/Time | The message submit time

The following table stores scan logs that include two types of information. The first type includes information about detected security risks such as the security risk name and the name of the file that was infected. The second type includes information about the filter that detected the security risk.

#### Table [tblFilterEntries]

Field Name | Data Type | Description
--- | --- | ---
filter_entry_id | Auto increment | Primary key
msg_entry_id | Number(Long Integer) | The foreign key for tblMsgEntries
filter_id | Number(Integer) | The id of the filter triggered
filter_rule | Text(64) | The filter rule triggered. Virus/malware name for security risk filter, rule name for content filter, file type blocked by attachment blocking filter (such as.exe), risk level of a malicious URL detected by Web Reputation filter
filter_rule_supplement | Number(Long Integer) | The virus/malware type for security risk filter, risk level of a malicious URL for Web Reputation filter
filter_engine | Text(32) | The engine version used
filter_pattern | Number(Long Integer) | The pattern version used
filter_action | Number(Long Integer) | The result of the action taken. Reference [`action_description.xml`], which is located in `%SMEX_HOME%\ web\xml`.
| ##### Note `%SMEX_HOME%` represents the SMEX installation directory. By default, this is `C:\Program Files\Trend Micro\Smex\`
filter_scan_time | Date/Time | The scan time
filter_original | Text(255) | The original file name that triggered the rule
filter_reason | memo | Detailed information about how the content is being detected for content violation, malicious URL for Web Reputation filter.
sent_to_csm | Number(Integer) | (internal use)
detected_by | Number(Integer) | The scan mechanism that detected the security risk Possible values: 1 - Virus Scan Engine 2 - ATSE 3 - Deep Discovery Advisor
risk_level | Number(Integer) | The determined risk level for an advanced threat Possible values: 0 - Suspicious (ATSE) 1 - Low 2 - Medium 3 - High 4 - Suspicious (Deep Discovery Advisor)

The following table stores information about when the quarantine, archive, or backup action was performed.

#### Table [tblStorageEntries]

Field Name | Data Type | Description
--- | --- | ---
storage_entry_id | Auto increment | Primary key
msg_entry_id | Number(Long Integer) | The foreign key for tblMsgEntries
msg_destination_full | memo | The full recipient list in XML format
filter_scan_time | Date/Time | The scan time
filter_entry_id | Number(Long Integer) | The foreign key for tblFilterEntries
filter_id | Number(Integer) | Filter ID
filter_action | Number(Long Integer) | The result of the action taken. Reference [`action_description.xml`], which is located in `%SMEX_HOME%\ web\xml`.
| ##### Note `%SMEX_HOME%` represents the SMEX installation directory. By default, this is `C:\Program Files\Trend Micro\Smex\`
filter_rule | Text(64) | The filter rule triggered. Virus/malware name for security risk filter, rule name for content filter, file type blocked by attachment blocking filter(such as .exe), risk level of a malicious URL for Web Reputation filter.
file_original | Text(255) | The original file name of this storage
storage_guid | Number Number(Replication ID) | The GUID of this storage entry. (Used by AMF)
storage_reason | Number(Integer) | The reason (quarantine, archive, or backup) to make this storage entry.
storage_path | Text(255) | The path the file saved to
storage_type | Number(Integer) | The storage type (message part or entire message)
storage_resend_count | Number(Integer) | The count of this entity has been resent
sent_to_csm | Number(Integer) | (internal use)

The following table stores event log information. For example, information about the start, progress, and completion of manual update.

#### Table [tblActivityEntries]

Field Name | Data Type | Description
--- | --- | ---
activity_entry_id | Auto increment | Primary key
activity_severity | Number(Long Integer) | The severity of this activity entry
activity_id | Number(Long Integer) | The id of this activity entry. Ref [`dbconf_log.xml`]
activity_time | Date/Time | The date and time of this activity entry began
activity_description | memo | Activity description
activity_parameter | memo | To indicate manual/scheduled update component type: pattern/engine/anti-spam rule
activity_duration_mark | Number(Integer) | To indicate this activity duration is either begin, end, or instant.
sent_to_csm | Number(Integer) | (internal use)

The following table stores information about the engine and patterns that are used to scan email messages.

#### Table [tblPatternEngineInfo]

Field Name | Data Type | Description
--- | --- | ---
pei_type | Number(Long Integer) | The type of the pattern/engine.
pei_current_version | memo | The current version of pattern/engine.
pei_latest_version | memo | The latest version of pattern/engine.
pei_last_query_time | Date/Time | The last query time of pattern/engine.
pei_last_update_time | Date/Time | The last update time of pattern/engine.
pei_last_successful_update_time | Date/Time | The last successful update time of pattern/engine.
pei_last_update_status | Number(Long Integer) | The last update status of pattern/engine.
pei_last_update_status_description | memo | The last update status description of pattern/engine.

The following table stores the scan summary information of detected security risks for today.

#### Table [tblScanningSummary]

Field Name | Data Type | Description
--- | --- | ---
ss_id | Auto increment | Primary key
ss_type | Number(Long Integer) | The type of malicious code (such as virus, spam, blocked attachment) Possible values of the ss_type, reference Note5 of this document.
ss_time | Date/Time | The scanning time
ss_count | Number(Long Integer) | The count of each type of scanned object

The following table stores the summary information of Search & Destroy mailbox searches.

#### Table [tblSearchResultMessages]

Field Name | Data Type | Description
--- | --- | ---
srm_id | Auto increment | Primary key
srm_task_name | text | Task name
srm_msg_id | longtext | Message id
srm_msg_pr_search_key | text | Message primary search key
srm_orig_mbx | longtext | Original mailbox
srm_orig_folder | longtext | Original folder
srm_msg_subject | text | Message subject
srm_msg_recipient | text | Message recipient
srm_msg_sender | text | Message sender
srm_msg_date | datetime | Message date
srm_msg_body | longtext | Message body

The following table stores the configuration replication server list. Perform configuration replication from the Server Management console or Control Manager.

#### Table [tblCfgReplication]

Field Name | Data Type | Description
--- | --- | ---
cr_session_guid | Number Number(Replication ID) | The session GUID
cr_time | Date/Time | The start time
cr_server_list | memo | The server list
cr_selection_list | memo | The selection list
cr_id | Number(Long Integer) | (Not in use)

The following table stores the configuration replication status.

#### Table [tblCfgReplicationStatus]

Field Name | Data Type | Description
--- | --- | ---
crs_id | Auto increment | Primary key
crs_session_guid | Number Number(Replication ID) | The session GUID
crs_start_time | Date/Time | The start time of configuration replication
crs_end_time | Date/Time | The end time of configuration replication
crs_server | memo | The server name which did the configuration replication
crs_status | Number(Long Integer) | The status of the configuration replication
crs_description | memo | The description of the configuration replication

| ##### Note For Event Tracking log query Microsoft Operations Manager (MOM) and System Center Operations Manager (SCOM) will not get the data directly from ScanMail, but the same information can be queried from the ScanMail database.

The following table stores all event tracking logs.

#### Table [tblAuditLog]

Field Name | Data Type | Description
--- | --- | ---
id | Auto increment | Primary key
ServerName | Text(255) | The virtual server name
UserName | Text(255) | The user name
EventTime | Date/Time | The current time of Audit Event
IpAddress | Text(255) | The remote host IP address
EventType | Number(Integer) | The event type (Three types: log in/out, configuration, operation)
SourceType | Number(Integer) | The source type (Three types: Configuration change through the UI(Value:1), Configuration change through Control Manager(Value: 2), Configuration change through Server Management(Value:3))
LogDescription | Text(255) | The description of log

The following table is not used.

#### Table [tblManagementGroupList]

Field Name | Data Type | Description
--- | --- | ---
mgl_id | Auto increment | Primary key
mgl_group_name | memo | The group name in the management group list

The following table is not used.

#### Table [tblManagementServerList]

Field Name | Data Type | Description
--- | --- | ---
msl_id | Auto increment | Primary key
msl_server_name | memo | The server name in the management group list
msl_group_id | Number(Long Integer) | The group ID to which the server belongs.

The following table is not used.

#### Table [tblManagementGroupMemberList]

Field Name | Data Type | Description
--- | --- | ---
mgml_id | Auto increment | Primary key
mgml_group_id | Number(Long Integer) | The group ID from table [tblManagementGroupList]
mgml_server_id | Number(Long Integer) | The server ID from table [tblManagementServerList]

The following table stores the time of the last configuration replication.

#### Table [tblCfgReplicationHistrory]

Field Name | Data Type | Description
--- | --- | ---
crh_id | Auto increment | Primary key
crh_session_guid | Number Number(Replication ID) | The session GUID
crh_time | Date/Time | The last time of configuration replication

### Example 1: Get event log from table "tblActivityEntries"

To query Manual update event between '2008-12-12 09:00:00' AND '2008-12-19 09:00:00':

    SELECT activity_time, activity_description
    FROM tblActivityEntries
    WHERE activity_id = 15
    AND (activity_time  BETWEEN '2008-12-12 09:00:00'
    AND '2008-12-19 09:00:00')
    AND (activity_description LIKE 'Manual update%' )
    ORDER BY activity_time

The following table lists the items to note for this example.

#### Possible Values of the activity_id

Variable | Value | Description
--- | --- | ---
ID_CMD_ENGINE_PATTERN_UPDATE | 1 | The engine pattern update command
ID_CMD_MANUAL_SCAN | 3 | The manual scan command
ID_CMD_SCHEDULE_SCAN | 4 | The schedule scan command
ID_CMD_CFG_DEPLOYMENT | 5 | The configuration deployment command
ID_CMD_CFG_QUERY_PATTERN_ENGINE_VERSION | 6 | The query the pattern engine version command
ID_CMD_QM_RESEND | 7 | The quarantine manager resend message command
ID_CMD_EUQ_CLEAN_SPAM_MSG | 8 | The End User Quarantine (EUQ) clean spam message command
ID_CMD_EUQ_CREATE_SPAM_FOLDER_RULE | 9 | The End User Quarantine (EUQ) create spam folder rule command
ID_CMD_LOG_MAINTENANCE | 10 | The log maintenance command
ID_CMD_EUQ_HOUSE_KEEPING_TASK | 11 | The EUQ house keeping task command
ID_CMD_EUQ_ENABLE_EUQ | 12 | The enable End User Quarantine (EUQ) command
ID_CMD_EUQ_DISABLE_EUQ | 13 | The disable End User Quarantine (EUQ) command
ID_CMD_EUQ_UPDATE_CONFIG | 14 | The update End User Quarantine (EUQ) configuration command
ID_CMD_UPDATE_COMPONENT | 15 | The update component command
ID_CMD_QUERY_LATEST_AU_COMPONENT | 16 | The query latest AU component command
ID_CMD_QUERY_LOCAL_LATEST_AU_COMPONENT | 17 | The query local latest AU component command
ID_CMD_QM_DELETE | 18 | The delete quarantine manager message command
ID_CMD_UPDATE_CLUSTER_COMPONENT | 19 | The update cluster component command

### Example 2: Query:Get Quarantine Log(storage_reason=1)

    SELECT storage_entry_id, filter_scan_time, msg_source,
    msg_destination, msg_subject, filter_id, filter_rule,
    file_original, storage_path as storage_path_quarantine,
    storage_resend_count
    FROM tblMsgEntries inner join tblStorageEntries
    ON tblMsgEntries.msg_entry_id = tblStorageEntries.msg_entry_id
    WHERE (storage_reason = 1 )
    AND (storage_resend_count BETWEEN 0 AND 2)
    AND (filter_id IN ('1','4'))
    AND (filter_scan_time BETWEEN '2008-12-12 09:00:00'
    AND '2008-12-19 09:00:00')
    ORDER BY filter_scan_time

### Example 3: Get Backup Log(storage_reason=2)

    SELECT filter_scan_time, msg_source, msg_destination,
    msg_subject, filter_rule as filter_rule_av, file_original,
    storage_path as storage_path_backup
    FROM tblMsgEntries inner join tblStorageEntries
    ON tblMsgEntries.msg_entry_id = tblStorageEntries.msg_entry_id
    WHERE (storage_reason = 2)
    AND (filter_scan_time BETWEEN '2008-12-12 09:00:00'
    AND '2008-12-19 09:00:00')
    ORDER BY filter_scan_time;

### Example 4: Get Archive Log(storage_reason=3)

    SELECT filter_scan_time, msg_source, msg_destination,
    msg_subject, filter_rule as filter_rule_cf, file_original,
    storage_path as storage_path_archive
    FROM tblMsgEntries inner join tblStorageEntries
    ON tblMsgEntries.msg_entry_id = tblStorageEntries.msg_entry_id
    WHERE (storage_reason = 3)
    AND (filter_scan_time BETWEEN '2008-12-12 09:00:00'
    AND '2008-12-19 09:00:00')
    ORDER BY filter_scan_time;

The following table lists the items to note for this example.

#### Possible Values of the storage_reason

Variable | Value | Description
--- | --- | ---
SR_QUARANTINE | 1 | The reason for why this storage entry is quarantine.
SR_BACKUP | 2 | The reason for why this storage entry is backup.
SR_ARCHIVE | 3 | The reason for why this storage entry is archive.

The following table lists the items to note for this example.

#### Possible Values of the filter_id

Variable | Value | Description
--- | --- | ---
ID_FILTERTYPE_VIRUS_SCANNING | 1(0x1) | The filter type of security risk scan
ID_FILTERTYPE_EMANAGER_5X | 2(0x2) | The filter type emanager_5X
ID_FILTERTYPE_FILE_BLOCKING | 4(0x4) | The filter type of file blocking
ID_FILTERTYPE_ANTISPAM | 8(0x8) | The filter type of spam prevention
ID_FILTERTYPE_SIZE_CHECKER | 16(0x10) | The filter type of size check
ID_FILTERTYPE_ACTIVE_MESSAGE_FILTER | 32(0x20) | Active message filter
ID_FILTERTYPE_UNSCANNABLE_FILTER | 64(0x40) | Unscannable filter
ID_FILTERTYPE_URL_FILTER | 128(0x80) | URL filter
ID_FILTERTYPE_ANTISPAM_ERS | 256(0x100) | Email Reputation spam prevention

### Example 5: Get System Event Log about ‘Realtime Scan’ that occurred between '2008-12-12 09:00:00' AND '2008-12-19 09:00:00'

    SELECT UserName, IpAddress, EventType, LogDescription,
    SourceType, EventTime
    FROM tblAuditLog
    WHERE ( EventTime BETWEEN '2008-12-12 09:00:00'
    AND '2008-12-19 09:00:00')
    AND (EventType in ('1','2'))
    AND LogDescription like '%Realtime Scan%'
    ORDER BY UserName

The following table lists the items to note for this example.

#### Possible Values of the EventType

Variable | Value | Description
--- | --- | ---
TYPE_LOG_IN_OUT | 1 | Log in/out
TYPE_CONFIGURATION | 2 | Configuration
TYPE_OPERATION_EVENT | 3 | Operation event

### Example 6: Get message information that needs to be resent

    SELECT msg_subject, msg_source, msg_destination_full,
    storage_path, storage_path, file_original, storage_type
    FROM tblMsgEntries inner join tblStorageEntries
    ON tblMsgEntries.msg_entry_id = tblStorageEntries.msg_entry_id
    WHERE storage_entry_id =1;

### Example 7: Get All Administrators

    SELECT distinct(username)
    FROM tblAuditLog;

### Example 7: Get Last Configuration Replication

    SELECT TOP 1 *
    FROM tblCfgReplicationHistory
    ORDER BY crh_time DESC;

### Example 8: Get Engine Pattern Information

    SELECT *

    FROM tblPatternEngineInfo;

### Example 9: Get Scanning Summary Count - Blocked attachment

    SELECT *
    FROM tblScanningSummary
    WHERE ss_type = 111;

The following table lists the items to note for this example.

#### Possible Values of the ss_type

Variable | Value | Description
--- | --- | ---
ST_SCANNED_MESSAGE | 100 | Scanned message
ST_DETECTED_VIRUS | 110 | Detected virus
ST_BLOCKED_ATTACHMENT | 111 | Blocked attachment
ST_DETECTED_SPAM | 112 | Detected spam.
ST_CONTENT_VIOLATION | 113 | Content violation
ST_DETECTED_ERS | 114 | Detected ERS
ST_SUSPICIOUS_URL | 115 | Malicious URL
ST_UNCLEANABLE_VIRUS | 117 | Uncleanable virus
ST_SCANNED_ATTACHMENT | 118 | Scanned attachment
ST_UNKNOWN | 119 | Unknown type
ST_DETECTED_PHISH | 120 | Detected phish
ST_DETECTED_SPYWARE | 121 | Detected spyware
ST_FALSE_POSITIVE | 124 | False positive
ST_UNSCANNABLE_ENTITY | 151 | Unscannable entity