| #

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