﻿<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE database_configuration [
    <!ENTITY ID_FILTER_UNKOWN    "0">
    <!ENTITY ID_FILTER_AV         "1">
    <!ENTITY ID_FILTER_CF         "2">
    <!ENTITY ID_FILTER_AB         "4">
	<!ENTITY ID_FILTER_DLP			"8">
    <!ENTITY ID_FILTER_US         "64">
    <!ENTITY ID_FILTER_WTP         "256">

    <!ENTITY SR_UNKNOWN            "0">
    <!ENTITY SR_QUARANTINE        "1">
    <!ENTITY SR_BACKUP            "2">
    <!ENTITY SR_ARCHIVE            "3">

    <!ENTITY SF_UNKONWN            "0">
    <!ENTITY SF_ENTIRE_MESSAGE    "1">
    <!ENTITY SF_ATTACHMENT        "2">
    <!ENTITY SF_MESSAGE_BODY    "3">
    
    <!ENTITY ID_ACTIVITY_UPDATE                "15">
    <!ENTITY ID_ACTIVITY_SCAN_TASK_MANUAL    "3">
    <!ENTITY ID_ACTIVITY_SCAN_TASK_SCHEDULE    "4">

  <!ENTITY SERVER_NAME            SYSTEM       "dbcfg_ServerName.txt"> 
  <!ENTITY PRODUCT_INSTALL_PATH   SYSTEM       "dbcfg_ProductInstallPath.txt">
  <!--ENTITY DATABASE_NAME                       "PPLog_&SERVER_NAME;"-->
  <!ENTITY DATABASE_NAME          SYSTEM       "dbcfg_LogDatabaseName.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_Log.xml"/>
    </database_installation>
  
    <database_uninstallation>
	    <schema_definition src="dbSchema_Log.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="InsertMsgLog" type="insert">
            <cmd_text>INSERT INTO tblMsgEntries&POSTFIX; 
            (msg_source, msg_destination, msg_found_at, msg_subject, msg_submit_time, msg_delivery_time, msg_task_id, msg_protocol) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?);
            </cmd_text>
            <parameter name="msg_source" size="255"/>
            <parameter name="msg_destination" size="255"/>
            <parameter name="msg_found_at" size="255"/>
            <parameter name="msg_subject" size="255"/>
            <parameter name="msg_submit_time"/>
            <parameter name="msg_delivery_time"/>
            <parameter name="msg_task_id"/>
            <parameter name="msg_protocol"/>
        </db_cmd>
        <db_cmd name="InsertFilterLog" type="insert">
            <cmd_text>INSERT INTO tblFilterEntries&POSTFIX;  
            (msg_entry_id, filter_id, filter_rule, filter_rule_supplement, filter_engine, filter_pattern, filter_action, filter_scan_time, filter_reason,
            file_original) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,?);
            </cmd_text>
            <parameter name="msg_entry_id"/>
            <parameter name="filter_id"/>
            <parameter name="filter_rule" size="255"/>
            <parameter name="filter_rule_supplement"/>
            <parameter name="filter_engine" size="32"/>
            <parameter name="filter_pattern"/>
            <parameter name="filter_action"/>
            <parameter name="filter_scan_time"/>
            <parameter name="filter_reason" size="255"/>
            <parameter name="file_original" size="255"/>
        </db_cmd>
        <db_cmd name="InsertStorageLog" type="insert">
            <cmd_text>INSERT INTO tblStorageEntries&POSTFIX;
            (msg_entry_id, msg_destination_full, filter_scan_time, filter_entry_id, filter_id, 
            filter_action, filter_rule, file_original, storage_reason, storage_path, storage_type, storage_guid)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) </cmd_text>
            <parameter name="msg_entry_id"/>
            <parameter name="msg_destination_full"/>
            <parameter name="filter_scan_time"/>
            <parameter name="filter_entry_id"/>
            <parameter name="filter_id"/>
            <parameter name="filter_action"/>
            <parameter name="filter_rule" size="255"/>
            <parameter name="file_original" size="255"/>
            <parameter name="storage_reason"/>
            <parameter name="storage_path" size="255"/>
            <parameter name="storage_type"/>
            <parameter name="storage_guid"/>
        </db_cmd>
        <db_cmd name="GetQuarantinePathByGUID" type="select">
      <cmd_text>
        Select storage_path from tblStorageEntries&POSTFIX; where storage_guid=?
      </cmd_text>
      <parameter name="storage_guid"/>
    </db_cmd>
    <db_cmd name="UpdateQuarantinePathByGUID" type="update">
      <cmd_text>
        Update tblStorageEntries&POSTFIX; set storage_path=?, storage_type=1 where storage_guid=?
      </cmd_text>
      <parameter name="storage_path"/>
      <parameter name="storage_guid"/>
    </db_cmd>
        <db_cmd name="InsertActivityLog" type="insert">
            <cmd_text>INSERT INTO tblActivityEntries&POSTFIX;
            (activity_severity, activity_id, activity_time, activity_description, activity_parameters, activity_duration_mark)
            VALUES (?, ?, ?, ?, ?, ?)</cmd_text>
            <parameter name="activity_severity"/>
            <parameter name="activity_id"/>
            <parameter name="activity_time"/>
            <parameter name="activity_description"/>
            <parameter name="activity_parameters"/>
            <parameter name="activity_duration_mark"/>
        </db_cmd>
    <db_cmd name="InsertEventTrackingLog" type="insert">
      <cmd_text>
        INSERT INTO tblAuditLog&POSTFIX;
        (ServerName, UserName, EventTime, IPAddress, EventType, SourceType, LogDescription)
        VALUES (?, ?, ?, ?, ?, ?, ?)
      </cmd_text>
      <parameter name="ServerName"/>
      <parameter name="Username"/>
      <parameter name="EventTime"/>
      <parameter name="IPaddress"/>
      <parameter name="EventType"/>
      <parameter name="SourceType"/>
      <parameter name="LogDescription"/>
    </db_cmd>    
        <db_cmd name="GetResendInfo" type="select">
            <cmd_text>SELECT msg_subject, msg_source, msg_destination_full, storage_path, storage_path, file_original, storage_type  
            FROM tblMsgEntries&POSTFIX; inner join tblStorageEntries&POSTFIX; ON tblMsgEntries&POSTFIX;.msg_entry_id = tblStorageEntries&POSTFIX;.msg_entry_id 
            WHERE storage_entry_id = ?;</cmd_text>
            <parameter name="storage_entry_id"/>
        </db_cmd>
        <db_cmd name="UpdateResendCount" type="update">
            <cmd_text>UPDATE tblStorageEntries&POSTFIX; SET 
                tblStorageEntries&POSTFIX;.storage_resend_count = tblStorageEntries&POSTFIX;.storage_resend_count + 1
                WHERE tblStorageEntries&POSTFIX;.storage_entry_id = ?;
            </cmd_text>
            <parameter name="storage_entry_id"/>
        </db_cmd>
        <db_cmd name="DeleteStorageEntry" type="delete">
            <cmd_text>DELETE FROM tblStorageEntries&POSTFIX; WHERE storage_entry_id = ?;</cmd_text>
            <parameter name="storage_entry_id"></parameter>
        </db_cmd>
    <db_cmd name="GetStorageLog" type="select">
      <cmd_text>
        SELECT * FROM vwMsgStorageEntries&POSTFIX; WHERE filter_scan_time BETWEEN ? AND ? 
                ORDER BY ? 
      </cmd_text>
      <parameter name="begin_time"/>
      <parameter name="end_time"/>
      <parameter name="order_by_field"/>
    </db_cmd>
        <db_cmd name="GetAVLog" type="select">
            <cmd_text>SELECT * FROM vwAVLogs&POSTFIX; WHERE filter_scan_time BETWEEN ? AND ? 
                -- ORDER BY ? </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!--parameter name="order_by_field"/-->
        </db_cmd>
        <db_cmd name="GetCFLog" type="select">
            <cmd_text>SELECT * FROM vwCFLogs&POSTFIX; WHERE filter_scan_time BETWEEN ? AND ? 
                -- ORDER BY ? </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!--parameter name="order_by_field"/-->
        </db_cmd>
        <db_cmd name="GetABLog" type="select">
            <cmd_text>SELECT * FROM vwABLogs&POSTFIX; WHERE filter_scan_time BETWEEN ? AND ?
                -- ORDER BY ? </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!--parameter name="order_by_field"/-->
        </db_cmd>
		<db_cmd name="GetDLPLog" type="select">
            <cmd_text>SELECT * FROM vwDLPLogs&POSTFIX; WHERE filter_scan_time BETWEEN ? AND ?
                        -- ORDER BY ?
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!--parameter name="order_by_field"/-->
        </db_cmd>
        <db_cmd name="GetWTPLog" type="select">
            <cmd_text>SELECT * FROM vwWTPLogs&POSTFIX; WHERE filter_scan_time BETWEEN ? AND ?
                        -- ORDER BY ? 
            </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!--parameter name="order_by_field"/-->
        </db_cmd>
        <db_cmd name="GetQuarantineLog" type="select">
            <cmd_text>SELECT 
                storage_entry_id, filter_scan_time, msg_source, msg_destination, msg_subject, filter_id, filter_rule, file_original, storage_path as storage_path_quarantine, storage_resend_count
                FROM tblMsgEntries&POSTFIX; inner join tblStorageEntries&POSTFIX; ON tblMsgEntries&POSTFIX;.msg_entry_id = tblStorageEntries&POSTFIX;.msg_entry_id
                WHERE (storage_reason = &SR_QUARANTINE; )
                AND (storage_resend_count BETWEEN ? AND ?)
                AND (filter_id IN (?, ?, ?, ?, ?, ?, ?))
                AND (filter_scan_time BETWEEN ? AND ?) -- ORDER BY ? </cmd_text>
            <parameter name="resend_count_min"/>
            <parameter name="resend_count_max"/>
            <parameter name="filter_id_AV"/>
            <parameter name="filter_id_AB"/>
            <parameter name="filter_id_AS"/>
            <parameter name="filter_id_CF"/>
            <parameter name="filter_id_US"/>
            <parameter name="filter_id_WTP"/>
			<parameter name="filter_id_DLP"/>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!--parameter name="order_by_field"/-->
        </db_cmd>
        <db_cmd name="GetArchiveLog" type="select">
            <cmd_text>SELECT filter_scan_time, msg_source, msg_destination, msg_subject, filter_rule as filter_rule_cf, file_original, storage_path as storage_path_archive
                FROM tblMsgEntries&POSTFIX; inner join tblStorageEntries&POSTFIX; ON tblMsgEntries&POSTFIX;.msg_entry_id = tblStorageEntries&POSTFIX;.msg_entry_id
                WHERE (storage_reason = &SR_ARCHIVE;)
                AND (filter_scan_time BETWEEN ? AND ?)
                -- ORDER BY ?; </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!--parameter name="order_by_field"/-->
        </db_cmd>
        <db_cmd name="GetBackupLog" type="select">
            <cmd_text>SELECT storage_entry_id, filter_scan_time, msg_source, msg_destination, msg_subject, filter_rule as filter_rule_av, file_original, msg_found_at, storage_path as storage_path_backup
                FROM tblMsgEntries&POSTFIX; inner join tblStorageEntries&POSTFIX; ON tblMsgEntries&POSTFIX;.msg_entry_id = tblStorageEntries&POSTFIX;.msg_entry_id
                WHERE (storage_reason = &SR_BACKUP;)
                AND (filter_scan_time BETWEEN ? AND ?) 
                -- ORDER BY ? </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!-- parameter name="order_by_field"/ -->
        </db_cmd>
        <db_cmd name="GetUpdateLog" type="select">
            <cmd_text>SELECT activity_time, activity_description FROM tblActivityEntries&POSTFIX; 
                WHERE (activity_id = &ID_ACTIVITY_UPDATE;)
                AND (activity_time BETWEEN ? AND ? )
                AND (activity_description LIKE ? )
                -- ORDER BY ?</cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <parameter name="keyword"/>
            <!-- parameter name="order_by_field"/-->
        </db_cmd>
        <db_cmd name="GetEventLog" type="select">
            <cmd_text>SELECT activity_time, activity_description FROM tblActivityEntries&POSTFIX; 
                WHERE (activity_id = &ID_ACTIVITY_SCAN_TASK_MANUAL; OR activity_id = &ID_ACTIVITY_SCAN_TASK_SCHEDULE; )
                AND (activity_time BETWEEN ? AND ?)
                AND (activity_description LIKE ? )
                -- ORDER BY ? </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <parameter name="keyword"/>
            <!-- parameter name="order_by_field"/ -->
        </db_cmd>
        <db_cmd name="GetUSLog" type="select">
            <cmd_text>SELECT * FROM vwUSLogs&POSTFIX; WHERE filter_scan_time BETWEEN ? AND ? 
                -- ORDER BY ? </cmd_text>
            <parameter name="begin_time"/>
            <parameter name="end_time"/>
            <!--parameter name="order_by_field"/-->
        </db_cmd>
        <db_cmd name="GetEventTrackingLog" type="select">
      <cmd_text>
        SELECT storage_entry_id, UserName, EventTime, IpAddress, EventType, SourceType, LogDescription FROM tblAuditLog&POSTFIX; WHERE ( EventTime BETWEEN ? AND ?)
                AND (%s)
                AND IPaddress like ?
                AND (EventType in (%s))
                AND (SourceType in (%s))
                AND LogDescription like ?
                -- ORDER BY ? 
      </cmd_text>
      <parameter name="begin_time"/>
      <parameter name="end_time"/>
      <!--<parameter name="name"/>-->
      <parameter name="IpAddress"/>
      <!--<parameter name="EventType"/>-->
      <parameter name="Description"/>
      <!--parameter name="order_by_field"/-->
    </db_cmd>    
        <!-- Quarantine item deletion -->
    <db_cmd name="DeleteQuarantineItemByGUID" type="delete">
        <cmd_text>DELETE FROM tblStorageEntries&POSTFIX; WHERE tblStorageEntries&POSTFIX;.storage_guid = ?;</cmd_text>
        <parameter name="storage_guid"/>
    </db_cmd>
    <!-- Log & quarantine maintenance commands -->
    <db_cmd name="DeleteDanglingMsgEntries" type="delete">
        <cmd_text>DELETE FROM tblMsgEntries&POSTFIX; 
        WHERE tblMsgEntries&POSTFIX;.msg_entry_id NOT IN (SELECT msg_entry_id FROM tblFilterEntries&POSTFIX;) 
        AND tblMsgEntries&POSTFIX;.msg_entry_id NOT IN (SELECT msg_entry_id FROM tblStorageEntries&POSTFIX;);
        </cmd_text>
    </db_cmd>
    <db_cmd name="DeleteMsgEntries" type="delete">
        <cmd_text>DELETE FROM tblMsgEntries&POSTFIX;
        WHERE tblMsgEntries&POSTFIX;.msg_entry_id = ?;</cmd_text>
        <parameter name="msg_entry_id"/>
    </db_cmd>
    <db_cmd name="DeleteFilterEntries" type="delete">
        <cmd_text>DELETE FROM tblFilterEntries&POSTFIX; WHERE filter_id = ? AND filter_scan_time &lt; ?;</cmd_text>
        <parameter name="filter_id"/>
        <parameter name="end_time"/>
    </db_cmd>
    <db_cmd name="DeleteStorageEntries" type="delete">
        <cmd_text>DELETE FROM tblStorageEntries&POSTFIX; 
        WHERE storage_reason = ? 
        AND storage_resend_count BETWEEN ? AND ?
        AND filter_scan_time &lt; ?;</cmd_text>
        <parameter name="storage_reason"/>
        <parameter name="resend_count_min"/>
        <parameter name="resend_count_max"/>
        <parameter name="end_time"/>
    </db_cmd>
    <db_cmd name="DeleteActivityEntries" type="delete">
        <cmd_text>DELETE FROM tblActivityEntries&POSTFIX; WHERE activity_id = ? AND activity_time &lt; ?;</cmd_text>
        <parameter name="activity_id"/>
        <parameter name="end_time"/>
    </db_cmd>
    <db_cmd name="DeleteEventTrackingLog" type="delete">
      <cmd_text>DELETE FROM tblAuditLog&POSTFIX; WHERE EventTime &lt; ?;</cmd_text>
      <parameter name="before_datetime"/>
    </db_cmd>
    <db_cmd name="GetDanglingMsgEntries" type="select">
        <cmd_text>SELECT tblMsgEntries&POSTFIX;.msg_entry_id FROM tblMsgEntries&POSTFIX; LEFT JOIN 
        (SELECT msg_entry_id FROM tblStorageEntries&POSTFIX; UNION ALL  SELECT msg_entry_id 
        FROM tblFilterEntries&POSTFIX;) AS tblTemp&POSTFIX; ON tblMsgEntries&POSTFIX;.msg_entry_id = tblTemp&POSTFIX;.msg_entry_id WHERE tblTemp&POSTFIX;.msg_entry_id IS NULL AND tblMsgEntries&POSTFIX;.msg_delivery_time &lt; ?;</cmd_text>
        <parameter name="end_time"/>
    </db_cmd>
    <db_cmd name="GetStorageInfo" type="select">
        <cmd_text>SELECT storage_entry_id, filter_scan_time, storage_path FROM tblStorageEntries&POSTFIX; 
        WHERE storage_reason = ? 
        AND storage_resend_count BETWEEN ? AND ? 
        AND filter_scan_time &lt; ?;</cmd_text>
        <parameter name="storage_reason"/>
        <parameter name="resend_count_min"/>
        <parameter name="resend_count_max"/>
        <parameter name="end_time"/>
    </db_cmd>
        <!--/ Log & quarantine maintenance commands -->
        <!-- ============================== Log Summary Commands ======================================= -->
        <db_cmd name="GetScanningSummaryCount" type="select">
            <cmd_text>SELECT * FROM tblScanningSummary&POSTFIX; WHERE ss_type = ? AND ss_time = ?;</cmd_text>
            <parameter name="ss_type"/>
            <parameter name="ss_time"/>
        </db_cmd>
        <db_cmd name="InsertScanningSummaryCount" type="insert">
            <cmd_text>INSERT INTO tblScanningSummary&POSTFIX; (ss_type, ss_time, ss_count) VALUES (?, ?, ?);</cmd_text>
            <parameter name="ss_type"/>
            <parameter name="ss_time"/>
            <parameter name="ss_count"/>
        </db_cmd>
        <db_cmd name="IncreaseScanningSummaryCount" type="update">
            <cmd_text>UPDATE tblScanningSummary&POSTFIX; SET tblScanningSummary&POSTFIX;.ss_count = tblScanningSummary&POSTFIX;.ss_count + 1 WHERE tblScanningSummary&POSTFIX;.ss_id = ?</cmd_text>
            <parameter name="ss_id"/>
        </db_cmd>
        <db_cmd name="IncreaseScanningSummaryCountByN" type="update">
            <cmd_text>UPDATE tblScanningSummary&POSTFIX; SET tblScanningSummary&POSTFIX;.ss_count = tblScanningSummary&POSTFIX;.ss_count + ? WHERE tblScanningSummary&POSTFIX;.ss_id = ?</cmd_text>
            <parameter name="ss_count"/>
            <parameter name="ss_id"/>
        </db_cmd>
        <!-- ============================== Engine Pattern Commands ======================================= -->
        <db_cmd name="GetEnginePatternInfo" type="select">
            <cmd_text>SELECT * FROM tblPatternEngineInfo&POSTFIX;;</cmd_text>
        </db_cmd>
        <db_cmd name="UpdateEnginePatternInfo" type="update">
            <cmd_text>UPDATE tblPatternEngineInfo&POSTFIX; SET pei_current_version = ?, pei_last_update_time = ?, pei_last_upate_status = ?, pei_last_update_status_description = ? WHERE pei_type = ?</cmd_text>
            <parameter name="pei_current_version"/>
            <parameter name="pei_last_update_time"/>
            <parameter name="pei_last_upate_status"/>
            <parameter name="pei_last_update_status_description"/>
            <parameter name="pei_type"/>
        </db_cmd>
        <db_cmd name="UpdateEnginePatternInfoEx" type="update">
            <cmd_text>UPDATE tblPatternEngineInfo&POSTFIX; SET pei_current_version = ?, pei_last_successful_update_time = ?, pei_last_update_time = ?, pei_last_upate_status = ?, pei_last_update_status_description = ? WHERE pei_type = ?</cmd_text>
            <parameter name="pei_current_version"/>
            <parameter name="pei_last_update_time"/>
            <parameter name="pei_last_successful_update_time"/>
            <parameter name="pei_last_upate_status"/>
            <parameter name="pei_last_update_status_description"/>
            <parameter name="pei_type"/>
        </db_cmd>
        <db_cmd name="UpdateEnginePatternInfoWithoutDesc" type="update">
            <cmd_text>UPDATE tblPatternEngineInfo&POSTFIX; SET pei_current_version = ? WHERE pei_type = ?</cmd_text>
            <parameter name="pei_current_version"/>
            <parameter name="pei_type"/>
        </db_cmd>
        <db_cmd name="UpdateLatestEnginePatternInfo" type="update">
            <cmd_text>UPDATE tblPatternEngineInfo&POSTFIX; SET pei_latest_version = ?, pei_last_query_time = ? WHERE pei_type = ?</cmd_text>
            <parameter name="pei_latest_version"/>
            <parameter name="pei_last_query_time"/>
            <parameter name="pei_type"/>
        </db_cmd>
        <db_cmd name="UpdateEnginePatternInfoFromLocal" type="update">
            <cmd_text>UPDATE tblPatternEngineInfo&POSTFIX; SET pei_current_version = ?, pei_last_query_time = ?, pei_last_update_time = ?, pei_latest_version = ? WHERE pei_type = ?</cmd_text>
            <parameter name="pei_current_version"/>
            <parameter name="pei_last_query_time"/>
            <parameter name="pei_last_update_time"/>
            <parameter name="pei_type"/>
        </db_cmd>
        <!-- ============================== Configurain Replication Commands ======================================= -->
        <db_cmd name="InsertCfgReplication" type="insert">
            <cmd_text>INSERT INTO tblCfgReplication&POSTFIX; (cr_session_guid, cr_time, cr_server_list, cr_selection_list) VALUES (?, ?, ?, ?);</cmd_text>
            <parameter name="cr_session_guid"/>
            <parameter name="cr_time"/>
            <parameter name="cr_server_list"/>
            <parameter name="cr_selection_list"/>
        </db_cmd>
        <db_cmd name="InsertCfgReplicationStatus" type="insert">
            <cmd_text>INSERT INTO tblCfgReplicationStatus&POSTFIX; (crs_session_guid, crs_start_time, crs_server, crs_status, crs_description) VALUES (?, ?, ?, ?, ?);</cmd_text>
            <parameter name="crs_session_guid"/>
            <parameter name="crs_start_time"/>
            <parameter name="crs_server"/>
            <parameter name="crs_status"/>
            <parameter name="crs_description"/>
        </db_cmd>
        <db_cmd name="UpdateCfgReplicationStatus" type="update">
            <cmd_text>UPDATE tblCfgReplicationStatus&POSTFIX; SET crs_end_time = ?, crs_status = ?, crs_description = ? WHERE crs_id = ?</cmd_text>
            <parameter name="crs_end_time"/>
            <parameter name="crs_status"/>
            <parameter name="crs_description"/>
            <parameter name="crs_id"/>
        </db_cmd>
        <db_cmd name="InsertCfgReplicationHistory" type="insert">
            <cmd_text>INSERT INTO tblCfgReplicationHistory&POSTFIX; (crh_session_guid, crh_time) VALUES (?, ?);</cmd_text>
            <parameter name="crh_session_guid"/>
            <parameter name="crh_time"/>
        </db_cmd>
        <db_cmd name="GetLastCfgReplication" type="select">
            <cmd_text>SELECT TOP 1 * FROM tblCfgReplicationHistory&POSTFIX; ORDER BY crh_time DESC;</cmd_text>
        </db_cmd>
        <db_cmd name="GetLastCfgReplicationStatus" type="select">
            <cmd_text>SELECT * FROM tblCfgReplicationStatus&POSTFIX; WHERE crs_session_guid = ?;</cmd_text>
            <parameter name="crs_session_guid"/>
        </db_cmd>
        <db_cmd name="GetLogDatabaseSize" type="select">
            <cmd_text>sp_helpdb ?;</cmd_text>
            <parameter name="log_DBName"/>
        </db_cmd>
    <!-- ============================== CSM Log commands ======================================= -->
    <db_cmd name="CSMGetIncidenceLog" type="select">
      <cmd_text>SELECT TOP ? * FROM vwCSMIncidenceLogs&POSTFIX; WHERE sent_to_csm = ? ORDER BY filter_scan_time ASC </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetVSLog" type="select">
      <cmd_text>SELECT TOP ? * FROM vwCSMVSLogs&POSTFIX; WHERE sent_to_csm = ? ORDER BY filter_scan_time ASC </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetABLog" type="select">
      <cmd_text>SELECT TOP ? * FROM vwCSMABLogs&POSTFIX; WHERE sent_to_csm = ? ORDER BY filter_scan_time ASC </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetCFLog" type="select">
      <cmd_text>SELECT TOP ? * FROM vwCSMCFLogs&POSTFIX; WHERE sent_to_csm = ? ORDER BY filter_scan_time ASC </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetUSLog" type="select">
      <cmd_text>SELECT TOP ? * FROM vwCSMUSLogs&POSTFIX; WHERE sent_to_csm = ? ORDER BY filter_scan_time ASC </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetStorageLog" type="select">
      <cmd_text>SELECT TOP ? * FROM vwCSMStorageLogs&POSTFIX; WHERE sent_to_csm = ? ORDER BY filter_scan_time ASC </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetArchiveLog" type="select">
      <cmd_text>SELECT TOP ? * FROM vwCSMArchiveLogs&POSTFIX; WHERE sent_to_csm = ? ORDER BY filter_scan_time ASC </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetBackupLog" type="select">
      <cmd_text>SELECT TOP ? * FROM vwCSMBackupLogs&POSTFIX; WHERE sent_to_csm = ? ORDER BY filter_scan_time ASC </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetActivityLog" type="select">
      <cmd_text>
        SELECT TOP ? activity_entry_id,activity_id,activity_time,activity_description,activity_duration_mark,sent_to_csm FROM tblActivityEntries&POSTFIX;
        WHERE sent_to_csm = ?
        ORDER BY activity_time ASC
      </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetUpdateLog" type="select">
      <cmd_text>
        SELECT TOP ? activity_entry_id,activity_id,activity_time,activity_description,activity_duration_mark,sent_to_csm FROM tblActivityEntries&POSTFIX;
        WHERE (activity_id = &ID_ACTIVITY_UPDATE;) AND (sent_to_csm = ?)
        ORDER BY activity_time ASC
      </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMGetEventLog" type="select">
      <cmd_text>
        SELECT TOP ? activity_entry_id,activity_id, activity_time, activity_description,activity_duration_mark,sent_to_csm FROM tblActivityEntries&POSTFIX;
        WHERE (activity_id = &ID_ACTIVITY_SCAN_TASK_MANUAL; OR activity_id = &ID_ACTIVITY_SCAN_TASK_SCHEDULE;)
        AND (sent_to_csm = ?)
        ORDER BY activity_time ASC
      </cmd_text>
      <parameter name="sent_to_csm"/>
    </db_cmd>
    <db_cmd name="CSMUpdateIncidenceLogSentStatus" type="update">
      <cmd_text>UPDATE tblFilterEntries&POSTFIX; SET sent_to_csm = ? WHERE filter_entry_id = ?</cmd_text>
      <parameter name="sent_to_csm"/>
      <parameter name="entry_id"/>
    </db_cmd>
    <db_cmd name="CSMUpdateStorageLogSentStatus" type="update">
      <cmd_text>UPDATE tblStorageEntries&POSTFIX; SET sent_to_csm = ? WHERE storage_entry_id = ?</cmd_text>
      <parameter name="sent_to_csm"/>
      <parameter name="entry_id"/>
    </db_cmd>
    <db_cmd name="CSMUpdateActivityLogSentStatus" type="update">
      <cmd_text>UPDATE tblActivityEntries&POSTFIX; SET sent_to_csm = ? WHERE activity_entry_id = ?</cmd_text>
      <parameter name="sent_to_csm"/>
      <parameter name="entry_id"/>
    </db_cmd>
    <db_cmd name="CSMDeleteSentIncidenceLogs" type="delete">
      <cmd_text>DELETE * FROM tblFilterEntries&POSTFIX; WHERE sent_to_csm = 1;</cmd_text>
    </db_cmd>
    <db_cmd name="CSMDeleteSentActivityLogs" type="delete">
      <cmd_text>DELETE * FROM tblActivityEntries&POSTFIX; WHERE sent_to_csm = 1;</cmd_text>
    </db_cmd>
    <db_cmd name="GetEventTackingUserList" type="select">
      <cmd_text>
        SELECT distinct(username)
        FROM tblAuditLog&POSTFIX;;
      </cmd_text>
    </db_cmd>
    </query_set>
    <simple_commands>
        <command name="QUERYSTRING_SECURITYLOG_BASE">
            SELECT B.filter_entry_id, B.filter_id, B.filter_rule, B.filter_rule_supplement, B.filter_engine, B.filter_pattern, B.filter_scan_time, B.filter_reason, B.filter_action, B.file_original, A.msg_task_id, A.msg_protocol, A.msg_found_at, A.msg_source, A.msg_destination, A.msg_subject, A.msg_delivery_time, C.storage_path, C.storage_type FROM ([tblMsgEntries&POSTFIX;] AS A INNER JOIN [tblFilterEntries&POSTFIX;] AS B ON A.msg_entry_id = B.msg_entry_id) LEFT JOIN [tblStorageEntries&POSTFIX;] AS C ON B.filter_entry_id = C.filter_entry_id
        </command>
        <command name="QUERYSTRING_EVENTLOG_BASE">
            SELECT * FROM [tblActivityEntries&POSTFIX;]
        </command>
        <command name="QUERYSTRING_SECURITYLOG_TOPN1">
            SELECT TOP %d B.filter_entry_id, B.filter_id, B.filter_rule, B.filter_rule_supplement, B.filter_engine, B.filter_pattern, B.filter_scan_time, B.filter_reason, B.filter_action, B.file_original, A.msg_task_id, A.msg_protocol, A.msg_found_at, A.msg_source, A.msg_destination, A.msg_subject, A.msg_delivery_time, C.storage_path, C.storage_type FROM ([tblMsgEntries&POSTFIX;] AS A INNER JOIN [tblFilterEntries&POSTFIX;] AS B ON A.msg_entry_id = B.msg_entry_id) LEFT JOIN [tblStorageEntries&POSTFIX;] AS C ON B.filter_entry_id = C.filter_entry_id
        </command>
        <command name="QUERYSTRING_EVENTLOG_TOPN1">
            SELECT TOP %d * FROM [tblActivityEntries&POSTFIX;]
        </command>
        <command name="QUERYSTRING_AUDITLOG_BASE">
            SELECT * FROM [tblAuditLog&POSTFIX;]
        </command>
        <command name="QUERYSTRING_AUDITLOG_TOPN1">
            SELECT TOP %d * FROM [tblAuditLog&POSTFIX;]
        </command>
        <name_conversion src="dbSchema_Log.xml"/>
    </simple_commands>
</database_configuration>
