﻿<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE database_configuration [
    <!ENTITY RPT_CATEID_MSG_BASE                    "100">
    <!ENTITY RPT_CATEID_VIRUS_BASE                    "150">
    <!ENTITY RPT_CATEID_ATTACH_BASE                    "200">
    <!ENTITY RPT_CATEID_CONTENT_BASE                "250">
    <!ENTITY RPT_CATEID_SPAM_BASE                    "300">
    <!ENTITY RPT_CATEID_UNSCANNABLE_BASE    "350">
    <!ENTITY RPT_CATEID_URL_BASE          "400">
    <!ENTITY RPT_CATEID_DLP_BASE          "450">

    <!ENTITY RPT_CATEID_VS_VIRUS_NAME                "(150 + 1)">
    <!ENTITY RPT_CATEID_VS_VIRUS_TYPE                "(150 + 2)">
    <!ENTITY RPT_CATEID_VS_ACTION                    "(150 + 3)">

    <!ENTITY RPT_CATEID_SPYWARE_NAME                "(150 + 4)">
    <!ENTITY RPT_CATEID_SPYWARE_ACTION                "(150 + 5)">
    <!ENTITY RPT_CATEID_VS_SENDER                    "(150 + 6)">
    <!ENTITY RPT_CATEID_SPYWARE_SENDER                "(150 + 7)">

    <!ENTITY RPT_CATEID_AB_FILETYPE                    "(200 + 1)">
    <!ENTITY RPT_CATEID_AB_EXTENSION                "(200 + 2)">
    <!ENTITY RPT_CATEID_AB_FILENAME                    "(200 + 3)">
    <!ENTITY RPT_CATEID_AB_SENDER                    "(200 + 4)">
    <!ENTITY RPT_CATEID_AB_RULE                    "(200 + 5)">

    <!ENTITY RPT_CATEID_CF_SENDER                    "(250 + 1)">
    <!ENTITY RPT_CATEID_CF_RECIPIENT                "(250 + 2)">
    <!ENTITY RPT_CATEID_CF_RULE                        "(250 + 3)">

    <!ENTITY RPT_CATEID_AS_SPAM_SENDER                "(300 + 1)">
    <!ENTITY RPT_CATEID_AS_SPAM_DOMAIN                "(300 + 2)">
    <!ENTITY RPT_CATEID_AS_FALSE_POSITIVE_DOMAIN    "(300 + 3)">
    <!ENTITY RPT_CATEID_AS_FALSE_POSITIVE_SENDER     "(300 + 4)">
    <!ENTITY RPT_CATEID_AS_SPAM_CATEGORY            "(300 + 5)">
    <!ENTITY RPT_CATEID_AS_SPAM_MAILBOX            "(300 + 6)">

    <!ENTITY RPT_CATEID_UNSCANNABLE_ENTITY        "(350 + 1)">
    <!ENTITY RPT_CATEID_UF_SUSPICIOUS_URL     "(400 + 1)">
    <!ENTITY RPT_CATEID_UF_SENDER                "(400 + 2)">
    
    <!ENTITY RPT_CATEID_DLP_SENDER                    "(450 + 1)">
    <!ENTITY RPT_CATEID_DLP_RECIPIENT                "(450 + 2)">
    <!ENTITY RPT_CATEID_DLP_RULE                    "(450 + 3)">
    <!ENTITY RPT_CATEID_DLP_TEMPLATE                "(450 + 4)">

  <!ENTITY SERVER_NAME             SYSTEM       "dbcfg_ServerName.txt">
  <!ENTITY PRODUCT_INSTALL_PATH    SYSTEM       "dbcfg_ProductInstallPath.txt">
  <!--ENTITY DATABASE_NAME                        "PPReport_&SERVER_NAME;"-->
  <!ENTITY DATABASE_NAME          SYSTEM        "dbcfg_ReportDatabaseName.txt">
  <!ENTITY POSTFIX                SYSTEM        "dbcfg_Postfix.txt">
]>
<database_configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="DBConf.xsd">
    <database_type>SQLSVR</database_type>
    <category>log</category>
    <database_name>&DATABASE_NAME;</database_name>
    <table_postfix>&POSTFIX;</table_postfix>
    <database_installation>
        <sql>
            USE [&DATABASE_NAME;]
        </sql>
        <schema_definition src="dbSchema_Report.xml"/>
    </database_installation>
  
    <database_uninstallation>
	    <schema_definition src="dbSchema_Report.xml"/>
		<sql>
		    IF EXISTS (SELECT * FROM fn_listextendedproperty(N'PP_Created', N'SCHEMA', N'dbo', default, default, default, default))
			   AND NOT EXISTS (select * from sys.sysobjects where xtype = 'U')
			BEGIN
                USE master
                ALTER DATABASE [&DATABASE_NAME;] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
                DROP DATABASE [&DATABASE_NAME;]
			END
		</sql>
    </database_uninstallation>

    <database_upgrade>

    </database_upgrade>
    
    <query_set>
        <db_cmd name="GetLastSummaryTime" type="select">
            <cmd_text>SELECT MAX(summary_datetime) AS lastest_datetime FROM tblSummary&POSTFIX;;</cmd_text>
        </db_cmd>
        <db_cmd name="InsertSummary" type="insert">
            <cmd_text>INSERT INTO tblSummary&POSTFIX; (summary_datetime, summary_total_message_count, summary_total_attachment_count,
            summary_virus_detected_count, summary_virus_uncleanable_count, summary_attachment_blocked_count, summary_content_filtered_count, summary_dlp_filtered_count,
            summary_spam_detected_count, summary_phish_detected_count, summary_false_positive_count, summary_unscannable_entity_count, summary_ers_count, summary_suspicious_url_count, summary_spyware_detected_count) VALUES
            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);</cmd_text>
            <parameter name="summary_datetime"/>
            <parameter name="summary_total_message_count"/>
            <parameter name="summary_total_attachment_count"/>
            <parameter name="summary_virus_detected_count"/>
            <parameter name="summary_virus_uncleanable_count"/>
            <parameter name="summary_attachment_blocked_count"/>
            <parameter name="summary_content_filtered_count"/>
            <parameter name="summary_dlp_filtered_count"/>
            <parameter name="summary_spam_detected_count"/>
            <parameter name="summary_phish_detected_count"/>
            <parameter name="summary_false_positive_count"/>
            <parameter name="summary_unscannable_entity_count"/>
            <parameter name="summary_ers_count"/>
            <parameter name="summary_suspicious_url_count" />
            <parameter name="summary_spyware_detected_count"/>
        </db_cmd>
        <db_cmd name="InsertPatternEngineInfo" type="insert">
            <cmd_text>INSERT INTO tblPatternEngineInfo&POSTFIX; (pei_type, pei_current_version, pei_latest_version, pei_last_update_time, pei_last_upate_status, pei_last_update_status_description) VALUES (?, ?, ?, ?, ?, ?);</cmd_text>
            <parameter name="pei_type"/>
            <parameter name="pei_current_version"/>
            <parameter name="pei_latest_version"/>
            <parameter name="pei_last_update_time"/>
            <parameter name="pei_last_upate_status"/>
            <parameter name="pei_last_update_status_description"/>
        </db_cmd>
        <db_cmd name="InsertSpamInfo" type="insert">
            <cmd_text>INSERT INTO tblSpamInfo&POSTFIX; (spaminfo_datetime, spaminfo_cate_id, spaminfo_value, spaminfo_count) VALUES (?, ?, ?, ?);</cmd_text>
            <parameter name="spaminfo_datetime"/>
            <parameter name="spaminfo_cate_id"/>
            <parameter name="spaminfo_value" size="64"/>
            <parameter name="spaminfo_count"/>
        </db_cmd>
        <db_cmd name="InsertContentInfo" type="insert">
            <cmd_text>INSERT INTO tblContentInfo&POSTFIX; (contentinfo_datetime, contentinfo_cate_id, contentinfo_value, contentinfo_count) VALUES (?, ?, ?, ?);</cmd_text>
            <parameter name="contentinfo_datetime"/>
            <parameter name="contentinfo_cate_id"/>
            <parameter name="contentinfo_value" size="64"/>
            <parameter name="contentinfo_count"/>
        </db_cmd>
        <db_cmd name="InsertDLPInfo" type="insert">
            <cmd_text>INSERT INTO tblDLPInfo&POSTFIX; (dlpinfo_datetime, dlpinfo_cate_id, dlpinfo_value, dlpinfo_count) VALUES (?, ?, ?, ?);</cmd_text>
            <parameter name="dlpinfo_datetime"/>
            <parameter name="dlpinfo_cate_id"/>
            <parameter name="dlpinfo_value" size="64"/>
            <parameter name="dlpinfo_count"/>
        </db_cmd>
        <db_cmd name="InsertAttachmentInfo" type="insert">
            <cmd_text>INSERT INTO tblAttachmentInfo&POSTFIX; (attachinfo_datetime, attachinfo_cate_id, attachinfo_value, attachinfo_count) VALUES (?, ?, ?, ?);</cmd_text>
            <parameter name="attachinfo_datetime"/>
            <parameter name="attachinfo_cate_id"/>
            <parameter name="attachinfo_value" size="255"/>
            <parameter name="attachinfo_count"/>
        </db_cmd>
        <db_cmd name="InsertVirusInfo" type="insert">
            <cmd_text>INSERT INTO tblVirusInfo&POSTFIX; (virusinfo_datetime, virusinfo_cate_id, virusinfo_value, virusinfo_count) VALUES (?, ?, ?, ?);</cmd_text>
            <parameter name="virusinfo_datetime"/>
            <parameter name="virusinfo_cate_id"/>
            <parameter name="virusinfo_value" size="64"/>
            <parameter name="virusinfo_count"/>
        </db_cmd>
        <db_cmd name="InsertUnscannableInfo" type="insert">
            <cmd_text>INSERT INTO tblUnscannableInfo&POSTFIX; (unscannableinfo_datetime, unscannableinfo_cate_id, unscannableinfo_value, unscannableinfo_count) VALUES (?, ?, ?, ?);</cmd_text>
            <parameter name="unscannableinfo_datetime"/>
            <parameter name="unscannableinfo_cate_id"/>
            <parameter name="unscannableinfo_value" size="64"/>
            <parameter name="unscannableinfo_count"/>
        </db_cmd>
        <db_cmd name="InsertURLInfo" type="insert">
              <cmd_text>INSERT INTO tblURLInfo&POSTFIX; (urlinfo_datetime, urlinfo_cate_id, urlinfo_value, urlinfo_count) VALUES (?, ?, ?, ?);</cmd_text>
              <parameter name="urlinfo_datetime"/>
              <parameter name="urlinfo_cate_id"/>
              <parameter name="urlinfo_value" size="64"/>
              <parameter name="urlinfo_count"/>
            </db_cmd>
        <!--***************************************** 
            Report generation query commands 
        ******************************************-->
        <db_cmd name="GetAllVirusCount" type="select">
            <cmd_text>SELECT 
                    virusinfo_value             AS virus_name,
                    Sum(virusinfo_count)    AS virus_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_VS_VIRUS_NAME;
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAllSpywareCount" type="select">
            <cmd_text>SELECT 
                    virusinfo_value             AS spyware_name,
                    Sum(virusinfo_count)    AS spyware_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_SPYWARE_NAME;
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
                
        <!--***************************************** 
            Report generation query commands 
        ******************************************-->
        <!--*****************************************
             System summary commands
        ******************************************-->
        <db_cmd name="GetSystemSummary" type="select">
            <cmd_text>SELECT *
                FROM tblPatternEngineInfo&POSTFIX;
            </cmd_text>
        </db_cmd>
        <db_cmd name="GetScanSummary" type="select">
            <cmd_text>SELECT 
                SUM(summary_virus_detected_count)     as virus_detected_count,
                SUM(summary_virus_uncleanable_count)  as virus_uncleanable_count,
                SUM(summary_spyware_detected_count)   as spyware_detected_count, 
                SUM(summary_attachment_blocked_count) as attachment_blocked_count,
                SUM(summary_content_filtered_count)   as content_filtered_count,
                SUM(summary_dlp_filtered_count)   as dlp_filtered_count, 
                SUM(summary_phish_detected_count)     as phish_detected_count, 
                SUM(summary_false_positive_count)     as false_positive_count,
                SUM(summary_total_message_count)      as total_message_count,
                SUM(summary_total_attachment_count)   as total_attachment_count,
                SUM(summary_unscannable_entity_count) as unscannable_entity_count,
                SUM(summary_suspicious_url_count)     as url_detected_count
                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? and summary_datetime &lt; ?
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <!--*****************************************
             Traffic report commands
        ******************************************-->
        <db_cmd name="GetTrafficByHour" type="select">
            <cmd_text>SELECT 
                Sum(tblSummary&POSTFIX;.summary_total_message_count) AS total_message_count_by_hour, 
                Min(summary_datetime) AS datetime_first,
                Max(summary_datetime) AS datetime_last,
                Year(summary_datetime) AS datetime_year, 
                Month(summary_datetime) AS datetime_month, 
                Day(summary_datetime) AS datetime_day, 
                DatePart(hour, summary_datetime) AS datetime_hour
                FROM tblSummary&POSTFIX;
                WHERE (((tblSummary&POSTFIX;.summary_datetime) &gt;= ? AND (tblSummary&POSTFIX;.summary_datetime) &lt; ?))
                GROUP BY Year(summary_datetime), Month(summary_datetime), Day(summary_datetime), DatePart(hour, summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetTrafficByDay" type="select">
            <cmd_text>SELECT 
                Sum(tblSummary&POSTFIX;.summary_total_message_count) AS total_message_count_by_hour, 
                Min(summary_datetime) AS datetime_first,
                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&POSTFIX;
                WHERE (((tblSummary&POSTFIX;.summary_datetime) &gt;= ? And (tblSummary&POSTFIX;.summary_datetime) &lt; ?))
                GROUP BY Year(summary_datetime), Month(summary_datetime), Day(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetTrafficByWeek" type="select">
            <cmd_text>SELECT 
                Sum(tblSummary&POSTFIX;.summary_total_message_count) AS total_message_count_by_hour, 
                Min(summary_datetime) AS datetime_first,
                Max(summary_datetime) AS datetime_last,
                Year(summary_datetime) AS datetime_year, 
                DatePart("ww", summary_datetime) AS datetime_weekofyear 
                FROM tblSummary&POSTFIX;
                WHERE (((tblSummary&POSTFIX;.summary_datetime) &gt;= ? And (tblSummary&POSTFIX;.summary_datetime) &lt; ?))
                GROUP BY Year(summary_datetime), DatePart("ww", summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <!--*****************************************
             Virus report commands
        ******************************************-->
        <db_cmd name="GetVirusSummary" type="select">
            <cmd_text>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,
                Sum(summary_spyware_detected_count)     as spyware_detected_count
                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetVirusGraphByHour" type="select">
            <cmd_text>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,
                Sum(summary_spyware_detected_count)     as spyware_detected_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,
                DatePart(hour, summary_datetime)                    as datetime_hour

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime), 
                    DatePart(hour, summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetVirusGraphByDay" type="select">
            <cmd_text>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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetVirusGraphByWeek" type="select">
            <cmd_text>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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    DatePart("ww", summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetVirusGraphByMonth" type="select">
            <cmd_text>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

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetTopViruses" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    virusinfo_value AS virus_name,
                    Sum(virusinfo_count) AS virus_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_VS_VIRUS_NAME; 
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value
                ORDER BY Sum(virusinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetVirusTopSender" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    virusinfo_value        AS virus_sender,
                    Sum(virusinfo_count)    AS virus_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_VS_SENDER;
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value
                ORDER BY Sum(virusinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        
        <db_cmd name="GetVirusActions" type="select">
            <cmd_text>SELECT 
                    virusinfo_value             AS virus_action,
                    Sum(virusinfo_count)    AS virus_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_VS_ACTION;
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value
                ORDER BY Sum(virusinfo_count) DESC;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetVirusTypes" type="select">
            <cmd_text>SELECT 
                    virusinfo_value             AS virus_type,
                    Sum(virusinfo_count)    AS virus_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_VS_VIRUS_TYPE;
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value
                ORDER BY Sum(virusinfo_count) DESC;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        
        <!--****************************************** 
            Spyware report query commands 
        *******************************************-->
        <db_cmd name="GetSpywareGraphByHour" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_spyware_detected_count)        as spyware_detected_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,
                DatePart(hour, summary_datetime)                    as datetime_hour

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime), 
                    DatePart(hour, summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpywareGraphByDay" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_spyware_detected_count)        as spyware_detected_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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpywareGraphByWeek" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_spyware_detected_count)        as spyware_detected_count,
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                DatePart("ww", summary_datetime)        as datetime_week

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    DatePart("ww", summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpywareGraphByMonth" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_spyware_detected_count)        as spyware_detected_count,
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                Month(summary_datetime)                    as datetime_month

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetTopSpywares" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    virusinfo_value AS spyware_name,
                    Sum(virusinfo_count) AS spyware_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_SPYWARE_NAME; 
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value
                ORDER BY Sum(virusinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpywareTopSender" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    virusinfo_value        AS spyware_sender,
                    Sum(virusinfo_count)    AS spyware_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_SPYWARE_SENDER;
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value
                ORDER BY Sum(virusinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        
        <db_cmd name="GetSpywareActions" type="select">
            <cmd_text>SELECT 
                    virusinfo_value        AS spyware_action,
                    Sum(virusinfo_count)    AS spyware_count
                FROM tblVirusInfo&POSTFIX; 
                WHERE virusinfo_cate_id = &RPT_CATEID_SPYWARE_ACTION;
                    AND virusinfo_datetime &gt;= ? AND virusinfo_datetime &lt; ?
                GROUP BY virusinfo_value
                ORDER BY Sum(virusinfo_count) DESC;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        
        <!--****************************************** 
            Attachment blocking report query commands 
        *******************************************-->
        <db_cmd name="GetAttachSummary" type="select">
            <cmd_text>SELECT 
                Sum(summary_total_attachment_count)        as total_attachment_count,
                Sum(summary_attachment_blocked_count)    as attach_blocked_count 
                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachGraphByHour" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_attachment_count)        as total_attachment_count,
                Sum(summary_attachment_blocked_count)    as attachment_blocked_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,
                DatePart(hour, summary_datetime)                    as datetime_hour

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime), 
                    DatePart(hour, summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachGraphByDay" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_attachment_count)     as total_attachment_count, 
                Sum(summary_attachment_blocked_count)    as attachment_blocked_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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachGraphByWeek" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_attachment_count)     as total_attachment_count, 
                Sum(summary_attachment_blocked_count)    as attachment_blocked_count,
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                DatePart("ww", summary_datetime)        as datetime_week

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    DatePart("ww", summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachGraphByMonth" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_attachment_count)     as total_attachment_count, 
                Sum(summary_attachment_blocked_count)    as attachment_blocked_count,
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                Month(summary_datetime)                    as datetime_month

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachTopType" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    attachinfo_value        AS attach_type,
                    Sum(attachinfo_count)    AS attach_count
                FROM tblAttachmentInfo&POSTFIX; 
                WHERE attachinfo_cate_id = &RPT_CATEID_AB_FILETYPE;
                    AND attachinfo_datetime &gt;= ? AND attachinfo_datetime &lt; ?
                GROUP BY attachinfo_value
                ORDER BY Sum(attachinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachTopExt" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    attachinfo_value        AS attach_ext,
                    Sum(attachinfo_count)    AS attach_count
                FROM tblAttachmentInfo&POSTFIX; 
                WHERE attachinfo_cate_id = &RPT_CATEID_AB_EXTENSION;
                    AND attachinfo_datetime &gt;= ? AND attachinfo_datetime &lt; ?
                GROUP BY attachinfo_value
                ORDER BY Sum(attachinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachTopFilename" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    attachinfo_value        AS attach_filename,
                    Sum(attachinfo_count)    AS attach_count
                FROM tblAttachmentInfo&POSTFIX; 
                WHERE attachinfo_cate_id = &RPT_CATEID_AB_FILENAME;
                    AND attachinfo_datetime &gt;= ? AND attachinfo_datetime &lt; ?
                GROUP BY attachinfo_value
                ORDER BY Sum(attachinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachTopSender" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    attachinfo_value        AS sender,
                    Sum(attachinfo_count)    AS attach_count
                FROM tblAttachmentInfo&POSTFIX; 
                WHERE attachinfo_cate_id = &RPT_CATEID_AB_SENDER;
                    AND attachinfo_datetime &gt;= ? AND attachinfo_datetime &lt; ?
                GROUP BY attachinfo_value
                ORDER BY Sum(attachinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetAttachTopRule" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    attachinfo_value        AS rulename,
                    Sum(attachinfo_count)    AS attach_count
                FROM tblAttachmentInfo&POSTFIX; 
                WHERE attachinfo_cate_id = &RPT_CATEID_AB_RULE;
                    AND attachinfo_datetime &gt;= ? AND attachinfo_datetime &lt; ?
                GROUP BY attachinfo_value
                ORDER BY Sum(attachinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <!--****************************************** 
            Content filter report query commands 
        *******************************************-->
        <db_cmd name="GetContentSummary" type="select">
            <cmd_text>SELECT 
                Sum(summary_total_message_count)        as total_message_count, 
                Sum(summary_content_filtered_count)        as content_filtered_count 
                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetContentGraphByHour" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_content_filtered_count)        as content_filtered_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,
                DatePart(hour, summary_datetime)                    as datetime_hour

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime), 
                    DatePart(hour, summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetContentGraphByDay" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_content_filtered_count)        as content_filtered_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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetContentGraphByWeek" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_content_filtered_count)        as content_filtered_count, 
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                DatePart("ww", summary_datetime)        as datetime_week

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    DatePart("ww", summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetContentGraphByMonth" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_content_filtered_count)        as content_filtered_count,
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                Month(summary_datetime)                    as datetime_month

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetContentTopSender" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    contentinfo_value        AS sender,
                    Sum(contentinfo_count)    AS filtered_count
                FROM tblContentInfo&POSTFIX; 
                WHERE contentinfo_cate_id = &RPT_CATEID_CF_SENDER;
                    AND contentinfo_datetime &gt;= ? AND contentinfo_datetime &lt; ?
                GROUP BY contentinfo_value
                ORDER BY Sum(contentinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetContentTopRecipient" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    contentinfo_value        AS recipient,
                    Sum(contentinfo_count)    AS filtered_count
                FROM tblContentInfo&POSTFIX; 
                WHERE contentinfo_cate_id = &RPT_CATEID_CF_RECIPIENT;
                    AND contentinfo_datetime &gt;= ? AND contentinfo_datetime &lt; ?
                GROUP BY contentinfo_value
                ORDER BY Sum(contentinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetContentTopRule" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    contentinfo_value        AS rulename,
                    Sum(contentinfo_count)    AS filtered_count
                FROM tblContentInfo&POSTFIX; 
                WHERE contentinfo_cate_id = &RPT_CATEID_CF_RULE;
                    AND contentinfo_datetime &gt;= ? AND contentinfo_datetime &lt; ?
                GROUP BY contentinfo_value
                ORDER BY Sum(contentinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <!--****************************************** 
            DLP filter report query commands 
        *******************************************-->
        <db_cmd name="GetDLPSummary" type="select">
            <cmd_text>SELECT 
                Sum(summary_total_message_count)        as total_message_count, 
                Sum(summary_dlp_filtered_count)        as dlp_filtered_count 
                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetDLPGraphByHour" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_dlp_filtered_count)        as dlp_filtered_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,
                DatePart(hour, summary_datetime)                    as datetime_hour

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime), 
                    DatePart(hour, summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetDLPGraphByDay" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_dlp_filtered_count)        as dlp_filtered_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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetDLPGraphByWeek" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_dlp_filtered_count)        as dlp_filtered_count, 
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                DatePart("ww", summary_datetime)        as datetime_week

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    DatePart("ww", summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetDLPGraphByMonth" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_dlp_filtered_count)        as dlp_filtered_count,
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                Month(summary_datetime)                    as datetime_month

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetDLPTopSender" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    dlpinfo_value        AS sender,
                    Sum(dlpinfo_count)    AS filtered_count
                FROM tblDLPInfo&POSTFIX; 
                WHERE dlpinfo_cate_id = &RPT_CATEID_DLP_SENDER;
                    AND dlpinfo_datetime &gt;= ? AND dlpinfo_datetime &lt; ?
                GROUP BY dlpinfo_value
                ORDER BY Sum(dlpinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetDLPTopRecipient" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    dlpinfo_value        AS recipient,
                    Sum(dlpinfo_count)    AS filtered_count
                FROM tblDLPInfo&POSTFIX; 
                WHERE dlpinfo_cate_id = &RPT_CATEID_DLP_RECIPIENT;
                    AND dlpinfo_datetime &gt;= ? AND dlpinfo_datetime &lt; ?
                GROUP BY dlpinfo_value
                ORDER BY Sum(dlpinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetDLPTopRule" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    dlpinfo_value        AS rulename,
                    Sum(dlpinfo_count)    AS filtered_count
                FROM tblDLPInfo&POSTFIX; 
                WHERE dlpinfo_cate_id = &RPT_CATEID_DLP_RULE;
                    AND dlpinfo_datetime &gt;= ? AND dlpinfo_datetime &lt; ?
                GROUP BY dlpinfo_value
                ORDER BY Sum(dlpinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetDLPTopTemplate" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    dlpinfo_value        AS templatename,
                    Sum(dlpinfo_count)    AS filtered_count
                FROM tblDLPInfo&POSTFIX; 
                WHERE dlpinfo_cate_id = &RPT_CATEID_DLP_TEMPLATE;
                    AND dlpinfo_datetime &gt;= ? AND dlpinfo_datetime &lt; ?
                GROUP BY dlpinfo_value
                ORDER BY Sum(dlpinfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <!--****************************************** 
            Anti-spam report query commands 
        *******************************************-->
        <db_cmd name="GetSpamSummary" type="select">
            <cmd_text>SELECT 
                Sum(summary_total_message_count)            as total_message_count, 
                Sum(summary_spam_detected_count)            as spam_detected_count,
                Sum(summary_phish_detected_count)            as phish_detected_count,
                Sum(summary_false_positive_count)            as false_positive_count,
                Sum(summary_ers_count)                                as ers_count
                
                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpamGraphByHour" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_spam_detected_count)        as spam_detected_count,
                Sum(summary_false_positive_count)         as false_positive_count,
                Sum(summary_ers_count)                                as ers_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,
                DatePart(hour, summary_datetime)                    as datetime_hour

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime), 
                    DatePart(hour, summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpamGraphByDay" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_spam_detected_count)        as spam_detected_count,
                Sum(summary_false_positive_count)         as false_positive_count,
                Sum(summary_ers_count)                                as ers_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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpamGraphByWeek" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_spam_detected_count)        as spam_detected_count,
                Sum(summary_false_positive_count)         as false_positive_count,
                Sum(summary_ers_count)                                as ers_count,
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                DatePart("ww", summary_datetime)        as datetime_week

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    DatePart("ww", summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpamGraphByMonth" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_total_message_count)         as total_message_count, 
                Sum(summary_spam_detected_count)        as spam_detected_count,
                Sum(summary_false_positive_count)         as false_positive_count,
                Sum(summary_ers_count)                                as ers_count,
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                Month(summary_datetime)                    as datetime_month

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpamTopSender" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    spaminfo_value        AS spam_sender,
                    Sum(spaminfo_count)    AS spam_count
                FROM tblSpamInfo&POSTFIX; 
                WHERE spaminfo_cate_id = &RPT_CATEID_AS_SPAM_SENDER;
                    AND spaminfo_datetime &gt;= ? AND spaminfo_datetime &lt; ?
                GROUP BY spaminfo_value
                ORDER BY Sum(spaminfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>

        <db_cmd name="GetSpamTopMailbox" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    spaminfo_value        AS spam_mailbox,
                    Sum(spaminfo_count)    AS spam_count
                FROM tblSpamInfo&POSTFIX; 
                WHERE spaminfo_cate_id = &RPT_CATEID_AS_SPAM_MAILBOX;
                    AND spaminfo_datetime &gt;= ? AND spaminfo_datetime &lt; ?
                GROUP BY spaminfo_value
                ORDER BY Sum(spaminfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>        
        <db_cmd name="GetSpamTopDomain" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    spaminfo_value        AS spam_domain,
                    Sum(spaminfo_count)    AS spam_count
                FROM tblSpamInfo&POSTFIX; 
                WHERE spaminfo_cate_id = &RPT_CATEID_AS_SPAM_DOMAIN; 
                    AND spaminfo_datetime &gt;= ? AND spaminfo_datetime &lt; ?
                GROUP BY spaminfo_value
                ORDER BY Sum(spaminfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpamTopFalsePositiveSender" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    spaminfo_value        AS fp_sender,
                    Sum(spaminfo_count)    AS fp_count
                FROM tblSpamInfo&POSTFIX; 
                WHERE spaminfo_cate_id = &RPT_CATEID_AS_FALSE_POSITIVE_SENDER;
                    AND spaminfo_datetime &gt;= ? AND spaminfo_datetime &lt; ?
                GROUP BY spaminfo_value
                ORDER BY Sum(spaminfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpamTopFalsePositiveDomain" type="select">
            <cmd_text>SELECT TOP ? WITH TIES
                    spaminfo_value        AS fp_domain,
                    Sum(spaminfo_count)    AS fp_count
                FROM tblSpamInfo&POSTFIX; 
                WHERE spaminfo_cate_id = &RPT_CATEID_AS_FALSE_POSITIVE_DOMAIN;
                    AND spaminfo_datetime &gt;= ? AND spaminfo_datetime &lt; ?
                GROUP BY spaminfo_value
                ORDER BY Sum(spaminfo_count) DESC;
            </cmd_text>
            <parameter name="top_n"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetSpamCategories" type="select">
            <cmd_text>SELECT 
                    spaminfo_value        AS categories,
                    Sum(spaminfo_count)    AS spam_count
                FROM tblSpamInfo&POSTFIX; 
                WHERE spaminfo_cate_id = &RPT_CATEID_AS_SPAM_CATEGORY;
                    AND spaminfo_datetime &gt;= ? AND spaminfo_datetime &lt; ?
                GROUP BY spaminfo_value
                ORDER BY Sum(spaminfo_count) DESC;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <!--****************************************** 
            Unscannable Entity report query commands 
        *******************************************-->
        <db_cmd name="GetUnscannableSummary" type="select">
            <cmd_text>SELECT 
                Sum(summary_unscannable_entity_count)        as unscannable_entity_count 
                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?;
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>    
        <db_cmd name="GetUnscannableGraphByHour" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_unscannable_entity_count)         as unscannable_entity_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,
                DatePart(hour, summary_datetime)                    as datetime_hour

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime), 
                    DatePart(hour, summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>            
        <db_cmd name="GetUnscannableGraphByDay" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_unscannable_entity_count)         as unscannable_entity_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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime), 
                    Day(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetUnscannableGraphByWeek" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_unscannable_entity_count)         as unscannable_entity_count, 
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                DatePart("ww", summary_datetime)        as datetime_week

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    DatePart("ww", summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>        
        <db_cmd name="GetUnscannableGraphByMonth" type="select">
            <cmd_text>SELECT 
                Min(summary_datetime)                    as datetime_first,
                Sum(summary_unscannable_entity_count)         as unscannable_entity_count, 
                
                Max(summary_datetime)                    as datetime_last,
                Year(summary_datetime)                    as datetime_year,
                Month(summary_datetime)                    as datetime_month

                FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
                GROUP BY 
                    Year(summary_datetime), 
                    Month(summary_datetime);
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
        </db_cmd>
        <!--****************************************** 
                URL Filtering report query commands 
            *******************************************-->
        <db_cmd name="GetURLSummary" type="select">
          <cmd_text>
            SELECT
            Sum(summary_total_message_count)        as total_message_count,
            Sum(summary_suspicious_url_count)      as suspicious_url_count
            FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?;
          </cmd_text>
          <parameter name="begin_time"/>
          <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetURLGraphByHour" type="select">
          <cmd_text>
            SELECT
            Min(summary_datetime)                    as datetime_first,
            Sum(summary_total_message_count)        as total_message_count,
            Sum(summary_suspicious_url_count)        as suspicious_url_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,
            DatePart(hour, summary_datetime)        as datetime_hour

            FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
            GROUP BY
            Year(summary_datetime),
            Month(summary_datetime),
            Day(summary_datetime),
            DatePart(hour, summary_datetime);
          </cmd_text>
          <parameter name="begin_time"/>
          <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetURLGraphByDay" type="select">
          <cmd_text>
            SELECT
            Min(summary_datetime)                    as datetime_first,
            Sum(summary_total_message_count)        as total_message_count,
            Sum(summary_suspicious_url_count)      as suspicious_url_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&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
            GROUP BY
            Year(summary_datetime),
            Month(summary_datetime),
            Day(summary_datetime);
          </cmd_text>
          <parameter name="begin_time"/>
          <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetURLGraphByWeek" type="select">
          <cmd_text>
            SELECT
            Min(summary_datetime)                    as datetime_first,
            Sum(summary_total_message_count)        as total_message_count,
            Sum(summary_suspicious_url_count)      as suspicious_url_count,

            Max(summary_datetime)                    as datetime_last,
            Year(summary_datetime)                    as datetime_year,
            DatePart("ww", summary_datetime)        as datetime_week

            FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
            GROUP BY
            Year(summary_datetime),
            DatePart("ww", summary_datetime);
          </cmd_text>
          <parameter name="begin_time"/>
          <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetURLGraphByMonth" type="select">
          <cmd_text>
            SELECT
            Min(summary_datetime)                    as datetime_first,
            Sum(summary_total_message_count)        as total_message_count,
            Sum(summary_suspicious_url_count)      as suspicious_url_count,

            Max(summary_datetime)                    as datetime_last,
            Year(summary_datetime)                    as datetime_year,
            Month(summary_datetime)                    as datetime_month

            FROM tblSummary&POSTFIX; WHERE summary_datetime &gt;= ? AND summary_datetime &lt; ?
            GROUP BY
            Year(summary_datetime),
            Month(summary_datetime);
          </cmd_text>
          <parameter name="begin_time"/>
          <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetURLTopURLCount" type="select">
          <cmd_text>SELECT TOP ? WITH TIES urlinfo_value AS suspicious_url, Sum(urlinfo_count) AS suspicious_url_count FROM tblURLInfo&POSTFIX; WHERE urlinfo_cate_id = &RPT_CATEID_UF_SUSPICIOUS_URL; AND urlinfo_datetime &gt;= ? AND urlinfo_datetime &lt; ? GROUP BY urlinfo_value ORDER BY Sum(urlinfo_count) DESC;</cmd_text>
          <parameter name="top_n"/>
          <parameter name="begin_time"/>
          <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="GetURLTopSender" type="select">
          <cmd_text>SELECT TOP ? WITH TIES
          urlinfo_value AS uf_sender, 
          Sum(urlinfo_count) AS top_suspicious_url_count 
          FROM tblURLInfo&POSTFIX; 
          WHERE urlinfo_cate_id = &RPT_CATEID_UF_SENDER; 
          AND urlinfo_datetime &gt;= ? AND urlinfo_datetime &lt; ? 
          GROUP BY urlinfo_value 
          ORDER BY Sum(urlinfo_count) DESC;
          </cmd_text>
          <parameter name="top_n"/>
          <parameter name="begin_time"/>
          <parameter name="end_time"/>
        </db_cmd>
        <db_cmd name="DeleteSummary" type="delete">
            <cmd_text>DELETE FROM tblSummary&POSTFIX;             WHERE summary_datetime &lt; ?;</cmd_text>
            <parameter name="before_datetime"/>
        </db_cmd>
        <db_cmd name="DeleteVirusInfo" type="delete">
            <cmd_text>DELETE FROM tblVirusInfo&POSTFIX;        WHERE virusinfo_datetime &lt; ?;</cmd_text>
            <parameter name="before_datetime"/>
        </db_cmd>
        <db_cmd name="DeleteAttachmentInfo" type="delete">
            <cmd_text>DELETE FROM tblAttachmentInfo&POSTFIX;    WHERE attachinfo_datetime &lt; ?;</cmd_text>
            <parameter name="before_datetime"/>
        </db_cmd>
        <db_cmd name="DeleteContentInfo" type="delete">
            <cmd_text>DELETE FROM tblContentInfo&POSTFIX;        WHERE contentinfo_datetime &lt; ?;</cmd_text>
            <parameter name="before_datetime"/>
        </db_cmd>
        <db_cmd name="DeleteDLPInfo" type="delete">
            <cmd_text>DELETE FROM tblDLPInfo&POSTFIX;        WHERE dlpinfo_datetime &lt; ?;</cmd_text>
            <parameter name="before_datetime"/>
        </db_cmd>
        <db_cmd name="DeleteSpamInfo" type="delete">
            <cmd_text>DELETE FROM tblSpamInfo&POSTFIX;            WHERE spaminfo_datetime &lt; ?;</cmd_text>
            <parameter name="before_datetime"/>
        </db_cmd>
        <db_cmd name="DeleteUnscannableInfo" type="delete">
            <cmd_text>DELETE FROM tblUnscannableInfo&POSTFIX;            WHERE unscannableinfo_datetime &lt; ?;</cmd_text>
            <parameter name="before_datetime"/>
        </db_cmd>
        <db_cmd name="DeleteURLInfo" type="delete">
              <cmd_text>DELETE FROM tblURLInfo&POSTFIX;    WHERE urlinfo_datetime &lt; ?;</cmd_text>
              <parameter name="before_datetime"/>
        </db_cmd>
    <!--****************************************** 
            CSM query commands
        *******************************************-->
    <db_cmd name="CSMGetSummaryLog" type="select">
      <cmd_text>SELECT * FROM tblSummary&POSTFIX; where sent_to_csm = ? ORDER BY summary_datetime ASC</cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMUpdateSummaryLogSentStatus" type="update">
      <cmd_text>UPDATE tblSummary&POSTFIX; SET sent_to_csm = ? WHERE id = ?</cmd_text>
      <parameter name="sent_to_csm"/>
      <parameter name="entry_id"/>
    </db_cmd>
    <db_cmd name="CSMDeleteSentSummaryLogs" type="delete">
      <cmd_text>DELETE * FROM tblSummary&POSTFIX; WHERE sent_to_csm = 1;</cmd_text>
    </db_cmd>
    <!--****************************************** 
            MOM report counter query commands (Total M)
        *******************************************-->
    <db_cmd name="GetMOMReportCounter" type="select">
      <cmd_text>SELECT * FROM tblReportCollectionSummary&POSTFIX;</cmd_text>
    </db_cmd>
    <db_cmd name="UpdateMOMReportCounter" type="update">
      <cmd_text>UPDATE tblReportCollectionSummary&POSTFIX; SET summary_total_message_count = ?, summary_total_attachment_count = ?, summary_virus_detected_count = ?, summary_virus_uncleanable_count = ?, summary_attachment_blocked_count = ?, summary_content_filtered_count = ?, summary_dlp_filtered_count = ?, summary_spam_detected_count = ?, summary_phish_detected_count = ?, summary_unscannable_entity_count = ?, summary_worm_trojan_virus_type_count = ?, summary_packed_file_virus_type_count = ?, summary_generic_virus_type_count = ?, summary_virus_virus_type_count = ?, summary_other_malicious_code_virus_type_count = ?, summary_additional_threat_virus_type_count = ? ,summary_ers_count = ? ,summary_suspicious_url_count = ? WHERE id = ?</cmd_text>
      <parameter name="summary_total_message_count"/>
      <parameter name="summary_total_attachment_count"/>
      <parameter name="summary_virus_detected_count"/>
      <parameter name="summary_virus_uncleanable_count"/>
      <parameter name="summary_attachment_blocked_count"/>
      <parameter name="summary_content_filtered_count"/>
      <parameter name="summary_dlp_filtered_count"/>
      <parameter name="summary_spam_detected_count"/>
      <parameter name="summary_phish_detected_count"/>
      <parameter name="summary_unscannable_entity_count"/>
      <parameter name="summary_worm_trojan_virus_type_count"/>
      <parameter name="summary_packed_file_virus_type_count"/>
      <parameter name="summary_generic_virus_type_count"/>
      <parameter name="summary_virus_virus_type_count"/>
      <parameter name="summary_other_malicious_code_virus_type_count"/>
      <parameter name="summary_additional_threat_virus_type_count"/>
      <parameter name="summary_ers_count"/>
      <parameter name="summary_suspicious_url_count"/>
      <parameter name="id"/>
    </db_cmd>
  </query_set>
</database_configuration>
