AVEVA™ Work Tasks

Retrieve Historian Data Using Database Activity

Save PDF

Retrieve Historian Data Using Database Activity

Save PDF

Last UpdatedJun 10, 2024
4 minute read

AVEVA Work Tasks has a powerful feature where it can connect to and retrieve data from Wonderware Historian Server using an AVEVA Work Tasks Database Activity. You can use this functionality in many scenarios, a few of which are described below:

Your network administrator needs data on the number of system status event tags that have been fired in a particular time-frame, which can then be used to optimize system performance.

Using the Database Activity, you can fetch data from the Historian database on how many SysStatusEvent event tags have been triggered in the last one week.

How you go about it:

Use the queries shown below in the database activity to get the desired output.

Sample Script 1: This script gets the Event Trigger history for a specific EventTag for a specific time-span.

--Get Event Trigger history for a particular EventTag for the last 10 days

SELECT isnull((select HierarchicalName from dbo.faaGetHierarchicalAttributeNames(Tag.TagName)),Tag.TagName)as HierarchicalName , Description, DateTime = convert(nvarchar, DateAdd(mi,60,DateTime), 21), DetectDateTime = convert(nvarchar, DateAdd(mi,60,DetectDateTime), 21)

FROM EventHistory

INNER JOIN Tag ON Tag.TagName = EventHistory.TagName

WHERE Tag.TagName IN ('SysStatusEvent')

AND DateTime >= DateAdd(DAY,-10,GetDate())

AND DateTime <= GetDate()

Samples Script 2: This script gets all the Event Trigger history for all EventTags for a specific time-span.

--Get All Event Trigger history for last 10 days

FROM EventHistory

INNER JOIN Tag ON Tag.TagName = EventHistory.TagName

WHERE DateTime >= DateAdd(DAY,-10,GetDate())

AND DateTime <= GetDate()

Samples Script 3: This script gets details of all Analog tags.

--Get detail of all Analog Tag

SELECT isnull((select HierarchicalName from dbo.faaGetHierarchicalAttributeNames(Tag.TagName)) ,Tag.TagName)as HierarchicalName , Description = Tag.Description, MinRaw, MaxRaw, Unit, MinEU, MaxEU, StorageRate, StorageType, DateCreated

FROM AnalogTag, Tag, EngineeringUnit

WHERE Tag.TagName = AnalogTag.TagName

AND AnalogTag.EUKey = EngineeringUnit.EUKey

To create Database connection object in the Workflow

Open the Database Connections object.
Click on New Connection.
Enter title, description and uncheck the "Connect to repository data source" check box
Database Type will be SQL server as the data store of Historian is Microsoft SQL server.
Keep the Connection Type as Connection string.
Mention the server name (Server name of Historian Database).
Select the Authentication type.

If Authentication type is "SQL server", then mention User Id, Password and Advance Settings.
Click the Refresh buttons to get all the databases in the specified server.
Select runtime for historian data.
Click Test Connection.

If the connection succeeds, the Save button will be enabled.
Click the Save button.

The connection string is stored.

To add the Database Activity

Database Activity is an activity inside the workflow wherein the workflow actually establishes the connection to the database server and performs actions to retrieve or update data in the database. The activity helps to execute Queries, Stored Procedure and Functions in workflows with ease.

The database activity can execute your queries, stored procedures and functions and carry the result in an XML variable where the schema will load the columns of the table in your database and will contain the value as per the given query.

For Insert and Update statements, we need not give the XML Variable Name since it does not return any value.

Hover the cursor on Enterprise Console and click on Workflows.

The Workflow window will open.
Click on the Workflow ribbon in the ribbon bar.
Enter the workflow name and description.
Click on Save and Continue. A window appears.
Click on Finish.

(Design the Workflow Now option should be checked before clicking on Finish: this is a default selection).

Workflow Design Editor will appear.
Add the database activity to the Workflow design.
Go to the properties of the Database Activity.
Change the Activity Name, if required. Add the Description, if required.
Select the database connection from the drop down and click on Save button.
Click on the Query Builder icon.

Query Builder

Select Command Type Query
Enter Query, you can use the same query mentioned I the scenario section.
Click on the Load Parameters button. It will load all the parameters below.
Enter the value of the parameter.
Enter the output XML variable name.
Save the information by clicking on the Save button.
If you want you can click on View Schema button to view the schema of the HistorianDataLoad variable as displaying in the below screen.
Click on Close button to close the view schema window.

Note: Multiple queries are not supported. If multiple queries are used, XML variable will carry the result of the last query written.

To log data in Console logger:

Close the query window and back to workflow design editor.

Drag and Drop "Logger" from Engine Activity. Right click on the logger image and click on property.
In Activity property, modify name if need. Add description if required.
Select "i – Information" as LogType.
Click on the right-most button to open the LogMessage property page.
Select XML Variable as Field.
The drop-down list will be populated on selecting the drop-down field.
Select "HistorianDataLoad" in the drop-down list.
Select Table in the last drop-down list and click on Append button.
After append, click on the Update button of the page.
On clicking Update, this page will be closed and the workflow design page will be on focus. Now we are done with the Workflow.
Click TestRun. Before clicking "TestRun", open the Logger Console.
If it succeeds, then check the Logger console whether all expected rows have been logged or not.

AVEVA™ Work Tasks

Retrieve Historian Data Using Database Activity

Table of Contents

Retrieve Historian Data Using Database Activity

Related Links