Report Database Schema Parent topic

The report database contains nine tables. These tables are not related to each other.
The following table stores the summary detected security risks per hour.

Table [tblSummary_[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
summary_datetime
datetime
This datetime when this record was summarized
summary_total_message_count
int
The total message scanned count for this period
summary_total_attachment_count
int
The total attachment scanned count for this period.
Summary_virus_detected_count
int
The virus/malware count for this period
summary_virus_uncleanable_count
int
The uncleanable virus/malware count for this period
summary_attachment_blocked_count
int
The blocked attachment count for this period
summary_content_filtered_count
int
The filtered-count for this period.
summary_dlp_filtered_count
int
The filtered-count for this period
Summary_spam_detected_count
int
The spam message count
summary_phish_detected_count
int
The phish message count
summary_false_positive_count
int
The reported false positive count
Summary_unscannable_entity_count
int
The unscannable count for this period.
Sent_to_csm
smallint
(internal use)
summary_ers_count
int
Blocked IP count for this period
summary_suspicious_url_count
int
The suspicious URL count shown in the report summary
summary_spyware_detected_count
int
The spyware/grayware count for this period
summary_apt_detected_count
int
The ATSE detections for this period
summary_ransom_detected_count
int
The detected ransomware count
The following table stores blocked attachment information by category.

Table [tblAttachmentInfo_[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
attachinfo_datetime
datetime
The datetime of summarization
attachinfo_cate_id
int
The category of this counter
attachinfo_value
nvarchar(64)
The value of this counter
attachinfo_count
int
The count of this data category
The following table stores content violation information by category.

Table [tblContentInfo_[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
contentinfo_datetime
datetime
The datetime of summarization.
contentinfo_cate_id
int
The category of this counter
contentinfo_value
nvarchar(64)
The value of this counter
contentinfo _count
int
The count of this data category.
The following table stores Data Loss Prevention incident information by category.

Table [tblDLPInfo_[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
dlpinfo_datetime
datetime
The datetime of summarization.
dlpinfo_cate_id
int
The category of this counter
dlpinfo_value
nvarchar(64)
The value of this counter
dlpinfo _count
int
The count of this data category.
The following table stores spam information by category.

Table [tblSpamInfo_[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
spaminfo_datetime
datetime
The date/time of summarization
spaminfo _cate_id
int
The category of this counter
spaminfo _value
nvarchar(64)
The value of this counter
spaminfo _count
int
The count of this data category.
The following table stores security risk information by category.

Table [tblVirusInfo_[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
virusinfo_datetime
datetime
The date/time of summarization
virusinfo_cate_id
int
The category of this counter
virusinfo_value
nvarchar(64)
The value of this counter
virusinfo_count
int
The count of this data category.
The following table stores unscannable message information by category.

Table [tblUnscannableInfo_[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
ucannableifo_datatime
datetime
The datetime of summarization
ucannableifo_cate_id
int
The category of this counter
ucannableifo_value
nvarchar(64)
The value of this counter
ucannableifo_count
int
The count of this data category
The following table stores the total number of detected security risks. This table is used by SCOM

Table [tblReportCollectionSummary_[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
summary_total_message_count
int
The total message scanned count for this period
summary_total_attachment_count
int
The total attachment scanned count for this period
summary_virus_detected_count
int
The virus/malware count for this period
summary_virus_uncleanable_count
int
The uncleanable virus/malware count for this period
summary_attachment_blocked_count
int
The blocked attachment count for this period
summary_content_filtered_count
int
The filtered-count for this period.
summary_dlp_filtered_count
int
The filtered-count for this period.
summary_spam_detected_count
int
The spam message count
summary_phish_detected_count
int
The phish message count
summary_unscannable_entity_count
int
The unscannable count for this period
summary_worm_trojan_virus_type_count
int
The worm trojan virus type count for this period
summary_packed_file_virus_type_count
int
The packed file virus type count for this period
summary_generic_virus_type_count
int
The generic virus/malware type count for this period
summary_virus_virus_type_count
int
The virus/malware type count for this period
summary_other_malicious_code_virus_type_count
int
Other malicious code virus type count for this period
summary_additional_threat_virus_type_count
int
The additional threat virus type count for this period
summary_ers_count
int
Blocked IP count for this period
summary_suspicious_url_count
int
The suspicious URL count shown in the report summary
summary_apt_detected_count
int
The ATSE detections for this period
The following table stores malicious URL information by category.

Table [tblURLInfo_[Server Name]] (add by WTP)

Field Name
Data Type
Description
id
Auto increment
Primary key
urlinfo_datetime
Date time
Date & Time
urlinfo_cate_id
int
Category ID
urlinfo_value
nvarchar(64)
The name of the report item counter
urlinfo_count
int
The value of the report item counter
The following table stores ransomware information by category.

Table [tblRansomeWareInfo _[Server Name]]

Field Name
Data Type
Description
id
Auto increment
Primary key
ransomewareinfo_datetime
Date time
Date & Time
ransomewareinfo_cate_id
int
Category ID
ransomewareinfo_value
text
The value of this counter
ransomewareinfo_count
int
The count of this data category

Example 1: Get Last Summary Time from table[tblSummary_[Server Name]].

SELECT MAX(summary_datetime) AS lastest_datetime
FROM tblSummary_[Server Name];

Example 2: Get SCOM Report Counter

SELECT *
FROM tblReportCollectionSummary_[Server Name].
Note
Note
Examples that follow example 2 all query virus information. Query expressions for ‘attachment blocking reports’, ‘content filter reports’, ‘spam prevention reports’, and ‘unscannable entity reports’ are the same as this example.

Example 3: Get All Virus Count between 12/12/2008 09:00:00’ AND ‘12/19/2008 09:00:00’. (Note: virusinfo_cate_id =151)

SELECT virusinfo_value AS virus_name,
Sum(virusinfo_count) AS virus_count
FROM tblVirusInfo_[Server Name]
WHERE virusinfo_cate_id = 151
AND virusinfo_datetime >= '2008-12-12 09:00:00'
AND virusinfo_datetime <'2008-12-19 09:00:00'
GROUP BY virusinfo_value;

Example 4: Get Virus Summary

SELECT Sum(summary_total_message_count) as total_message_count,
Sum(summary_virus_detected_count) as virus_detected_count,
Sum(summary_virus_uncleanable_count)as virus_uncleanable_count
FROM tblSummary_[Server Name] 
WHERE summary_datetime >= '2008-12-12 09:00:00' 
AND summary_datetime < '2008-12-19 09:00:00';

Example 5: Get Virus Graph By Week

SELECT Min(summary_datetime)as datetime_first,
Sum(summary_total_message_count) as total_message_count, 
Sum(summary_virus_detected_count) as virus_detected_count,
Sum(summary_virus_uncleanable_count) as
virus_uncleanable_count, Max(summary_datetime) as
datetime_last, Year(summary_datetime) as datetime_year,
DatePart("ww",summary_datetime) as datetime_week
FROM tblSummary_[Server Name] 
WHERE summary_datetime  >= '2008-12-12 09:00:00' 
AND summary_datetime < '2008-12-19 09:00:00'
GROUP BY Year(summary_datetime), DatePart("ww",
summary_datetime);

Example 6: Get Virus Graph By Day

SELECT Min(summary_datetime) as datetime_first,
Sum(summary_total_message_count) as total_message_count, 
Sum(summary_virus_detected_count) as virus_detected_count,
Sum(summary_virus_uncleanable_count) as
virus_uncleanable_count,	 Max(summary_datetime) as
datetime_last,	 Year(summary_datetime) as datetime_year,
Month(summary_datetime) as datetime_month,
Day(summary_datetime) as datetime_day
FROM tblSummary_[Server Name] 
WHERE summary_datetime >='2008-12-12 09:00:00' 
AND summary_datetime < '2008-12-19 09:00:00'
GROUP BY Year(summary_datetime), Month(summary_datetime),
Day(summary_datetime);

Example 7: Get Top 3 Viruses (Note: virusinfo_cate_id =151)

SELECT TOP 3 virusinfo_value AS virus_name, 
Sum(virusinfo_count) AS virus_count
FROM tblVirusInfo_[Server Name] 
WHERE virusinfo_cate_id =151 
AND virusinfo_datetime  >='2008-12-12 09:00:00' 
AND virusinfo_datetime < '2008-12-19 09:00:00'
GROUP BY virusinfo_value
ORDER BY Sum(virusinfo_count) DESC;

Example 8: Get Viruses Actions (Note: virusinfo_cate_id =153)

SELECT virusinfo_value AS virus_action, 
Sum(virusinfo_count) AS virus_count
FROM tblVirusInfo_[Server Name] 
WHERE virusinfo_cate_id =153 
AND virusinfo_datetime  >= '2008-12-12 09:00:00' 
AND virusinfo_datetime < '2008-12-19 09:00:00'
GROUP BY virusinfo_value
ORDER BY Sum(virusinfo_count) DESC;

Example 9: Get Virus Types (Note: virusinfo_cate_id =152)

SELECT virusinfo_value AS virus_type,	 
Sum(virusinfo_count) AS virus_count
FROM tblVirusInfo_[Server Name] 
WHERE virusinfo_cate_id =152 
AND virusinfo_datetime  >= '2008-12-12 09:00:00' 
AND virusinfo_datetime < '2008-12-19 09:00:00' 
GROUP BY virusinfo_value
ORDER BY Sum(virusinfo_count) DESC;
The following table lists the items to note for this example.

Possible Values of the virusinfo_cate_id

Variable
Value
Description
RPT_CATEID_VS_VIRUS_NAME
151
The count of viruses/malware of a certain virus name.
RPT_CATEID_VS_VIRUS_TYPE
152
The count of viruses/malware of a certain virus type.
RPT_CATEID_VS_ACTION
153
The count of viruses/malware which were taken the same action.
RPT_CATEID_SPYWARE_NAME
154
The count of spyware of a certain spyware name.
RPT_CATEID_SPYWARE_ACTION
155
The count of spyware which were taken the same action.
RPT_CATEID_VS_SENDER
156
The count of a single sender who sent virus/malware
RPT_CATEID_SPYWARE_SENDER
157
The count of a single sender who sent spyware/grayware
RPT_CATEID_AB_FILETYPE
201
The count of blocked attachment of a certain file type
RPT_CATEID_AB_EXTENSION
202
The count of blocked attachments of a certain extension
RPT_CATEID_AB_FILENAME
203
The count of blocked attachments of a certain filename
RPT_CATEID_CF_SENDER
251
The count for a single sender that triggered the content filtering rules
RPT_CATEID_CF_RECIPIENT
252
The count of content violation of an individual recipient
RPT_CATEID_CF_RULE
253
The count of content violation of a content filtering rule
RPT_CATEID_AS_SPAM_SENDER
301
The count of spam messages from an individual sender
RPT_CATEID_AS_SPAM_DOMAIN
302
The count of spam messages from an individual domain
RPT_CATEID_AS_FALSE_POSITIVE_DOMAIN
303
The count of false positive messages from an individual domain
RPT_CATEID_AS_FALSE_POSITIVE_SENDER
304
The count of false positive messages from an individual sender
RPT_CATEID_AS_SPAM_CATEGORY
305
The count of spam messages of a single spam category
RPT_CATEID_AS_SPAM_MAILBOX
306
The count of spam message to an individual recipient
RPT_CATEID_UNSCANNABLE_ENTITY
351
The count of unscannable messages
RPT_CATEID_UF_SUSPICIOUS_URL
401
The count of malicious URL
RPT_CATEID_UF_SENDER
402
The count of a single sender who sent email messages that contained a malicious URL

Virus Type

Virus Type String
Virus Type ID
Virus
2
Trojan
4
Spyware
16
Joke
8
Test_Virus
8
Other
8
Packer
16384
Generic
32768

Virus Name String

Virus Name String
Protected file
Over restriction (others)
Over restriction (mail entity count)
Over restriction (message body size)
Over restriction (attachment size)
Over restriction (decompressed file count)
Over restriction (decompressed file size)
Over restriction (number of layer of compression)
Over restriction (compression ratio)