We use cookies on our website to ensure we provide you with the best experience on our website. By using our website, you agree to the use of cookies for analytics and personalized content.This website uses cookies. More Information
It seems like your browser didn't download the required fonts. Please revise your security settings and try again.

How to Assign a Default Policy to Attached PST Files

  • Last updated on

This article refers to the Barracuda PST Enterprise version 2.2.0.1242 or higher.

You may want to automatically apply a default policy to PSTs if:

  • PSTs are found connected in an Outlook profile; and/or
  • PSTs are found not connected to an Outlook profile (files on the file system-only).

To automatically apply a default policy, you can define a database trigger.

Define Database Trigger

The steps in this section assume you are familiar with SQl Management Studio and have a good understanding of databases.

A database trigger is a stored procedure that automatically executes when an event occurs in the database server. At a high level, the trigger intercepts the process of the row being inserted into the database table and updates the relevant field with the correct policy ID value.

Use the following steps to add an INSTEAD OF INSERT type trigger:

  1. Open SQL Management Studio, and connect to the SQL instance, typically (in SQL Express), PSTENTERPRISE.
  2. Open the PSTEnterprise database hierarchy and browse to Databases > PSTEnterprise > Tables.
  3. Right-click dbo.PSTPolicyDefinition, and click Select Top 100 Rows:
    trigger01.png
  4. In the Results tab, identify and note the policy ID you wish to assign by default:
    trigger02.png
  5. In SQL Management Studio, click New Query. Paste the appropriate SQL statement for your PST Enterprise version in the SQL STATEMENT.
    • SQL Statement, version 2.2.x

      USE [PSTEnterprise]
      GO

      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE TRIGGER [dbo].[assigndefaultpolicy] ON [dbo].[PSTFile]
      INSTEAD OF INSERT
      AS BEGIN

          DECLARE @maxid BIGINT;
      DECLARE @policyid BIGINT;

      SET @policyid = 1;
      SET @maxid = (SELECT MAX(id) FROM PSTFile);
      SET @maxid = @maxid + 1;

      SET IDENTITY_INSERT pstfile ON;

      INSERT INTO pstfile (
      ID
      ,[Machine]
      ,[FilePath]
      ,[Source]
      ,[Title]
      ,[ErrorCode]
      ,[InitialDataSize]
      ,[DataSize]
      ,[DiskSize]
      ,[Folders]
      ,[Messages]
      ,[Attachments]
      ,[FirstSeen]
      ,[LastSeen]
      ,[EarliestMessage]
      ,[LatestMessage]
      ,[OwnerName]
      ,[OwnerDn]
      ,[ConfidenceFactor]
      ,[ConfidenceReason]
      ,[Unicode]
      ,[CustomerID]
      ,[LastStatusText]
      ,[FinaliseStatus]
      ,[PSTMailbox_id]
      ,[CurrentPolicy_id]
      ,[ActionStatus]
      ,[PSTCopyFilePath]
      ,[JobId]
      ,[AdditionalData]
      ,[LastPacketUpload])

      SELECT
      @maxid
      ,[Machine]
      ,[FilePath]
      ,[Source]
      ,[Title]
      ,[ErrorCode]
      ,[InitialDataSize]
      ,[DataSize]
      ,[DiskSize]
      ,[Folders]
      ,[Messages]
      ,[Attachments]
      ,[FirstSeen]
      ,[LastSeen]
      ,[EarliestMessage]
      ,[LatestMessage]
      ,[OwnerName]
      ,[OwnerDn]
      ,[ConfidenceFactor]
      ,[ConfidenceReason]
      ,[Unicode]
      ,[CustomerID]
      ,[LastStatusText]
      ,[FinaliseStatus]
      ,[PSTMailbox_id]
      ,[CurrentPolicy_id]
      ,[ActionStatus]
      ,[PSTCopyFilePath]
      ,[JobId]
      ,[AdditionalData]
      ,[LastPacketUpload]

      FROM inserted;

        SET IDENTITY_INSERT pstfile OFF;

      UPDATE PSTFile SET CurrentPolicy_id = @policyid, ErrorCode = 0
      WHERE ID = @maxid AND [Source] = ‘FoundAttached’

      SELECT @maxid;

      END

    • SQL Statement, version 2.4:

      USE [PSTEnterprise]
      GO

      SET ANSI_NULLS ON 
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE TRIGGER [dbo].[assigndefaultpolicy] ON [dbo].[PSTFile]
      INSTEAD OF INSERT
      AS BEGIN

          DECLARE @maxid BIGINT;
      DECLARE @policyid BIGINT;

      SET @policyid = 1;
      SET @maxid = (SELECT MAX(id) FROM PSTFile);
      SET @maxid = @maxid + 1;

      SET IDENTITY_INSERT pstfile ON;

      INSERT INTO pstfile (
      ID

      ,[Machine]

      ,[FilePath]

      ,[Source]

      ,[Title]

      ,[ErrorCode]

      ,[InitialDataSize]

      ,[DataSize]

      ,[InitialDiskSize]
      ,[DiskSize]

      ,[Folders]

      ,[Messages]

      ,[Attachments]

      ,[FirstSeen]

      ,[LastSeen]

      ,[EarliestMessage]

      ,[LatestMessage]

      ,[OwnerName]

      ,[OwnerDn]

      ,[ConfidenceFactor]

      ,[ConfidenceReason]

      ,[Unicode]

      ,[CustomerID]

      ,[LastStatusText]

      ,[FinaliseStatus]

      ,[PSTMailbox_id]

      ,[CurrentPolicy_id]

      ,[ActionStatus]

      ,[PSTCopyFilePath]

      ,[JobId]

      ,[AdditionalData]

      ,[LastPacketUpload]
      ,[NextProcessTime]
      )

      SELECT
      @maxid

      ,[Machine]

      ,[FilePath]

      ,[Source]

      ,[Title]

      ,[ErrorCode]

      ,[InitialDataSize]

      ,[DataSize]

      ,[InitialDiskSize]
      ,[DiskSize]

      ,[Folders]

      ,[Messages]

      ,[Attachments]

      ,[FirstSeen]

      ,[LastSeen]

      ,[EarliestMessage]

      ,[LatestMessage]

      ,[OwnerName]

      ,[OwnerDn]

      ,[ConfidenceFactor]

      ,[ConfidenceReason]

      ,[Unicode]

      ,[CustomerID]

      ,[LastStatusText]

      ,[FinaliseStatus]

      ,[PSTMailbox_id]

      ,[CurrentPolicy_id]

      ,[ActionStatus]

      ,[PSTCopyFilePath]

      ,[JobId]

      ,[AdditionalData]

      ,[LastPacketUpload]
      ,[NextProcessTime]

      FROM inserted;

        SET IDENTITY_INSERT pstfile OFF;

      UPDATE PSTFile SET CurrentPolicy_id = @policyid, ErrorCode = 0
      WHERE ID = @maxid AND [Source] = ‘FoundAttached’

      SELECT @maxid;

      END

    • SQL Statement, version 3.0 and higher:

      USE [PSTEnterprise]
      GO

      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE TRIGGER [dbo].[assigndefaultpolicy] ON [dbo].[PSTFile]
      INSTEAD OF INSERT

      AS BEGIN

      DECLARE @maxid BIGINT;
      DECLARE @policyid BIGINT;

      SET @policyid = 1;
      SET @maxid = (SELECT MAX(id) FROM PSTFile);
      IF @maxid is null
      SET @maxid = 1
      ELSE
      SET @maxid = @maxid + 1;

      SET IDENTITY_INSERT pstfile ON;

      INSERT INTO pstfile (
      ID
      ,[Machine]
      ,[FilePath]
      ,[Source]
      ,[Title]
      ,[ErrorCode]
      ,[InitialDataSize]
      ,[DataSize]
      ,[InitialDiskSize]
      ,[DiskSize]
      ,[Folders]
      ,[Messages]
      ,[Attachments]
      ,[FirstSeen]
      ,[LastSeen]
      ,[EarliestMessage]
      ,[LatestMessage]
      ,[OwnerName]
      ,[OwnerDn]
      ,[ConfidenceFactor]
      ,[ConfidenceReason]
      ,[Unicode]
      ,[CustomerID]
      ,[LastStatusText]
      ,[FinaliseStatus]
      ,[ActionStatus]
      ,[PSTCopyFilePath]
      ,[JobId]
      ,[AdditionalData]
      ,[LastPacketUpload]
      ,[NextProcessTime]
      ,[MsgErrCount]
      ,[MsgErrRetryCount]
      ,[MsgExceededSizeCount]
      ,[FoundDuringIteration]
      ,[NotFoundCount]
      ,[PSTProcMachine]
      ,[PreDormantState]
      ,[OwnerMailboxSMTPEmailAddress]
      ,[SchedulingStart]
      ,[SchedulingEnd]
      ,[PolicyReassigned]
      ,[PSTMailbox_id]
      ,[CurrentPolicy_id])

      SELECT
      @maxid
      ,[Machine]
      ,[FilePath]
      ,[Source]
      ,[Title]
      ,[ErrorCode]
      ,[InitialDataSize]
      ,[DataSize]
      ,[InitialDiskSize]
      ,[DiskSize]
      ,[Folders]
      ,[Messages]
      ,[Attachments]
      ,[FirstSeen]
      ,[LastSeen]
      ,[EarliestMessage]
      ,[LatestMessage]
      ,[OwnerName]
      ,[OwnerDn]
      ,[ConfidenceFactor]
      ,[ConfidenceReason]
      ,[Unicode]
      ,[CustomerID]
      ,[LastStatusText]
      ,[FinaliseStatus]
      ,[ActionStatus]
      ,[PSTCopyFilePath]
      ,[JobId]
      ,[AdditionalData]
      ,[LastPacketUpload]
      ,[NextProcessTime]
      ,[MsgErrCount]
      ,[MsgErrRetryCount]
      ,[MsgExceededSizeCount]
      ,[FoundDuringIteration]
      ,[NotFoundCount]
      ,[PSTProcMachine]
      ,[PreDormantState]
      ,[OwnerMailboxSMTPEmailAddress]
      ,[SchedulingStart]
      ,[SchedulingEnd]
      ,[PolicyReassigned]
      ,[PSTMailbox_id]
      ,[CurrentPolicy_id]

      FROM inserted;

      SET IDENTITY_INSERT pstfile OFF;

      UPDATE PSTFile SET CurrentPolicy_id = @policyid, ErrorCode = 0
      WHERE ID = @maxid AND [Source] = 'FoundAttached'

      SELECT @maxid;

      END

  6. Edit the statement as follows:
    1. Locate the line SET @policyid = 1; and replace the ID number with correct ID value for the Policy you want assigned as default.
    2. Edit the WHERE clause in the third line from the bottom to match your requirements of what type of PSTs the default policy applies to. For example, 

      WHERE ID = @maxid AND [Source] = ‘FoundAttached’
      /***** for just PSTs that are found connected in an Outlook profile *****/

      WHERE ID = @maxid AND [Source] <> ‘FoundAttached’ 
      /***** for just PSTs that are found not connected in an Outlook profile *****/

      WHERE ID = @maxid 
      /***** for all PSTs regardless of how they are found. *****/

  7. Execute the script.
  8. Use some sample PST files to confirm the script is operating as expected.
  9. To remove the trigger, execute the following query:
    USE [PSTEnterprise]
    GO
    DROP TRIGGER [dbo].[assigndefaultpolicy]

 

Last updated on