Query and Visualise AVEVA Historian on Grafana Dashboard

This post will outline the procedure to set up Grafana to query tags from an AVEVA Historian using Microsoft SQL Server connector and plot them on a chart. Users can select available tags from a list.

The reader is assumed to have prior knowledge of how to set up AVEVA Historian and Grafana.

Introduction of AVEVA Historian and Grafana

The AVEVA Historian is formerly known as Wonderware Historian. It is the default historian for AVEVA System Platform .

Bundled with the historian are the traditional Windows desktop application - AVEVA Historian Client Trend, and more recently the AVEVA Historian Web Client. Both the desktop and web application requires a valid license and is limited to a certain number of clients.

It is often desirable to display dashboards from multiple data sources, as well as allow more clients to access the dashboards. Grafana is a perfect solution for this.

Unlike AVEVA Insight , Grafana is vendor-neutral and can be hosted on a local machine or on a cloud platform. Not only does it offer a plethora of connectors and plugins, but also plenty of visualisation types to allow deep customisation of dashboards.

The procedure

Create Grafana user in Microsoft SQL Server

We need to add a user with read-only permission for grafana backend. Grafana's Microsoft SQL connector supports either SQL Server Authentication or Windows Authentication (single sign-on for Windows users). I chose grafana as the Login name and SQL Server Authentication. Set default database to Runtime

After creating the user, open the property window and choose db_datareader role for the Runtime database. This will prevent Grafana from modifying the database.

Add Grafana data source

Add a Microsoft SQL Server data source. The database name is Runtime.

Create a new dashboard and variables

AVEVA Historian provides a History virtual table to query a single tag, or a WideHistory table to query multiple tags. For this demonstration 2 variables are created:

tagname: the name of the tag to query from the History table
taglist: the list of tags to query from the WideHistory table

Both variables are set to Query type with the following query to get the list of available tags. Enable Multi-value for the taglist variable.

SELECT TagName FROM TagRef

Add trend

Retrieve a single tag from the History table:

SELECT
  $__time(DateTime),
  Value as value,
  TagName as metric
FROM
  History
WHERE
  TagName = '$tagname'
  AND wwRetrievalMode = 'Cyclic'
  AND wwResolution = $__interval_ms
  AND $__timeFilter(DateTime)
  AND wwTimeZone = 'UTC'
ORDER BY
  DateTime ASC

The WideHistory table is used to retrieve multiple tags at once. The OPENQUERY function is required for retrieving from the wide table.

DECLARE @TSQL varchar(8000), @DATE_FROM char(19), @DATE_TO char(19)

SELECT @DATE_FROM = REPLACE($__timeFrom(), 'T', ' ')
SELECT @DATE_TO = REPLACE($__timeTo(), 'T', ' ')
SELECT @TSQL = '
  SELECT DateTime AS time, ${taglist:raw} FROM OPENQUERY(
    INSQL, ''
      SELECT DateTime, ${taglist:raw}
      FROM Runtime.dbo.WideHistory
      WHERE wwResolution = $__interval_ms
        AND wwRetrievalMode = "Average"
        AND DateTime >= ''''' + @DATE_FROM + '''''
        AND DateTime <= ''''' + @DATE_TO + '''''
        AND wwTimeZone = ''''UTC''''
      ORDER BY
        DateTime ASC
    '')
'

EXEC (@TSQL)

Final result

The dashboard in Grafana:

Data retrieval via REST API

In more recent versions of AVEVA Historian, the ability to query data via REST API is introduced. AVEVA Historian Insight (on-premises) uses windows integrated security. The solution is to use an NTLM Authorization Proxy Server to allow Grafana to access the historian via the REST API. Please leave a comment if you would like to know more details or have anything to add or discuss :)

Bill Liu's Blog