# 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:** 12\ **Page:** 2
**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 19, 2021, 1:31pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/22 "2021-11-19T13:31:09Z")
how do you get the top five event codes in the last month with the historian? I can get it from the transaction group. I don’t know how to get it from the historian. ```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; ``` ---
**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:37pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/23 "2021-11-19T13:37:34Z")
With scripting. Grab the data with [system.tag.queryTagHistory - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory) then write code to do what you want. I believe @pturmel has a module to make querying a dataset easier. There is a lot of help online for Python. ---
**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 19, 2021, 2:34pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/24 "2021-11-19T14:34:50Z")
Yes, I recommend the [view()](https://www.automation-pros.com/simaids/doc/expression.view.html) expression function from my free [Simulation Aids](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids) module. It supports filtering and grouping and pivoting in a Pseudo-SQL format. ---
**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 19, 2021, 9:52pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/25 "2021-11-19T21:52:14Z")
I need to get by without adding a module or resource if possible. I see how to get the dataset from the historian with the system.tag.queryTagHistory I don’t know how to query it though. Is there some way to point the queries at the dataset and use named queries on it? So far, I only can do queries on the transaction group tables. I put the name of the table made by the transaction group in the from section, and I am able to query. ---
**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 19, 2021, 11:31pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/26 "2021-11-19T23:31:41Z")
> [@zacharyw.larson](#): > > I need to get by without adding a module or resource if possible. Did you see the part where this module is _ **free** _ ? > [@zacharyw.larson](#): > > point the queries at the dataset That is precisely what my `view()` function does. Nothing else that I've seen anywhere in Ignition will do this for you. Your alternative is to script the grouping and pivoting yourself. ---
**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 22, 2021, 1:43pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/27 "2021-11-22T13:43:04Z")
figuring out how to install on my local tried importing the project “ClassNotFoundException: com.automation\_pros.simaids.TransientDataset” installed module trying the import again "Provider ‘default’ not found: " The demo window is running. I am not sure where or how to use View(). * * * In my production project, I have a historized tag for the fault code. I am adding it to a transaction group. I knew how to make it into a dataset, but I didn’t know how to query that dataset. Still trying to figure out how to query the fault code durations. Found this [How To: Create a Downtime Report | Inductive Automation](https://inductiveautomation.com/news/how-create-downtime-report) They have a column for the time though. I am trying to get that from the query. ---
**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 22, 2021, 3:03pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/28 "2021-11-22T15:03:16Z")
So I presume you’ve figured out how to get your fault code history using a tag history binding or a named query binding to get one row per event, yielding a t\_stamp column and the fault code column. Also presumably limited to the time period of interest. Hook such a binding up to a _ **custom** _ dataset property on a container or a table. Then, on the table’s _ **data** _ property, place this _expression binding_: ```auto view("Selec faultCode, len(faultCode) As Qty Group By faultCode Order By -Qty", {Root Container.path.to.table.customProp}) ``` Put your actual column and property names in that. ---
**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 22, 2021, 3:29pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/29 "2021-11-22T15:29:11Z")
thanks Simulation Aids is on my local. The datasets I need to work on are on production, and I am not authorized to add modules to the gateway or add resources from exchange to the project. * * * I didn't know `Partition By` was a thing in queries. I also need to read up on lead and lag. > [@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… This thread was linked early in this thread, and I like your post on it. I think I need to implement that kind of query. * * * I don't think a tag historian binding has "partition by" functionality. I think for this fault code , I have to use the transaction group since I can't add the resource that you made. ---
**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 22, 2021, 3:45pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/30 "2021-11-22T15:45:53Z")
> [@zacharyw.larson](#): > > I don’t think a tag historian binding has “partition by” functionality. That is correct. ---
**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 22, 2021, 3:53pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/31 "2021-11-22T15:53:14Z")
> [@zacharyw.larson](#): > > I am not authorized to add modules to the gateway I understand this. Adding and upgrading modules can be disruptive--if the module has any elements in Vision Client and/or Designer scopes, those scopes are forced to restart. Gateway scripting also restarts, similar to project saves. I recommend you encourage adoption of my Simulation Aids module at some point though, properly coordinated with production. I consider it essential, which is why I made it free. ---
**Author:** ![Zach\_Larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zach_larson/32/54388_2.png) [@Zach\_Larson](https://forum.inductiveautomation.com/u/Zach_Larson)\ **Post date:** [February 22, 2024, 12:29pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/32 "2024-02-22T12:29:12Z")
The link for your Simulation Aids is not working or it has been moved? ---
**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:** [February 22, 2024, 12:47pm UTC](https://forum.inductiveautomation.com/t/historian-help-needed/51281/33 "2024-02-22T12:47:30Z")
It's name was changed: > [@Automation Professionals' Integration Toolkit Module](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934): > > Automation Professionals is pleased to announce the immediately availability of a major upgrade to its free Simulation Aids Integration Toolkit module. The new feature justifying the "major upgrade" language is the implementation of eleven thirty-one new expression functions which use iteration to accomplish complex transforms of datasets and lists, to or from other datasets and lists, applying filtering, ordering, and computed new columns along the way. While there are certain operations that… [Previous page](https://forum.inductiveautomation.com/t/historian-help-needed/51281.md?page=1)