Wonderware Historian SQL Query Guide, Plus PI Web API

To query Wonderware Historian (now AVEVA Historian) with SQL, open the Runtime database on its SQL Server and select from the History view with a TagName filter, a DateTime range and a wwRetrievalMode. For one column per tag, wrap the query in OPENQUERY(INSQL, '...'). For AVEVA PI System, use PI Web API, AF SDK or PI SQL Client.

Both products belong to AVEVA now. Wonderware Historian is sold as AVEVA Historian, and AVEVA completed its acquisition of OSIsoft on March 19, 2021, so the PI System is now the AVEVA PI System. Some plants run both, with Historian next to System Platform and InTouch on the line and PI as the site or enterprise archive. Below are the supported ways to get data out of each, sample queries for SQL Server Management Studio, and when to stop paying for a historian at all. For the wider method, see our guide to legacy system integration and reverse engineering with AI.

How do you query Wonderware Historian with SQL?

You query it through SQL Server. Historian stores process data in history blocks on disk, outside the database, and exposes it through an OLE DB provider named INSQL that the installer registers as a linked server. SQL Server hands your WHERE clause to that provider, and the provider reads the blocks and returns rows.

AVEVA's documentation describes the INSQL OLE DB provider as something you cannot call on its own. A client always connects to SQL Server first. From there you have three ways to address the data:

• Four-part names, such as INSQL.Runtime.dbo.History. The four-part naming page notes that SQL Server rewrites the statement before passing it on, and sometimes drops the WHERE clause, which produces Msg 7320: "InSQL did not receive a WHERE clause from SQL Server."
• The views in the Runtime database that carry the same names as the extension tables, so SELECT ... FROM History works without the linked server prefix. AVEVA's own examples use this form.
• OPENQUERY(INSQL, '...'), which sends your query text to the provider unchanged. It is required for the WideHistory table, it caps the passed statement at 8,000 characters, and it does not accept variables.

The extension tables worth knowing are History (one row per tag per timestamp), WideHistory (one column per tag), Live, AnalogSummaryHistory, StateSummaryHistory and Events. Older reports often hit the legacy AnalogHistory, DiscreteHistory and StringHistory tables, which AVEVA keeps for backward compatibility. A minimal query against the History view looks like this:

-- Run in the Runtime database on the Historian's SQL Server
SELECT DateTime, TagName, Value, Quality, OPCQuality
FROM History
WHERE TagName IN ('ReactLevel', 'ReactTemp')
  AND DateTime >= '2026-10-24 06:00:00.000'
  AND DateTime <  '2026-10-24 07:00:00.000'
  AND wwRetrievalMode = 'Cyclic'
  AND wwResolution = 60000;   -- one row per tag per minute

Leave out wwResolution or wwCycleCount on a cyclic query and you get 100 rows per tag, spread evenly across the time range. The default is documented on the History query page, and it catches anyone who expected a full export.

If a four-part query fails with Msg 7320, run the same statement inside OPENQUERY, where the provider receives your WHERE clause exactly as written. To find tag names, query the Tag table in the same Runtime database, which carries TagName, TagType and Description. When you filter history by tag metadata, AVEVA recommends INNER REMOTE JOIN over a WHERE TagName IN (SELECT ...) subquery:

SELECT DateTime, T.TagName, vValue, Quality, QualityDetail
FROM Tag T INNER REMOTE JOIN History H
  ON T.TagName = H.TagName
WHERE T.Description LIKE 'Reactor%'
  AND DateTime >= '2026-10-24 06:00:00.000'
  AND DateTime <= '2026-10-24 07:00:00.000'
  AND wwRetrievalMode = 'Delta';

AVEVA also publishes a sample-WonderwareHistorian repository on GitHub. Its README describes the scripts as unsupported stored procedures, prefixed wwkb, that add objects to the Runtime database without changing existing ones. Read them before you install them on a production historian.

What do wwRetrievalMode, wwResolution and wwCycleCount do?

