| #

## Report Database Schema

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 MOM/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 MOM Report Counter

    SELECT *
    FROM tblReportCollectionSummary.

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