Archiving Old Records (UDR System)

Owned by Greg Walsh
Sept 30, 2021
7 min read

Summary

This article explains how to archive usage records of a specified date within the EngageIP UDR System.

Archiving records allows you to keep the size of the EngageIP Database to an optimal level. Once the records are archived successfully they will be deleted from the UDR table.

Configuration

To setup the automated usage archiving process you need to perform the following steps:

  1. Load EngageIP as the top level owner

  2. Load the Setup tab and under the Extensibility heading click on Custom Code
    Note: Prior to EngageIP version 8.5.17.0 the Custom Code link was listed under the Configuration heading on the Setup tab

  3. On the Custom Code page click the Add button and enter a Name (e.g. ‘Archive Old Records’)

  4. Paste the Custom Code shown below into the Code textbox //Reference: Castle.MonoRail.Framework

    //Reference: Data //Reference: Presentation //Reference: System.Data using System; using System.Collections.Generic; using System.Data; using System.IO; using System.IO.Compression; using System.Linq; using System.Net; using Logisense.Boss.Logic; using Logisense.Boss.Logic.DomainModel; using Logisense.Boss.Presentation; using Logisense.Database.Attributes; using Config = Logisense.Boss.Logic.DomainModel.Config; namespace Logisense.Boss.CustomScripts { public class AutomatedUsageArchive : CustomCodeUIBase { public override string SetupModuleName { get { return "Configuration"; } } public override object Model { get { return new AutomatedUsageArchiveModel(); } } public override string FormName { get { return "AutomatedUsageArchive"; } } public override void OnSave(object model) { var jobName = "UDR Archive"; var AutomatedUsageArchiveModel = model as AutomatedUsageArchiveModel; if (AutomatedUsageArchiveModel != null) { if (Owner.GetByID(OwnerId).SearchJobScheduleByJob(jobName).Length == 0) { new Logic.DomainModel.JobSchedule { Job = jobName, Enabled = true, OwnerID = OwnerId, Schedule = "24h", Parameters = "No Paramters" }.Create(); } } } public class AutomatedUsageArchiveModel { [FieldType("Text-Required")] [FieldLabel("Server")] [FieldMaxSize("255")] public string Server { get; set; } [FieldType("Text-Required")] [FieldLabel("User Name")] [FieldMaxSize("50")] public string UserName { get; set; } [FieldType("Password-Required")] [FieldLabel("Password")] [FieldMaxSize("50")] public string Password { get; set; } [FieldType("Text")] [FieldLabel("Domain")] [FieldMaxSize("255")] public string Domain { get; set; } [FieldType("Duration-Required")] [FieldLabel("Older Than")] [FieldMaxSize("255")] public string OlderThan { get; set; } [FieldType("Text-Required")] [FieldLabel("Local Temp Folder Path On The Server")] [FieldMaxSize("255")] public string LocalServerTempFolder { get; set; } [FieldType("Bit")] [FieldLabel("Zip")] public bool Zip { get; set; } [FieldType("Bit")] [FieldLabel("Group Per User")] public bool GroupPerUser { get; set; } } } public class UDRArchiveJob : Logisense.Boss.Logic.Core.IJob { private const string ZipExtension = ".gzip"; public string Name { get { return "UDR Archive"; } } public string Description { get { return "Writes UDRs to a CSV file on a remote server. Takes two parameters, the date to stop at and the filename."; } } public bool Run(Logisense.Boss.Logic.DomainModel.Job job) { var model = Logic.Core.CustomCodeUiFields.LoadCustomCodeUiModel<AutomatedUsageArchive.AutomatedUsageArchiveModel>(); if (model == null) throw new Exception("There was an error loading the model"); DateTime endDate = (DateTime.Today) - Logic.Core.DataType.Duration(model.OlderThan); string filename = string.Format("UDRArchive_{0}_{1}.csv", endDate.ToShortDateString().Replace("/", "_"), job.Date.Ticks); string copyPathFrom = model.LocalServerTempFolder; string copyPathTo = model.Server; bool archive = model.Zip; try { //write backup to a file Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Before Backup", "System", string.Format("@endDate={0} copyPathFrom={1} fileName={2}", endDate, copyPathFrom, filename), job.OwnerID, null, Int32.MinValue, null); BackupOlderUdrsToArchiveTable(job, endDate); copyPathFrom = SetLocalTempFolder(copyPathFrom); //Run BCP.exe to archive RunBcpArchive(copyPathFrom, filename, model.GroupPerUser, archive); //move the file to the archive server CopyFile(model.Server, model.UserName, model.Password, model.Domain, copyPathFrom, copyPathTo, filename, model.GroupPerUser, archive); //delete from the local folder RemoveLocalTempFolders(copyPathFrom); } catch (Exception e) { //Log the error, send email.. job.Message = "Something bad happened and, deactivating this job, " + e.Message; if (job.ID > 0) { job.Update(); } Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "Error: " + e.Message, job.OwnerID, null, Int32.MinValue, null); } return true; } private string SetLocalTempFolder(string copyPathFrom) { //check if the temp folder exists if not try to create if (!Directory.Exists(copyPathFrom)) { CreateFolder(copyPathFrom); } if (!copyPathFrom.EndsWith("\\")) { copyPathFrom += "\\"; } return copyPathFrom; } public virtual void BackupOlderUdrsToArchiveTable(Job job, DateTime endDate) { //backup the paeriod into temp table string backupToTempTableSql = @"IF OBJECT_ID('dbo.UDRArchive', 'U') IS NOT NULL DROP TABLE dbo.UDRArchive SELECT TOP 0 UDR.* INTO UDRArchive from UDR inner join UDRBiller ON UDR.UDRBillerID=UDRBiller.ID DELETE dbo.UDR OUTPUT DELETED.* INTO UDRArchive FROM dbo.UDR INNER JOIN dbo.UDRBiller ON dbo.UDR.UserID = dbo.UDRBiller.UserID AND dbo.UDRBiller.InvoiceID IS NOT NULL WHERE UDR.Date <@endDate"; SQLHelper.ExecuteNonQuery(backupToTempTableSql, new SQLParameters {{"@endDate", endDate}}); } public virtual void RemoveLocalTempFolders(string copyPathFrom) { var execute = string.Format("rmdir {0} -f /s/q", copyPathFrom); var delResult = SQLHelper.ExecuteShellProcess(execute); Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", string.Format("Delete temp folder:{0} res:{1}", execute, delResult), 1, null, Int32.MinValue, null); } public virtual void CopyFile(string server, string username, string password, string domain, string copyPathFrom, string copyPathTo, string filename, bool groupPerUser, bool archive) { using (new NetworkConnection(server, new NetworkCredential(username, password, domain))) { if (groupPerUser) { DirectoryCopy(copyPathFrom, copyPathTo, true, archive); } else { //string file = string.Format("{0}{1}", copyPathFrom, filename); DirectoryCopy(copyPathFrom, copyPathTo, false, archive); } } } private static void DirectoryCopy(string sourceDirName, string destDirName, bool copySubDirs,bool archive) { var dir = new DirectoryInfo(sourceDirName); DirectoryInfo[] dirs = dir.GetDirectories(); // If the source directory does not exist, throw an exception. if (!dir.Exists) { throw new DirectoryNotFoundException("Source directory does not exist or could not be found: " + sourceDirName); } // If the destination directory does not exist, create it. if (!Directory.Exists(destDirName)) { Directory.CreateDirectory(destDirName); } // Get the file contents of the directory to copy. FileInfo[] files = dir.GetFiles(); foreach (FileInfo file in files) { if (archive && !file.Name.EndsWith(ZipExtension)) { continue; } // Create the path to the new copy of the file. string temppath = Path.Combine(destDirName, file.Name); // Move the file. file.MoveTo(temppath); Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", string.Format("Moved to: {0}", file.FullName), 1, null, Int32.MinValue, null); } // If copySubDirs is true, copy the subdirectories. if (copySubDirs) { foreach (DirectoryInfo subdir in dirs) { // Create the subdirectory. string temppath = Path.Combine(destDirName, subdir.Name); // Copy the subdirectories. DirectoryCopy(subdir.FullName, temppath, copySubDirs, archive); } } } public void RunBcpArchive(string pathFolder, string filename, bool groupPerUser,bool archive) { var connectionString = Config.GetConnectionString(); var strings = connectionString.Split(';'); var server = strings[0].Split('=')[1]; var database = strings[2].Split('=')[1]; var username = strings[3].Split('=')[1]; var password = strings[4].Split('=')[1]; if (!groupPerUser) { ExecuteBcpExportToCsvForAllRecords(pathFolder, filename, database, username, password, server, archive); if (archive) { Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "Start archiving", 1, null, Int32.MinValue, null); ExecuteArchiving(pathFolder, filename); Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "Finished archiving", 1, null, Int32.MinValue, null); } } else { var users = GetDistinctUsers(); foreach (var user in users) { var folderToCreateFiles = string.Format("{0}{1}\\", pathFolder, user.Name); CreateFolder(folderToCreateFiles); ExecuteBcpExportToCsvForIndividualUser(folderToCreateFiles, filename, database, user.UserId, username, password, server, archive); if (archive) { Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "Start archiving", 1, null, Int32.MinValue, null); ExecuteArchiving(folderToCreateFiles, filename); Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "Finished archiving", 1, null, Int32.MinValue, null); } } } } public virtual List<UdrUser> GetDistinctUsers() { var users = SQLHelper.ExecuteDataTable("select UserID,u.Name from UDRArchive inner join [User] u on UDRArchive.UserID = u.ID group by UserID,u.Name order by UserID asc", new SQLParameters()); return (from DataRow row in users.Rows select new UdrUser{Name = (string)row["Name"],UserId = (int)row["UserID"]}).ToList(); } public virtual void ExecuteBcpExportToCsvForIndividualUser(string pathFolder, string filename, string database, int userId, string username, string password, string server,bool archive) { var selectQuery = string.Format("select * from {0}.dbo.[UDRArchive] where UserID='{1}'", database, userId); Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "ECP run this sql: " + selectQuery, 1, null, Int32.MinValue, null); var result = SQLHelper.ExportCSVWithBCP(pathFolder, filename, username, password, server, selectQuery); Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "ECP Returned: " + result, 1, null, Int32.MinValue, null); if (result.IndexOf("[ExitCode:0]") == -1) { //there was an error throw new Exception(result); } } public virtual void ExecuteBcpExportToCsvForAllRecords(string pathFolder, string filename, string database, string username, string password, string server, bool archive) { var selectQuery = string.Format("select * from {0}.dbo.[UDRArchive]", database); Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "ECP run this sql: " + selectQuery, 1, null, Int32.MinValue, null); var result = SQLHelper.ExportCSVWithBCP(pathFolder, filename, username, password, server, selectQuery); Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "ECP Returned: " + result, 1, null, Int32.MinValue, null); if (result.IndexOf("[ExitCode:0]") == -1) { //there was an error throw new Exception(result); } } public virtual void ExecuteArchiving(string pathFolder, string filename) { var file = string.Format("{0}{1}", pathFolder, filename); if (File.Exists(file)) { Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "Archiving " + file, 1, null, Int32.MinValue, null); try { var archiveFile = string.Format("{0}{1}", file, ZipExtension); using (var stream = new StreamReader(file)) { var bytes = System.Text.Encoding.UTF8.GetBytes(stream.ReadToEnd()); var compressedBytes = Compress(bytes); File.WriteAllBytes(archiveFile, compressedBytes); } Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "Archived with success " + archiveFile, 1, null, Int32.MinValue, null); } catch (Exception e) { Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", "Error archiveing " + e.Message, 1, null, Int32.MinValue, null); } } else { Logic.EventLogger.Log(Int32.MinValue, Int32.MinValue, "Automated Usage Archive", "System", string.Format("Can't find file {0} to archive" , file), 1, null, Int32.MinValue, null); } } public virtual void CreateFolder(string folder) { Directory.CreateDirectory(folder); } public static byte[] Compress(byte[] fileBytes) { using (var outFile = new MemoryStream()) { using (var inFile = new MemoryStream(fileBytes)) { using (var gZipStream = new GZipStream(outFile, CompressionMode.Compress)) { inFile.WriteTo(gZipStream); } } return outFile.ToArray(); } } } public class UdrUser { public int UserId { get; set; } public string Name { get; set; } } }

  5. Click the Save button

  6. Return to the Setup tab and under the Configuration heading click the new link titled Automated Usage Archive

  7. Enter the following details into the fields listed:
    Server = shared folder path on a remote server where archived files will be copied to (e.g. \\remote.server\F$\folder)
    User Name, Password, Domain = credentials to connect to the remote server
    Older Than = define the records that should be archived based upon the record age (e.g. ‘1Y’ will archive UDRs that are one year old or older as of today)
    Local Temp Folder Path = folder path on the server where the temp files will be created (e.g. C:\UDRArchive)
    Zip = if enabled a compressed csv file will be copied onto the destination server
    Group Per User = if enabled files will be created in a separate folder per each user/account name

  8. Click the Save button

Once saved a job will be scheduled to run every 24 hours to archive all UDR Records that have reached the age which you have defined in the configuration above. If you wish to set a specific time to Archive the data (e.g. 3am) or wish to change the frequency for how often records are archived, see the section below

Scheduling the Archive Job

You can set when and how frequently the archive job should be run in the Job Schedule Configuration.

  1. Load the Setup tab and under Configuration click on Job Schedule

  2. Click on the job name ‘UDR Archive‘

  3. Set the Schedule as desired (e.g. 7D = every 7 days, 24h = every 24 hours, 6M = every 6 months)

  4. To control the time the job is run clear the Last Run field and set the Next Run date to the desired time (e.g. ’12/12/2014 03:00:00 AM’ + a Schedule value of 24h if you want the job to run every morning at 3am)