Historizing Fault tags data

I am creating a fault tag history table in SQL database. We have about 70 machines. In each machine’s PLC program we have several (about 400 of them) tags that are fault tags. They have either 0 or 1 value. 1 meaning fault occurred. Most of the times all of these tags have values 0. Maybe 2 or 3 of them may have values 1 at some point. These tag names are already in an excel file. So using excel to JSON I am able to import all of these 400 tags into Ignition as OPC tags. Then I created 1 transaction group and added all these tags and configured the Ignition transaction group to record value only if it changes. So now I have an SQL table for each machine with 400 columns and most of the values being 0 and very few as 1. Lets say the columns names (tag names) are col1, col2, col3…col400. Additional columns are id and t_stamp. I want to take this SQL table (wide format) and create another SQL table (normalized, narrow format) which will have only 5 columns – id (new id not the original table id), machine_name (which I already know since the original table belongs to only 1 specific machine), fault_name (this would be one of those 400 columns), start_date_time, end_date_time. We want to start_date_time when the fault is 1 and stop_date_time when that fault is back to 0.
I am thinking of doing this conversion by programming (python or .net). But before I do that I wanted to share my thoughts and see if there is a better and easier way of achieving what I want to.

Why do you need an alarm history ?

You can use the alarm system of ignition instead. It already create a table with all alarms event data (timestamp, source, priority, etc...).
And these alarms can be natively used in an alarm banner to display the current actives alarms

  1. Alarms are already in PLC. So instead of creating new alarms we just want to pull those tags. These alarms in PLC are bit complicated since there is some logic in the PLC on what conditions/events or combination of conditions/events trigger what alarms.
  2. Current HMI / OIT interface already displays current alarms for the operators. So we are good from the operator perspective, What we lack is to see historical trend of these tags especially for the engineers/technicians. The idea is to have this data available for dashboarding/Power BI or any other application

So.... just create tags in Ignition and create an alarm on them and then let the alarm journal log them so you can get a history of them later. You can set the Display Path for the alarm to be the machine_name so you can filter on that later.

Or are you wanting to actually trend them? If so, then just enable history on them as well.

No reason to create new alarms in the PLC or disturb your existing HMIs.

You can add an alarm on the True status of these tags, not delayed or whatever.
Then use the alarm history view with a filter on these tags to only view them.
Or if you want something more custom, you can run query against the alarm journal database and get what you want.

The database will be automaticaly create, and populated.
I think is a waste of time to recreate this, just use what Inductive Automation has done for you.

2 Likes

Thank you very much