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,
Miguel
Hello Miguel,
Thank you for getting in touch with us.
To create a backup of your alarm history, follow these steps:
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.
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:
Go to Edit > Datasets > Queries and create a query in the AlarmHistorian DB.
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";
@Dataset.QUERYNAME.ExecuteCommand();
alarm.Refresh();
Please give these steps a try and let us know if they work for you.
Best Regards,
Tatsoft Team.
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:
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?
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.
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,
Miguel
Hello Miguel,
Thank you by the correction in the last script!
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.
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('/','_');
alarm.ExportToCsv(@"C:\FILEPATH\AlarmHistory_"+date+".csv",",");
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));
index++;
}
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;
else
row[i] = str;
}
dataTable.Rows.Add(row);
}
@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.
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,
Miguel