wwRetrievalMode decides whether you get stored values or calculated ones. wwResolution sets the cycle length in milliseconds. wwCycleCount sets the number of cycle boundaries, and AVEVA computes the cycle length as the query duration divided by (n - 1), so a cycle count of 4 over one minute gives 20-second spacing, per the cycle count page. Set one of the two. These are columns on the History table that you set in the WHERE clause, and the History table reference lists all of them.

The default mode depends on the table. It is DELTA for the History table, CYCLIC for the legacy analog tables, and DELTA for discrete and string tables. wwRowCount still works but is deprecated in favor of wwCycleCount. The modes that matter for extraction:

Best fit is easy to overlook. AVEVA's best fit page gives the example of one week of five-second data: 120,960 rows in delta mode versus about 300 in best fit. That trade suits a trend in a web app. A warehouse load needs every point, so use FULL there.

Four other parameters change results enough that you should set them explicitly. wwTimeZone defaults to converting stored UTC timestamps to the Historian server's local time, daylight saving included, and 'UTC' keeps them in UTC. wwVersion picks ORIGINAL or LATEST when values have been edited, and LATEST is the default. wwQualityRule (GOOD, EXTENDED or OPTIMISTIC) controls whether doubtful-quality points feed calculations. wwInterpolationType (STAIRSTEP or LINEAR) decides how interpolated, best fit, average and integral retrieval value a cycle boundary that has no stored point.

Sample SQL queries for exporting Historian data

The three queries below cover most extraction jobs: an incremental raw export, an hourly rollup and a wide pivot with date variables. They use AVEVA's demo tags ReactLevel and ReactTemp; swap in your own tag names and test on a short range first.

Incremental raw export with a watermark

-- Every stored value after the last successful load. Bounds are UTC.
SELECT TagName, DateTime, Value, vValue, Quality, QualityDetail, OPCQuality
FROM History
WHERE TagName IN ('ReactLevel', 'ReactTemp')
  AND DateTime >  '2026-10-24 00:00:00.000'   -- exclusive: last watermark
  AND DateTime <= '2026-10-25 00:00:00.000'
  AND wwRetrievalMode = 'Full'
  AND wwTimeZone = 'UTC';

The start bound uses >, not >=. With an inclusive start, delta and full queries return a row at the start time even when nothing was stored there: the last earlier value, restamped to the start time and marked with Quality 133. A point stored exactly on the start time comes back with normal quality. Load the restamped rows into a warehouse and you get fake samples at batch boundaries. vValue carries string and mixed-type values, because Value is always NULL for string tags. Keep all three quality columns: OPCQuality is what the data source reported, QualityDetail is Historian's internal representation, and Quality is the basic indicator.

Historian also accepts late data. Sources that use IDAS or the Historian Client Access Layer store and forward when the network drops, so a window you already loaded can gain rows hours later. We re-read a trailing window on each run and upsert on tag name and timestamp, and we pass UTC bounds with wwTimeZone = 'UTC' so the autumn daylight saving change cannot create two identical local hours. Check the timezone behavior on one known tag before you trust it across thousands.

Hourly rollup from AnalogSummaryHistory

SELECT TagName, StartDateTime, EndDateTime,
       Average, Minimum, Maximum, ValueCount
FROM AnalogSummaryHistory
WHERE TagName = 'ReactTemp'
  AND StartDateTime >= '2026-10-01 00:00'
  AND EndDateTime   <= '2026-10-08 00:00'
  AND wwResolution = 3600000;   -- 1 hour, which is also the default

AnalogSummaryHistory returns several statistics per cycle in one row, and its Average is time weighted, which a GROUP BY over raw rows is not. On current Historian releases, wwRetrievalMode = 'Daily' on this view follows local calendar days and stretches the cycle to 25 hours on the day daylight saving ends, as shown in AVEVA's hourly and daily report scenario.

WideHistory with variables, through dynamic SQL

DECLARE @start datetime = '2026-10-24 06:00:00.000';
DECLARE @end   datetime = '2026-10-24 07:00:00.000';
DECLARE @sql   nvarchar(max) = N'SELECT * FROM OPENQUERY(INSQL, ''
  SELECT DateTime, ReactLevel, ReactTemp
  FROM WideHistory
  WHERE DateTime >= "' + CONVERT(varchar(23), @start, 121) + '"
    AND DateTime <= "' + CONVERT(varchar(23), @end, 121) + '"
    AND wwRetrievalMode = "Cyclic"
    AND wwResolution = 60000
