Backup and Delete Alarms History

Dear Expert,

I need help knowing how to periodically delete the alarm history, for example, every 30 days. However, before deleting it, I want to make a backup outside of the project path so that I can save it to another storage device and use less memory on the main PC.

Best regards,

Hello Miguel,

Thank you for getting in touch with us.

To create a backup of your alarm history, follow these steps:

  1. Rename your Alarm Window object that displays the Alarm History. Let’s assume you named it “alarm” in this example.

  1. Create a button that will execute a method in the display codebehind:

public void MouseLeftButtonDown1(object sender, System.Windows.Input.InputEventArgs e)
    TAlarmWindow alarm = this.CurrentDisplay.GetAlarmWindow("alarm") as TAlarmWindow;
    alarm.ExportToCsv(@"C:\FilePath\FileName.csv", ",");

The ExportToCsv method will save the content of the Alarm window object to a .CSV file. The first argument represents the file path and name, while the second argument is the separator used in the .csv file.

  1. To automate the backup process, you can create a scheduled task that runs every 30 days to create the backup file automatically.

To delete the alarm history, you can create an SQL query that removes all data from the “Alarms” table in the AlarmHistorian Database of your project:

  1. Go to Edit > Datasets > Queries and create a query in the AlarmHistorian DB.

  2. Modify the script used to create the backup file, so it also deletes the alarm history table:

TAlarmWindow alarm = this.CurrentDisplay.GetAlarmWindow("alarm") as TAlarmWindow;
alarm.ExportToCsv(@"C:\FilePath\FileName.csv", ",");
@Dataset.Query.QUERYNAME.SqlStatement = "DELETE FROM Alarms";

Please give these steps a try and let us know if they work for you.

Best Regards,
Tatsoft Team.

1 Like

Hello Tatsoft Team,
Thank you so much for the answer, it work perfectly, just what I wanted, I just had to make a correction, in the last script, the line @Dataset.QUERYNAME.ExecuteCommand(); had to be @Dataset.Query.QUERYNAME.ExecuteCommand(); in case someone else need this information.

Now with this working I had some new questions:

  1. if the scheduled task runs every 30 days and I shutdown the run time or the pc, that time resets or still runing at the same point when it turn on again? for example if it turn off at the day 29 when it turn on again it will execute 30 days after it turns off or 1 day after?

  2. Its possible make the filename with any value storage in a tag? because if the file already exist it dont overwrite the file o make a copy, so I want to name the file with the date for example.

  3. Once I backup and delete the DB, can I import the csv file again to the db on the project? I think maybe I can not because its not practical but its just to know if its possible in any case

Best Regards,

Hello Miguel,

Thank you by the correction in the last script!

  1. If the computer is shut down during its execution, upon turning on the computer again, that time resets and task will execute after 30 days of shut down. We suggest you to run the task manually when the execution shuts down, doing the backup and deleting the data to start the next 30 days.

  2. Yes, you can use the server property “Server.DateString” and concatenate with the original string. On the example below, we used “Replace(‘/’,‘_’)” so the file wouldn’t have any naming issues:

TAlarmWindow alarm=this.CurrentDisplay.GetAlarmWindow("alarm") as TAlarmWindow;
string date=@Server.DateString.Replace('/','_');
  1. Yes, you can import the csv file again to the db on the project. To do this, follow the steps:
    3.1 - First of all, you have to edit your AlarmWindow to have the same parameters of the Alarms table, which is the table we will import to. You have to modify the name of following parameters:
  • Ack → AckStatus
  • Active Time → ActiveTime_Ticks
  • Norm Time → NormTime_Ticks
  • Ack Time → AckTime_Ticks
  • DateCreated → DateCreated_Ticks
  • Limit → AlarmLimit

    3.2 - Turn all of the Columns visible, except for “UserFullName”. It’s important to have all of them saved on CSV file, otherwise you can not import this file again to the table:

    3.3 - Paste the format “yyyy-MM-dd HH:mm:ss” on “Format” field of the following Columns: ActiveTime_Ticks, NormTime_Ticks, AckTime_Ticks, DateCreated_Ticks.
    3.4 - On “Datasets > Tables” create the table Alarms with “AlarmHistorian” DB and “Alarms” TableName:
    3.5 - Now, you can use the following code to import the CSV file to the Alarms table:
string csvFilePath = @"C:\FilePath\FileName.csv";

using (var reader = new StreamReader(csvFilePath))
    	DataTable dataTable = new DataTable("Alarms");
    	string[] headers = reader.ReadLine().Split(',');
        Dictionary<string, int> map = new Dictionary<string, int>();
        int index = 0;
        String str = "";
    	foreach (string header in headers)
    		str = header.Replace("\"", "");
    		map.Add(str, index);
    		if(str.Equals("ID") || str.Equals("ActiveTime_Ticks") || str.Equals("NormTime_Ticks") || str.Equals("AckTime_Ticks") || str.Equals("Category") || str.Equals("DateCreated_Ticks") || str.Equals("Duration"))
    			dataTable.Columns.Add(str, typeof(System.Int64));
    		else if (str.Equals("ItemName") || str.Equals("TagName") || str.Equals("Condition") || str.Equals("Value") || str.Equals("Message") || str.Equals("Group") || str.Equals("Area") || str.Equals("UserName") || str.Equals("Comments") || str.Equals("ColorFG") || str.Equals("ColorBG") || str.Equals("Level") || str.Equals("AuxValue") || str.Equals("PreviousValue") || str.Equals("AuxValue2") || str.Equals("AuxValue3"))
    			dataTable.Columns.Add(str, typeof(System.String));
    		else if (str.Equals("AckStatus") || str.Equals("AckRequired") || str.Equals("State") || str.Equals("Priority"))
    			dataTable.Columns.Add(str, typeof(System.Int32));
    		else if (str.Equals("NotSync"))
    			dataTable.Columns.Add(str, typeof(System.Boolean));
    		else if (str.Equals("AlarmLimit"))
    			dataTable.Columns.Add(str, typeof(System.Double));
    	str = "ActiveLocalTime";
    	map.Add(str, index);
    	dataTable.Columns.Add(str, typeof(System.DateTime));
    	while (!reader.EndOfStream)
        	string[] values = reader.ReadLine().Split(',');
        	DataRow row = dataTable.NewRow();

        	for (int i = 0; i < index+1; i++)
        	     if(i < index) str = values[i].Replace("\"", "");
        	     if(i == map["ActiveLocalTime"]) 
        		     row[i] = DateTime.Now;
        	     else if(str.Equals("") && i != map["ID"] && i != map["ActiveTime_Ticks"] && i != map["NotSync"]) 
        		     row[i] = DBNull.Value;
        	     else if (i == map["ActiveTime_Ticks"] || i == map["NormTime_Ticks"] || i == map["AckTime_Ticks"] || i == map["DateCreated_Ticks"]) 
        		     row[i] = (DateTime.ParseExact(values[i].Replace("\"", ""), "yyyy-MM-dd HH:mm:ss", null)).Ticks/TimeSpan.TicksPerMillisecond;
        		     row[i] = str;

        @Dataset.Table.Alarms.UpdateFromDataTable(dataTable, true);

Please give these steps a try and let us know if they work for you.

Bests Regards,
Tatsoft Team.

1 Like

Hello Tatsoft Team,
Thank you so much for the answer, it work perfectly!
I have not tried yet the import csv but I will try it later in case someone else need this information.

Best Regards,