How to Assign a Default Policy to Attached PST Files

Last updated on 2017-07-19 16:25:33

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:

Open SQL Management Studio, and connect to the SQL instance, typically (in SQL Express), PSTENTERPRISE.
Open the PSTEnterprise database hierarchy and browse to Databases > PSTEnterprise > Tables.
Right-click dbo.PSTPolicyDefinition, and click Select Top 100 Rows:
In the Results tab, identify and note the policy ID you wish to assign by default:
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
  Click here to see more
  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:
  Click here to see more
  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:
  Click here to see more
  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
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. *****/
Execute the script.
Use some sample PST files to confirm the script is operating as expected.
To remove the trigger, execute the following query:
USE [PSTEnterprise]
GO
DROP TRIGGER [dbo].[assigndefaultpolicy]