'')';
EXEC sp_executesql @sql;

OPENQUERY will not take variables, so AVEVA's own workaround is to build the statement as a string and execute it. Inside the passed text, literals use double quotes. Style 121 produces the yyyy-mm-dd hh:mm:ss.fff format the provider expects. Keep an eye on two limits: the passed statement must stay under 8,000 characters, and wide tables top out at 1,024 columns. If you need 3,000 tags in wide format, pull them narrow from History and pivot downstream.

Does Wonderware Historian have a REST API?

Yes. AVEVA Historian Client Web serves the Historian Data REST API, an OData interface, on port 32569 for HTTP and 32573 for HTTPS by default. Version 2 is the current one. Resources include Tags, TagGroups, ProcessValues, AnalogSummary, StateSummary and Events.

The on-premises retrieval page gives the URL pattern https://<server>:32573/Historian/v2/<resource>?<query>. A raw-value pull for one tag looks like this:

GET https://historian01:32573/Historian/v2/ProcessValues
    ?$filter=FQN eq 'Baytown.tank_level'
       and DateTime ge 2026-10-24T00:00:00Z
       and DateTime le 2026-10-25T00:00:00Z
    &RetrievalMode=Delta

In version 2, DateTime values take no quotes, except on Events, according to the supported versions page. $filter on FQN matches exactly, and partial matches need the TagFilter parameter. Filtering on process values is case sensitive. Results come back in pages of up to 5,000 records, each with a link to the next page, so your client has to follow those links. The same API, hosted, is what AVEVA Insight exposes at online.wonderware.com/apis/historian/v2; the Insight retrieval page lists the RetrievalMode values it accepts.

Use the REST API when the consumer cannot reach SQL Server: Power BI in the cloud, a web app in a DMZ, or a team that will not get a SQL login on a control network. For bulk backfills SQL is simpler, since it avoids paging and gives you every retrieval parameter.

How do you get data out of AVEVA PI System?

Use PI Web API for most new integrations. It is AVEVA's REST interface to PI points and PI AF attributes. For .NET jobs on Windows, AF SDK reads PI directly without an HTTP layer, and the PI SQL drivers suit tools that only speak ODBC, OLE DB or JDBC.

The PI Web API reference works on WebIDs. You look up a point once by path, then read its stream:

# 1. Resolve a PI point to its WebId
GET https://pisrv01/piwebapi/points?path=\\PISRV01\ReactTemp&selectedFields=WebId

# 2. Pull recorded (compressed) values for a day
GET https://pisrv01/piwebapi/streams/{webId}/recorded
    ?startTime=2026-10-24T00:00:00Z
    &endTime=2026-10-25T00:00:00Z
    &maxCount=150000

# 3. Many points at once
GET https://pisrv01/piwebapi/streamsets/recorded?webId={id1}&webId={id2}&startTime=*-1d

Watch the defaults. GetRecorded returns at most 1,000 values unless you raise maxCount, and with no times it reads only the last day (*-1d to *). The server setting MaxReturnedItemsPerCall caps any single response at 150,000 items by default, so long ranges need to be chunked. The StreamSet controller reads many points per request, and the Batch controller bundles several logical requests into one HTTP call. Use interpolated with an interval when you want evenly spaced samples; the default interval is one hour.

The other PI surfaces, briefly:

• AF SDK is AVEVA's .NET library for PI and AF. PIPoint.RecordedValues takes a time range, a boundary type, an optional filter expression and a max count, and returns compressed values. Bulk calls on PIPointList handle many points.
• PI DataLink is the PI add-in for Microsoft Excel. It is fine for one-off pulls and a poor base for a scheduled pipeline.
• PI SQL Client (ODBC, OLE DB and JDBC drivers) sends SQL to PI SQL Data Access Server (RTQP Engine), which runs on the PI AF server. The older PI OLEDB Enterprise gives read-only SQL over AF metadata and AF-referenced point data.
• PI Integrator for Business Analytics is a separately licensed product. Its Standard edition publishes PI System data to external data warehouses such as Microsoft SQL Server, and the Advanced edition adds streaming targets.

