# Historian help needed **URL:** \ **Category:** Ignition\ **Created:** [September 23, 2021, 7:16pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281 "2021-09-23T19:16:24Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 23, 2021, 7:16pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/1 "2021-09-23T19:16:24Z")
I am still trying to figure out how to use the tag historian. for example I get a tag for an event code. If it is like 2210 then I know the part was too small, shrunk too much 2211 too big, didn’t shrink enough 2212 missing a hole for the keychain 2213 deformed from the mold I also have a tag that turns to 1 when a new event happens, and 0 when there isn’t a new event. I have some ints for date and time. how do I get the number of occurrences per shift and per hour of each code? On the labels I display, do I make a binding for tag historian query? ---
**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\ **Post date:** [September 23, 2021, 7:27pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/2 "2021-09-23T19:27:55Z")
You probably want to use a transaction group for this instead of tag history. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 23, 2021, 7:31pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/3 "2021-09-23T19:31:18Z")
are there videos on that, I don’t remember seeing that in the training edit: found the videos ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [September 23, 2021, 7:47pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/4 "2021-09-23T19:47:35Z")
I disagree, I think tag history is great to use for what you need. For your event, if you trust that you won't have multiple 1s or 0s in a row, then use count aggregate for one hour interval. If you could have multiples, then you will want to use a script. Something like the one below that @JordanCClark did, but instead of total time, you would change to total count. > [@Calculating Total Time in Seconds](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/3): > > This may help you out: # Create sample dataset sampleHeaders = ['t\_stamp', 'value'] sampleData = [['2021-07-01 13:42:28', 1], ['2021-07-01 13:43:23', 1], ['2021-07-01 13:43:28', 0], ['2021-07-01 13:43:29', 0], ['2021-07-01 13:43:30', 1], ['2021-07-01 13:43:32', 1], ['2021-07-01 13:43:36', 0], ['2021-07-01 13:43:39', 1], ['2021-07-01 13:43:41', 1], ['2021-07-01 13:43:45'… ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 23, 2021, 8:22pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/5 "2021-09-23T20:22:51Z")
I watched the videos for the transaction group. I see the dataset that was created. I do not understand how I go from receiving the event codes to output a 9 or 5, however many times the code read for each different code. I want to say that it is super hot and for some reason heat is being blown on me at work. It is not helping haha. ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [September 23, 2021, 8:32pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/6 "2021-09-23T20:32:46Z")
You are going to have some difficult because you are trying to do two different calculations at once. Your tag that is 1 when an event happens can be pretty easily done with a tag calculation query, but the count of the occurrences of the codes is another story. If it were me i would do it with regular sql query. You can actually perform your aggregation for the two different types of calculations, then join/union your results together. Here’s a example query using a tag that is logged with historian, with partitioning off. This will get you your code counts by the hour. Im sure there is a way to do this with partitioning enabled. ```auto SELECT tagpath, from_unixtime(t_stamp/1000) as tstamp, intvalue, count(intvalue) as codecount FROM rounds_test.sqlth_1_data inner join sqlth_te on sqlth_1_data.tagid = sqlth_te.id where tagpath like 'codetag' group by intvalue, hour(from_unixtime(t_stamp/1000)) ``` Also if you want to have this data span long time periods you will need to ammend the group by to include day, year. You could also not do the aggregation here, but instead do it on the table using grouping. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 24, 2021, 12:55pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/7 "2021-09-24T12:55:48Z")
I don’t think my scan time will work well with that tag that is 1 when a new code comes. I have a different set of tags for the date time. int hour int minut int day int second int month int year coming from the plc that I will need to work with instead I think Scantime is like 10seconds and that 1 comes on for less I found out. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [September 24, 2021, 1:23pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/8 "2021-09-24T13:23:47Z")
Build a stack then. When all of the data is ready, send it. Do searches for FIFO, even on this forum, it has been discussed before. Edit, is that 10 second scan a periodic or the result of poor programming and using continuous task? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 24, 2021, 1:32pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/9 "2021-09-24T13:32:15Z")
wow periodic ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 24, 2021, 1:50pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/10 "2021-09-24T13:50:56Z")
partitioning is on for me ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [September 24, 2021, 2:04pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/11 "2021-09-24T14:04:42Z")
Now that you have gotten more specific on what you have, I think a transaction group is your only choice. You will need to build the data, then send when ready. Initially it sounded like the data was separate. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 24, 2021, 2:55pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/12 "2021-09-24T14:55:29Z")
Nevermind, I think I will write a counter in the plc. When I read the counter goes up, I know there is a new event code. Then I will write a script that reads the code. Make a bunch of tags, then add 1 to the appropriate tag. Just not sure how to do the 1 hour total. It seems like tag historian has a 1 hour count option, but I don’t know if it works across a reset. Like if the value were 9, then shift change reset it to 0, would the tag history binding count the ones that happened in the last hour and be 4 or however many were just in the last hour? Or do I make them all pulses? ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [September 24, 2021, 3:02pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/13 "2021-09-24T15:02:56Z")
> [@zacharyw.larson](#): > > Just not sure how to do the 1 hour total. It seems like tag historian has a 1 hour count option, but I don't know if it works across a reset. Like if the value were 9, then shift change reset it to 0, would the tag history binding count the ones that happened in the last hour and be 4 or however many were just in the last hour? If you use count aggregate it shouldn't matter if you reset. ---
**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\ **Post date:** [September 24, 2021, 3:08pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/14 "2021-09-24T15:08:16Z")
Yes, make some kind of trigger that indicates a new record/part/whatever has been processes so you know it’s a new part even if the event code doesn’t change Then just create a transaction group with a Execution time lower than the shortest time between two records could be in your process: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/1/d16daf373beeac733bc35dab92cf697a1a4e337b.png) Store the timestamp for each record: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/2/f2ad1298c31da5497c223a86bf64548f4e8d2bad.png) Then you can assign that trigger value as a trigger for the transaction group, something like this: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/5/75c20f0f144fd5813f5864b022712052fa259220.png) Then you can just do a aggregate by hour with a SQL query once you have records with timestamps. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 18, 2021, 9:34pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/15 "2021-11-18T21:34:24Z")
A bunch of time has passed. I don’t understand many ways to make the tag historian useful. So many tags are historized with expectations that it would be so useful. I open a table on perspective, bind the tag history, and it shows the t\_stamp in epoc. * * * I got some things to be useful in tag historian. Like I historized the tags. Then on an object in perspective, I setup a few properties to show arrays of all the values at a certain time in history, then another array at a different time to get the difference. * * * Is it just me, or are transaction groups a lot better if I am doing anything other than a line graph? I am about to make a transaction group for tags that are being historized… ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [November 18, 2021, 9:37pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/16 "2021-11-18T21:37:54Z")
I’m on the other side of the fence in that i find the historian much more useful than transaction groups. I come from a SQL background, so i am much more comfortable manipulating the data with queries, and transaction groups you don’t necessarily need to have any SQL knowledge. That said you don’t actually need to use either, you can write data however you like with scripting. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 18, 2021, 9:45pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/18 "2021-11-18T21:45:33Z")
if you have a tag historized that shows you the reason for a stop as a 4 digit number, how do you get the top five stops in the last month with the historian? I was taught how to do it with the transaction groups in a named query ```auto SELECT Top 40 myCode, sum(myQuantity) as Quantity, count(myCode) as myCode_Count FROM myTable where ( t_stamp > DATEADD(minute, -5000, GETDATE())) Group by myCode order by sum(myQuantity) DESC; ``` it is like this for the top 40 I believe * * * I tried to delete the post I had before this. Are people able to see that post how it was before I had deleted it? Trying to keep my code generic. ---
**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 18, 2021, 9:53pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/19 "2021-11-18T21:53:52Z")
Y'all might find this topic helpful: > [@SQL Tall/Long vs Wide Table Structure](https://forum.inductiveautomation.com/t/sql-tall-long-vs-wide-table-structure/26231): > > I am in the process of trying to collect high speed historical data to an MSSQL database. We would like to collect data to set up a predictive maintenance system. We would like to collect information like temperature, vibration, voltage, current, etc. on CNC machines on a per machine cycle/part cycle basis at a 100mS sample rate. We would then like to compare the charts based on machine/part cycle to set thresholds and detect abnormalities before they become breakdowns. We are collecting all… Further things to consider: - Anything you can do with a transaction group you can do with a gateway timer script or a gateway tag change event. Scripted manipulation of wide tables can perform more complicated and/or more robust handshaking with PLCs than transaction groups. - If you only care about a particular data item's relationship to _ **time** _, and you only need to store it when it changes, then it is a good candidate for the tag historian. - If the value represents a process reading (analog perhaps) that has a bit of jitter you can ignore, then it is a **very** good candidate for the tag historian. - If you care about a particular data item's relationship to _ **other items** _, they probably need to be sampled and stored together. Use a wide table, not the historian. If sampling on a trigger, use OPC Read mode in the transaction group, or `system.opc.read*()` in a script, to ensure you have the associated data from **after** the trigger change. If you need the data stored in a SQL DB in a predictable structure for non-Ignition access, avoid the tag historian. Unless you are willing to implement a web API with the webdev module to deliver data to external consumers. ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [November 18, 2021, 10:28pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/20 "2021-11-18T22:28:18Z")
What @pturmel said. Somethings are best in the historian, others in transaction groups, and yet other times it is easier to script it based on what is going on when that data needs to be logged. Part of the trick is knowing when to use what technology. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [November 19, 2021, 1:15am UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/21 "2021-11-19T01:15:08Z")
Tag history is very convenient, transactions are not. That’s my opinion. Before you make your decision get very familiar with both. I have a feeling you don’t know either very well at all. [Next page](https://forum.inductiveautomation.com/t/historian-help-needed/51281.md?page=2)