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]

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

Table [tblAttachmentInfo]

Field Name
Data Type
Description
id
Auto increment
Primary key
attachinfo_datetime
Date/Time
The datetime of summarization
attachinfo_cate_id
Number(Long Integer)
The category of this counter
attachinfo_value
Text(64)
The value of this counter
attachinfo_count
Number(Long Integer)
The count of this data category
The following table stores content violation information by category.

Table [tblContentInfo]

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

Table [tblDLPInfo]

Field Name
Data Type
Description
id
Auto increment
Primary key
dlpinfo_datetime
Date/Time
The datetime of summarization.
dlpinfo_cate_id
Number(Long Integer)
The category of this counter
dlpinfo_value
Text(64)
The value of this counter
dlpinfo _count
Number(Long Integer)
The count of this data category.
The following table stores spam information by category.

Table [tblSpamInfo]

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

Table [tblVirusInfo]

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

Table [tblUnscannableInfo]

Field Name
Data Type
Description
id
Auto increment
Primary key
ucannableifo_datatime
Date/Time
The datetime of summarization.
ucannableifo_cate_id
Number(Long Integer)
The category of this counter
ucannableifo_value
Text(64)
The value of this counter
ucannableifo_count
Number(Long Integer)
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]

Field Name
Data Type
Description
id
Auto increment
Primary key
summary_total_message_count
Number(Long Integer)
The total message scanned count for this period
summary_total_attachment_count
Number(Long Integer)
The total attachment scanned count for this period
summary_virus_detected_count
Number(Long Integer)
The virus/malware count for this period
summary_virus_uncleanable_count
Number(Long Integer)
The uncleanable virus/malware count for this period
summary_attachment_blocked_count
Number(Long Integer)
The blocked attachment count for this period
summary_content_filtered_count
Number(Long Integer)
The filtered-count for this period.
summary_dlp_filtered_count
Number(Long Integer)
The filtered-count for this period.
summary_spam_detected_count
Number(Long Integer)
The spam message count
summary_phish_detected_count
Number(Long Integer)
The phish message count
summary_unscannable_entity_count
Number(Long Integer)
The unscannable count for this period
summary_worm_trojan_virus_type_count
Number(Long Integer)
The worm trojan virus type count for this period
summary_packed_file_virus_type_count
Number(Long Integer)
The packed file virus type count for this period
summary_generic_virus_type_count
Number(Long Integer)
The generic virus/malware type count for this period
summary_virus_virus_type_count
Number(Long Integer)
The virus/malware type count for this period
summary_other_malicious_code_virus_type_count
Number(Long Integer)
Other malicious code virus type count for this period
summary_additional_threat_virus_type_count
Number(Long Integer)
The additional threat virus type count for this period
summary_ers_count
Number(Long Integer)
Blocked IP count for this period
summary_suspicious_url_count
Number(Long Integer)
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] (add by WTP)

Field Name
Data Type
Description
id
Auto increment
Primary key
urlinfo_datetime
Date time
Date & Time
urlinfo_cate_id
Number(Long Integer)
Category ID
urlinfo_value
Text(64)
The name of the report item counter
urlinfo_count
Number(Long Integer)
The value of the report item counter

Example 1: Get Last Summary Time from table[tblSummary].

SELECT MAX(summary_datetime) AS lastest_datetime
FROM tblSummary;

Example 2: Get SCOM Report Counter

SELECT *
FROM tblReportCollectionSummary.
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
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 
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 
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 
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 
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 
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 
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 tables list 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)