Log Database Schema Parent topic

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
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
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
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