Whatever you use, the archive has its own ceiling. A single query that touches more recorded values than the ArcMaxCollect tuning parameter allows fails with error -11091, even if the filtered result is small. Backfills should walk the timeline in slices sized to your densest tags. A tag stored every second produces 86,400 values a day, about 2.6 million in a 30-day month, so a month-wide request for one such tag can already be too large.

Which extraction method should you pick?

Use SQL for bulk Historian work and PI Web API or AF SDK for bulk PI work. Use the REST interfaces when the consumer sits outside the control network or cannot get database credentials.

For context on how this data gets into the historian in the first place, see our comparison of OPC UA and MQTT on the factory floor. If the historian feeds MES or ERP, the MES integration guide for legacy plants covers the next hop.

Is AVEVA licensing a reason to look for OSIsoft PI alternatives?

Sometimes, but usually not on its own. AVEVA sells on subscription through AVEVA Flex. Its October 2022 announcement describes Flex as a program that "spans any mix of cloud, hybrid, and on-premises solutions," and offered PI System customers a trade-in credit for moving from perpetual licenses to subscription.

AVEVA does not publish Flex prices, so any number you read online is somebody's quote. Get yours in writing, with the tag count and the client products priced separately. Then compare it with the cost of running a replacement for five years, including the people who maintain it. Count the reports, displays, calculations and habits built on top of the historian as well, because replacing them is part of the bill.

Read your license agreement before you build an extraction pipeline, too, and check whether it limits client connections or access by third-party software. This is not legal advice; review your license agreement and MSA with counsel.

When should you replace the historian with TimescaleDB or InfluxDB?

Replace it when the historian only serves analytics and nobody on the plant floor depends on it. Keep it, and sync data out, when operators trend from it, when System Platform or InTouch writes to it, or when its store-and-forward is protecting data from flaky networks.

We see the second case more often. A historian at the control layer buffers data through network outages, keeps OPC quality with every value and feeds operator trends, while the business wants the same data in a warehouse or a custom app. A one-way sync gives the business that data without touching the control system. Two open time-series databases are worth evaluating as targets:

• TimescaleDB is PostgreSQL with hypertables. Hypertables are in the Apache 2 edition. Continuous aggregates and the Hypercore columnstore, which you will want for years of tag data, are in the Community edition under the Tiger Data License.
• InfluxDB 3 Core went GA on April 15, 2025 under MIT and Apache 2. InfluxData describes it as a single-node, recent-data engine; high availability and read replicas are in the paid Enterprise edition. Core also limits query time ranges to about 72 hours, which makes it a poor fit for querying years of historian data.

Full replacement means rebuilding what the historian already does. That list includes edge buffering during network loss, OPC quality codes carried with every value, time-weighted averages and interpolation that match the old reports, and trend clients for operators. Price those before you count the license savings, or you may end up running a new database next to a historian you still pay for.

A middle path works well: keep the historian for the control layer, stream new data to TimescaleDB or a warehouse, and retire historian clients one report at a time. For a sense of what an incremental sync can do on a different kind of legacy source, our SIS data ingestion pipeline handles up to 1.2 million records a day at peak from five SIS providers. Latency fell from 4 to 6 hours to under 3 minutes for webhook-enabled providers and 12 to 15 minutes for scheduled syncs.

What to inventory before you start

Before writing any pipeline code, write down the following for each historian:

1. The product and version (AVEVA Historian release, PI Data Archive and PI AF versions) and who holds admin rights.
2. Whether the INSQL linked server is healthy, whether Historian Client Web is installed, and whether PI Web API is installed and on which host.
3. Tag counts, scan rates and the densest 20 tags, since those size your chunking.
4. Every report or app that reads the historian today, and which retrieval mode each depends on.
5. Your license terms for client connections and API access, and the renewal date.

The same list gives you the line items for the keep-or-replace comparison. Our industrial IoT development team builds these syncs for manufacturers running AVEVA, PI and older systems side by side. If you want a second opinion on your inventory, send it over.