# Logging downtime **URL:** \ **Category:** Ignition\ **Created:** [November 28, 2022, 9:24am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962 "2022-11-28T09:24:59Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Laima\_Meilute](https://avatars.discourse-cdn.com/v4/letter/l/9de0a6/32.png) [@Laima\_Meilute](https://forum.inductiveautomation.com/u/Laima_Meilute)\ **Post date:** [November 28, 2022, 9:24am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/1 "2022-11-28T09:24:59Z")
Hey everyone, I've been stuck on a task for some time and can't seem to find any raised topics on this - if I've just missed them, I'm really sorry. I want to log how long a machine is not operated during change-overs and we currently don't have any specific signals for the start and end of this process, so my solution is to measure the time between OEE drop below 20% and then jump back over 20%. I've created a transaction group and made an hour meter for this, but the problem is with logging it - I've set the table action to insert a row, but it keeps addidng every second. How could I log the settled value only? Thanks in advance. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [November 28, 2022, 9:28am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/2 "2022-11-28T09:28:39Z")
When, and where, do you want to log it ? ---
**Author:** ![Laima\_Meilute](https://avatars.discourse-cdn.com/v4/letter/l/9de0a6/32.png) [@Laima\_Meilute](https://forum.inductiveautomation.com/u/Laima_Meilute)\ **Post date:** [November 28, 2022, 9:32am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/3 "2022-11-28T09:32:11Z")
I want to log every time the counter stops and only then, for ex.: currently the machine has been stopped for 15453 seconds due to a cleaning procedure, currently it gives 15 thousand lines on every second, while I want only the 15k value logged - just to get how long the machine was down and at what time. Where - in a DB or another tag, I'm not sure - my end goal is to present the values on a table, to showcase how long changeovers take. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [November 28, 2022, 9:42am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/4 "2022-11-28T09:42:46Z")
If you're using the historian, you could historize the tag you're using, and simply process the history when you want a report. Otherwise, I'd create a boolean tag, let's call it `notOperated` that monitors your OEE and becomes `True` if OEE is under 20, something like this: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/5/0561ca2609466277f184214bcfe013ae67b467fc.png) Then you can use this as a trigger, and its timestamp to calculate how long it stayed `True`. ---
**Author:** ![Laima\_Meilute](https://avatars.discourse-cdn.com/v4/letter/l/9de0a6/32.png) [@Laima\_Meilute](https://forum.inductiveautomation.com/u/Laima_Meilute)\ **Post date:** [November 28, 2022, 9:47am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/5 "2022-11-28T09:47:02Z")
That is almost precisely what I have done - I have created a tag, that is true when an alarm on OEE is active, I then created a transaction group to have an hour meter: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/6/866adb2c6bfa7485a0e03c171ecc09f21921d110.png) The problem with this is how it writes the meter to a DB: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/9/2928dd749395a9ec1a2d96a40dcbfe4d57106de0.png) It adds a new row every time a second is added. How can I log the end, settled value, when the machine turns on again? ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [November 28, 2022, 9:51am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/6 "2022-11-28T09:51:40Z")
Use a tag change script (I'd go for a gateway event, but a script directly on the tag would also work). You may or may not need a secondary tag to store timestamps, as I don't remember if the `previousTimestamp` is available in tag change scripts. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [November 28, 2022, 5:46pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/7 "2022-11-28T17:46:37Z")
The problem is likely how you have the Transaction group configured. What are you using as the trigger? ---
**Author:** ![Laima\_Meilute](https://avatars.discourse-cdn.com/v4/letter/l/9de0a6/32.png) [@Laima\_Meilute](https://forum.inductiveautomation.com/u/Laima_Meilute)\ **Post date:** [November 28, 2022, 9:19pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/8 "2022-11-28T21:19:49Z")
In the trigger tab I haven't set anything in particular if that is what you are asking ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/9/891e4278dba84ec6c8b4fbaa4fb6b502b2ad7bb2.png) I just have a tag that becomes true when OEE is below 20, and that automatically becomes false when OEE is back over 20 ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [November 28, 2022, 9:24pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/9 "2022-11-28T21:24:58Z")
> [@Laima\_Meilute](#): > > I just have a tag that becomes true when OEE is below 20, and that automatically becomes false when OEE is back over 20 You should set you transaction group up so that this tag is the Trigger. Add it to the group as an Item if you haven't already, set the option to execute this group on a trigger, and then set the Trigger on Item to the item that is bound to this tag. Select: - Only execute once while trigger is active - Prevent trigger caused by group start (unless you want that to happen) - Active on value change You could also use the actual value as the trigger, but I find it is clearer to use a boolean tag as a trigger, especially since you already have one. This should make it so that the transaction is only triggered when your trigger tag changes, then you can use SQL to calculate the difference between when the records were stored. Right now the transaction is triggered when ever any tag in the group changes, resulting in a record for every second. ---
**Author:** ![Laima\_Meilute](https://avatars.discourse-cdn.com/v4/letter/l/9de0a6/32.png) [@Laima\_Meilute](https://forum.inductiveautomation.com/u/Laima_Meilute)\ **Post date:** [November 30, 2022, 7:59am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/10 "2022-11-30T07:59:39Z")
Thank you for your detailed answer. However the part I am struggling with is precisely the one you skipped over 😃 I am struggling with differentiating the values from SQL, as the transaction group stores the hour meter every second that it is active, and I only need the value when it becomes false again. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [November 30, 2022, 8:13am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/11 "2022-11-30T08:13:15Z")
> [@lrose](#): > > Only execute once while trigger is active This should prevent your data to be logged every second. It would log it just once. ---
**Author:** ![Laima\_Meilute](https://avatars.discourse-cdn.com/v4/letter/l/9de0a6/32.png) [@Laima\_Meilute](https://forum.inductiveautomation.com/u/Laima_Meilute)\ **Post date:** [November 30, 2022, 9:36am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/12 "2022-11-30T09:36:49Z")
Oh, sorry, my bad for reading that wrong. I tried making the trigger and I do manage to get a singular value, and I'm really sorry for bothering you guys so much, but I with the trigger I am stuck on the fact, that if I create the trigger to be activated when the value is true, it writes a singular first second, not the last, and if I make it write when the tag becomes false, it only writes zero (tried battling this with making the seconds retentive, bet then the trigger doesn't go off, because the value isn't zero) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 30, 2022, 2:11pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/13 "2022-11-30T14:11:29Z")
I recommend you not use the hour meter. It is meant for use with repeated recordings. Instead, use an "Any Change" trigger and just log the current value. You can extract the durations from the timestamps using SQL LEAD() or LAG(). Some examples: > [@Calculating Total Time in Seconds](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/4): > > I usually do all of this in the database with lead() and/or lag() window functions. Say you have multiple of these recording to a single table, using an id column to segregate them. You record every change, and due to tag restarts or script restarts you get some duplicate rows as you’ve shown. The query to pick out all of the completed cycles that overlap a time period would be something like this: Select id, t\_stamp, finish\_ts, finish\_ts - t\_stamp As duration From ( Select id, t\_st… > [@SQL Query Group data in Table 2 by time events in Table 1](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/8): > > I presume you want to ignore repeat ones and zeros in the output column? If so, running periods from table1 would be something like this: Select t\_stamp as start\_ts, lead(t\_stamp, 1, current\_timestamp) Over (Order by t\_stamp) as end\_ts From ( Select t\_stamp, output From ( Select t\_stamp, output, lag(output, 1, 0) Over (Order By t\_stamp) as lag\_out From table1 ) innerq Where output != lag\_out ) changesq Where output = 1 Order By start\_ts And similarly, downtime pe… And [more here](https://forum.inductiveautomation.com/search?q=%22lead%28%22). ---
**Author:** ![Nick\_Heiting](https://avatars.discourse-cdn.com/v4/letter/n/8797f3/32.png) [@Nick\_Heiting](https://forum.inductiveautomation.com/u/Nick_Heiting)\ **Post date:** [December 2, 2022, 7:45pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/14 "2022-12-02T19:45:59Z")
Would another option be to use the transaction group - Standard Group rather than the Historical Group. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/9/a9a537b1b1267704aac2eae90ce69276d87f000a.png) I've done this when recording batch data to our database. It has the option to either update an existing database entry or create a new entry. In this case you would need another tag to differentiate one downtime event from the next. - Another option would be to think about logging in a different way. I capture downtime events by having a tag setup to monitor or calculate the status for a piece of equipment. When the downtimeEventActive tag turns on indicating a downtime event started the tag script copies the current time to a eventStartTime tag. When the tag turns off it copies the current time to a eventEndTime tag then subtracts the eventStartTime from the eventEndTime to get a total duration of the event. Finally, the script adds one to a transaction trigger that is setup to trigger a Transaction Group to store the desired tags to a database. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/a/4aa0207ced7f023cbf4eefa03caadc2c2e1d3361.png) ---
**Author:** ![Shaheryar\_Ahmad](https://avatars.discourse-cdn.com/v4/letter/s/f6c823/32.png) [@Shaheryar\_Ahmad](https://forum.inductiveautomation.com/u/Shaheryar_Ahmad)\ **Post date:** [December 11, 2022, 6:01pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/16 "2022-12-11T18:01:10Z")
@pturmel Is it okay, instead of using transaction group, if we enable the 'Tag history' on that tag whose downtime/uptime is to be monitored and then use SQL query to get the duration? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 11, 2022, 6:38pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/17 "2022-12-11T18:38:50Z")
Querying tag history with your own SQL is very complicated. The tag historian's bindings and scriptable query tools do not have the equivalent of LEAD() and LAG(). A dedicated table for events, using either the SQL Bridge module (transaction groups) or scripted inserts, will be much easier to work with. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [December 11, 2022, 8:50pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/18 "2022-12-11T20:50:22Z")
@Shaheryar_Ahmad Personally I would rather use tag history and then get the data back by scripting. As Phil suggested, use SQL queries for tag history data can get hairy, but using tag history and scripting is rather simple IMO. ---
**Author:** ![Shaheryar\_Ahmad](https://avatars.discourse-cdn.com/v4/letter/s/f6c823/32.png) [@Shaheryar\_Ahmad](https://forum.inductiveautomation.com/u/Shaheryar_Ahmad)\ **Post date:** [December 12, 2022, 7:16am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/19 "2022-12-12T07:16:21Z")
@jlandwerlen Okay I have done the same, enabled tag history on a tag named 'OnState\_Button' and then use a script on this tag (on value change) picture below. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/f/0f5327ba5c707864000fed069f7ae733e48163e4.png) Issue is, the system.tag.writeBlocking is not storing duration output value into the other tag. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [December 12, 2022, 10:20am UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/20 "2022-12-12T10:20:09Z")
When seeking help about something that doesn't work, it's usually a good idea to make it clear what behavior you're observing. Like, error messages, etc. But don't use spaces in tags names. As a general rule, if it's not a valid python variable name, it's not a good idea to use it for a tag name. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 12, 2022, 12:57pm UTC](https://forum.inductiveautomation.com/t/logging-downtime/66962/21 "2022-12-12T12:57:32Z")
Don't run the query in the tag event script. Run it in a timer event with dedicated thread, to update your duration tag steadily even when the state tags aren't changing.