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.
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;
With scripting. Grab the data with system.tag.queryTagHistory - Ignition User Manual 8.1 - Ignition Documentation 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.
Yes, I recommend the view() expression function from my free Simulation Aids module. It supports filtering and grouping and pivoting in a Pseudo-SQL format.
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.
Did you see the part where this module is free ?
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.
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
They have a column for the time though.
I am trying to get that from the query.
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:
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.
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.
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.
That is correct.
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.
The link for your Simulation Aids is not working or it has been moved?
It's name was changed: