How to clear the events older than 3 months via Windows Scheduler OR Global Reflex

Runninv via Windows Scheduler

Principle

To clear events in reports older than 3 months, you must install a SQL Stored Procedure that will be launched automatically once every day.

This Stored Procedure will delete events older than 3 months in each database of your SQL instance whose name contains the word "Journal", in order to also delete events older than 3 months in the archive databases.

Installation

1. Open the SQL Studio and launch a New query.
2. Paste the following query (attached):
   

   USE master
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE PROCEDURE [dbo].[Clear3Month]
   AS
   BEGIN
   SET NOCOUNT ON;
   

       DECLARE @DbName NVARCHAR(255)
   DECLARE db_cursor CURSOR FOR
   SELECT name
   FROM sys.databases
   WHERE name LIKE '%Journal%'

       OPEN db_cursor
   FETCH NEXT FROM db_cursor INTO @DbName

       WHILE @@FETCH_STATUS = 0
   BEGIN
   DECLARE @Sql NVARCHAR(MAX)

           SET @Sql = N'
      USE [' + @DbName + N']
               DELETE FROM dbo.AccessEventsLog WHERE DateTime < DATEADD(month, -3, GETDATE())
               DELETE FROM dbo.AlarmEventsLog WHERE DateTime < DATEADD(month, -3, GETDATE())
               DELETE FROM dbo.AuditEventsLog WHERE DateTime < DATEADD(month, -3, GETDATE())
               DELETE FROM dbo.CommEventsLog WHERE DateTime < DATEADD(month, -3, GETDATE())
               DELETE FROM dbo.GalaxyEventsLog WHERE DateTime < DATEADD(month, -3, GETDATE())
               DELETE FROM dbo.Log WHERE Date < DATEADD(month, -3, GETDATE())
               DELETE FROM dbo.TechnicalEventsLog WHERE DateTime < DATEADD(month, -3, GETDATE())
               DELETE FROM dbo.UserManualEventsLog WHERE DateTime < DATEADD(month, -3, GETDATE())
           '

           EXEC sp_executesql @Sql

           FETCH NEXT FROM db_cursor INTO @DbName
   END

       CLOSE db_cursor
   DEALLOCATE db_cursor
   END

3. If the databases don't contain the word "Journal", change this name in the query.
4. Launch the query.
5. On Guard Point 10 server, open Powershell as Admin and write:
   set-executionpolicy unrestricted
6. Confirm this command to validate.
7. On Guard Point 10 server, paste the 'Clear3Month.ps1' file (attached) in the C:\ folder.
8. Right click on this file and make sure that the file is Unblocked and SYSTEM and Administrators have Full control on it.
9. On Guard Point 10 server, open the Windows Scheduler.
10. Create a new Task with the following settings:
    • In General tab, set the options as follows:
      
      
    • In Trigger tab, set the options as follows (the time can be adjusted as needed):
      
       
    • In Actions tab, type « powershell.exe » as Program and enter the following arguments: c:\Clear3Month.ps1 runtype $true

The Task is ready to clear the events automatically:

Running via a global reflex.

1. Make sure that the procedure 'Clear3Month' exists on the journal database ('AC8Journal_default' > 'Programmability' > 'Stored Procedures' > 'dbo.Clear3Month'). If yes, do not make any modifications; simply create a global reflex as described in point 2 below.

2. Create a global reflex (you need Advanced GR module on your license to use 'Run SQL script' action)

Trigger Events: Time Specific > Schedule.

Action: Run SQL Script → select journal database, type 'Clear3Month' (make sure that there are not spaces before and after it) and tick 'Stored procedure' checkbox.