How to clean-up SQL Server call archive and re-import it from a "Storico" file

Applies to:

Imagicle Call Analytics Standard/Enterprise

Description:

How to remove calls from SQL Server database and reimport them from "Storico" text file

Solution: UCX Suite starting from 2021.3.1

Stop Blue's Service Host in Call Analytics → Administrative pages → Service Manager

open Microsoft SQL Manager Studio (make a backup copy of the database)

Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.

Please keep in mind that dates are based on UTC Time Zone.

CODE


/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */

-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'

-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-01 00:00:00.000'

-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000

----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)

SET ROWCOUNT @recordsPerChunk

RAISERROR('Deleting [BibCalls] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [BibCalls] WHERE [UtcStartDateTime] >= @startDate AND [UtcStartDateTime] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [BibCalls] WHERE [UtcStartDateTime] >= @startDate AND [UtcStartDateTime] < @endDate
    COMMIT TRANSACTION
END

RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    COMMIT TRANSACTION
END

SET ROWCOUNT 0

If you need reimport more than one StoricoSITE file, please follow this procedure

in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings

Start Blue's Service Host in Call Analytics → Administrative pages → Service Manager and wait until miner has finished importing all calls

Solution: UCX Suite starting from 2020.3.1 up to 2021.1.1

Stop Blue's Service Host in Call Analytics → Administrative pages → Service Manager

open Microsoft SQL Manager Studio (make a backup copy of the database)

Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.

CODE


/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */

-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'

-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-01 00:00:00.000'

-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000

----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)

SET ROWCOUNT @recordsPerChunk

RAISERROR('Deleting [BibCalls] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [BibCalls] WHERE [StartDate] >= @startDate AND [StartDate] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [BibCalls] WHERE [StartDate] >= @startDate AND [StartDate] < @endDate
    COMMIT TRANSACTION
END

RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    COMMIT TRANSACTION
END

SET ROWCOUNT 0

If you need reimport more than 1 StoricoSITE file follow this procedure

in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings

Start Blue's Service Host in Call Analytics → Administrative pages → Service Manager and wait until Miner has finished importing all calls

Solution: UC Suite previous 2020.3.1

Stop Blue's Service Host in Call Analytics → Administrative pages → Service Manager

open Microsoft SQL Manager Studio (make a backup copy of the database)

Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.

CODE


/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */

-- Declare the date after which the calls will be canceled

-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'


-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-29 00:00:00.000'

-- Declare the site to be canceled 
DECLARE @sito VARCHAR(50)
set @sito = 'SITE'


-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000

----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)

SET ROWCOUNT @recordsPerChunk

RAISERROR('Deleting [ChiamateInterne] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
        SELECT @recordsNumber = COUNT(*) FROM [ChiamateInterne] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
        
        BEGIN TRANSACTION
        DELETE FROM [ChiamateInterne] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        COMMIT TRANSACTION
END


RAISERROR('Deleting [Chiamate] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
        SELECT @recordsNumber = COUNT(*) FROM [Chiamate] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
        
        BEGIN TRANSACTION
        DELETE FROM [Chiamate] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
        COMMIT TRANSACTION
END

RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
    SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT

    BEGIN TRANSACTION
    DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
    COMMIT TRANSACTION
END

SET ROWCOUNT 0

Please change the dates of the above query according to the period you wish to delete

If you need reimport more than 1 StoricoSITE file follow this procedure

in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard

in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings

Start Blue's Service Host in Call Analytics → Administrative pages → Service Manager and wait until Miner has finished importing all calls