This article from InSource shows how to use the Wonderware OnLine Historian InSight API to Extract, Transform and Load (ETL) data for use in other customized applications.
- Author: Chris Selph
- Published: 11/20/2017
- Applies to: Historian OnLine InSight
Wonderware's OnLine Historian has a default user interface for data visualization called InSight and is accessible from most any browser. There is also mobile access to this data repository from mobile devices via the SmartGlance Apple or Android app. However, advanced users may need to Extract - Transform - Load data into other enterprise applications such as Material and Planning systems, Maintenance Management systems or enterprise reporting repositories to name a few.
While it is possible to use the InSight charting apps to export data to Excel for external use, this is a very manual process. The idea is to use an automated system to periodically connect to the OnLine Historian and extract the last Day/Week/Month of data for a specific set of tags at a particular resolution. The can be accomplished via the provided REST API for both data and charts.
The API is fully documented and examples can be found in the InSight online help.
The ODATA interface allows developers to either POST or GET data to/from the Historian while either consuming or producing data structures in the JSON format.
Here, we will use a relatively simple browser query in this example. Here is the query URL and its components:
(Note this URL string has extra spaces for formatting purposes)
https :// online.wonderware.com/s/8uz2gw/apis/Historian/v1/ProcessValues? $filter= FQN+eq+%27Reactor%20Demo1.ReactLevel%27+and+DateTime+ge+datetimeoffset%272017-05-09T00:00:00.000-07:00%27+and+DateTime+le+datetimeoffset%272017-05-10T00:00:00.000-07:00%27&Resolution=3600000
https://online.wonderware.com/s/8uz2.../Historian/v1/ - This base URL includes a unique solution ID and can be found in Administration > Integration Settings > Historian Data REST API
ProcessValues? - Represents a data and event resource which also includes other resources like:
$filter= - Enables data filtering based on tag names, date ranges, data resolution etc
FQN+eq+%27Reactor%20Demo1.ReactLevel - The FQN filter is Fully Qualified Name followed by the tags you want to include in the query
DateTime+ge+datetimeoffset%272017-05-09T00:00:00.000-07:00%27+and+DateTime+le+datetimeoffset%272017-05-10T00:00:00.000-07:00 - This is the date range filter specifying data from May 9th 2017 at 7am to May 10th 2017 at 7am.
Resolution=3600000 - The data resolution filter specifies at what resolution data should be returned in the result set in milliseconds.
Executing the browser query
Note this query has no security credentials embedded so you will be challenged for user/pw/
This result set can then be used as input to other customized applications per your needs.
Here for visualization and testing, the data was manually pasted into a free utility (JSONTOTABLE.COM) than creates a nice table from the JSON results: