| #

## Log Database Schema

The following table stores message information such as the sender, recipient, and message subject.
tblMsgEntries_[Server Name]

#### Table [tblMsgEntries_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
msg_entry_id | Auto increment | Primary key
msg_task_id | int | The scan task this message belongs to
msg_protocol | int | The protocol this message is sent with
msg_found_at | nvarchar(255) | The place where this message was found
msg_source | nvarchar(255) | The semi-colon delimited sender list
msg_destination | nvarchar(255) | The semi-colon delimited recipient list
msg_subject | nvarchar(255) | The subject of this message
msg_delivery_time | datetime | The message delivery time
msg_submit_time | datetime | The message submit time
msg_id | longtext | Message ID

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_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
filter_entry_id | Auto increment | Primary key
msg_entry_id | int | The foreign key for tblMsgEntries_[Server Name]
filter_id | smallint | The id of the filter triggered
filter_rule | nvarchar(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 | int | The virus/malware type for security risk filter, risk level of a malicious URL for Web Reputation filter
filter_engine | nvarchar(32) | The engine version used
filter_pattern | int | The pattern version used
filter_action | int | 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 | datetime | The scan time
filter_original | nvarchar(255) | The original file name that triggered the rule
filter_reason | ntext | Detailed information about how the content is being detected for content violation, malicious URL for Web Reputation filter.
sent_to_csm | smallint | (internal use)
detected_by | int | The scan mechanism that detected the security risk Possible values: 1 - Virus Scan Engine 2 - ATSE 3 - Virtual Analyzer
risk_level | int | The determined risk level for an advanced threat Possible values: 0 - Suspicious (ATSE) 1 - Low 2 - Medium 3 - High 4 - Suspicious (Virtual Analyzer)
url_category | text | The category of the detected URL
atse_aggressive_level | int | ATSE scan level
detected_rule_category | int | ATSE detected rule category
DataContent | longtext | Matched content
entry_uuid | text | The uuid for dtasagent to identify which record needs updating
dda_int_mode | integer | To indicate which integration mode is used: inline mode or monitor mode
dda_coworking_status | integer | DTAS agent working status with Virtual Analyzer like uploading, duplicate checking, querying result, and so on
dda_ui_status | integer | Show the status of sample handling, such as unrated, being analyzed, rated, aborted, and other status on the UI
sent_to_dda_time | datetime | The time of sending sample to Virtual Analyzer server
orgsha1 | text | The SHA1 value of the sample
is_ransomeware | smallint | Indicate whether the threat is ransomware
url_score | integer | The URL score that queried from Web Reputation Service

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

#### Table [tblStorageEntries_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
storage_entry_id | Auto increment | Primary key
msg_entry_id | int | The foreign key for tblMsgEntries_[Server Name]
msg_destination_full | ntext | The full recipient list in XML format
filter_scan_time | datetime | The scan time
filter_entry_id | int | The foreign key for tblFilterEntries_[Server Name]
filter_id | smallint | Filter ID
filter_action | int | 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 | nvarchar(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 | nvarchar(255) | The original file name of this storage
storage_guid | uniqueidentifier | The GUID of this storage entry. (Used by AMF)
storage_reason | smallint | The reason (quarantine, archive, or backup) to make this storage entry.
storage_path | nvarchar(255) | The path the file saved to
storage_type | smallint | The storage type (message part or entire message)
storage_resend_count | smallint | The count of this entity has been resent
sent_to_csm | smallint | (internal use)

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

#### Table [tblActivityEntries_[Server Name]]

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

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

#### Table [tblPatternEngineInfo_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
pei_type | int | The type of the pattern/engine.
pei_current_version | ntext | The current version of pattern/engine.
pei_latest_version | ntext | The latest version of pattern/engine.
pei_last_query_time | datetime | The last query time of pattern/engine.
pei_last_update_time | datetime | The last update time of pattern/engine.
pei_last_successful_update_time | datetime | The last successful update time of pattern/engine.
pei_last_update_status | int | The last update status of pattern/engine.
pei_last_update_status_description | ntext | The last update status description of pattern/engine.

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

#### Table [tblScanningSummary_[Server Name]]

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

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

#### Table [tblSearchResultMessages_[Server Name]]

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_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
cr_session_guid | uniqueidentifier | The session GUID
cr_time | datetime | The start time
cr_server_list | ntext | The server list
cr_selection_list | ntext | The selection list
cr_id | int | (Not in use)

The following table stores the configuration replication status.

#### Table [tblCfgReplicationStatus_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
crs_id | Auto increment | Primary key
crs_session_guid | uniqueidentifier | The session GUID
crs_start_time | datetime | The start time of configuration replication
crs_end_time | datetime | The end time of configuration replication
crs_server | ntext | The server name which did the configuration replication
crs_status | int | The status of the configuration replication
crs_description | ntext | The description of the configuration replication

| ##### Note For Event Tracking log query 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_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
id | Auto increment | Primary key
ServerName | nvarchar(255) | The virtual server name
UserName | nvarchar(255) | The user name
EventTime | datetime | The current time of Audit Event
IpAddress | nvarchar(255) | The remote host IP address
EventType | smallint | The event type (Three types: log in/out, configuration, operation)
SourceType | smallint | 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 | nvarchar(255) | The description of log

The following table is not used.

#### Table [tblManagementGroupList_[Server Name]]

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

The following table is not used.

#### Table [tblManagementServerList_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
msl_id | Auto increment | Primary key
msl_server_name | ntext | The server name in the management group list
msl_group_id | int | The group ID to which the server belongs.

The following table is not used.

#### Table [tblManagementGroupMemberList_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
mgml_id | Auto increment | Primary key
mgml_group_id | int | The group ID from table [tblManagementGroupList_[Server Name]]
mgml_server_id | int | The server ID from table [tblManagementServerList_[Server Name]]

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

#### Table [tblCfgReplicationHistrory_[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
crh_id | Auto increment | Primary key
crh_session_guid | uniqueidentifier | The session GUID
crh_time | datetime | The last time of configuration replication

The following table stores the spam logs.

#### Table [tblSpamLog _[Server Name]]

Field Name | Data Type | Description
--- | --- | ---
filter_entry_id | identity | The entry id of filter
msg_source | text | The message sender
msg_destination_full | longtext | The message recipients
msg_subject | text | The message subject
msg_submit_time | datetime | The message submission time
filter_rule | text | The filter rule
filter_action | integer | The action settings
filter_scan_time | datetime | The scanning time
filter_engine | text | The engine information
filter_pattern | integer | The pattern information

### Example 1: Get event log from table "tblActivityEntries_[Server Name]"

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_[Server Name]
    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_[Server Name] inner join
    tblStorageEntries_[Server Name]
    ON tblMsgEntries_[Server Name].msg_entry_id =
    tblStorageEntries_[Server Name].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_[Server Name] inner join
    tblStorageEntries_[Server Name]
    ON tblMsgEntries_[Server Name].msg_entry_id =
    tblStorageEntries_[Server Name].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_[Server Name] inner join
    tblStorageEntries_[Server Name]
    ON tblMsgEntries_[Server Name].msg_entry_id =
    tblStorageEntries_[Server Name].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_[Server Name]
    WHERE ( EventTime BETWEEN '2008-12-12 09:00:00'
    AND '2008-12-19 09:00:00')
    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_[Server Name] inner join
    tblStorageEntries_[Server Name]
    ON tblMsgEntries_[Server Name].msg_entry_id =
    tblStorageEntries_[Server Name].msg_entry_id
    WHERE storage_entry_id =1;

### Example 7: Get Last Configuration Replication

    SELECT TOP 1 *
    FROM tblCfgReplicationHistory_[Server Name]
    ORDER BY crh_time DESC;

### Example 8: Get Engine Pattern Information

    SELECT *
    FROM tblPatternEngineInfo_[Server Name];

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

    SELECT *
    FROM tblScanningSummary_[Server Name]
    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