Skip navigation
All Places > PI Developers Club > Blog > Author: tramachandran

PI Developers Club

6 Posts authored by: tramachandran Employee

Overview

Most of us have have searched for PI Points, but as our PI System grows larger or as more products like PI Connectors and Relays automatically create PI tags it becomes imperative to understand how to narrow down and optimize search queries. You might have used the Tag Search Dialog or simply copy pasted sample queries provided in the examples and modified them to suit your needs. Most of the times these queries are intuitive to read and understand but there are situations where we may need to utilize their full expressive power.

In this blog post we will explore PIPoint Query Search Syntax in PI AF SDK. We will have a deeper look into the Syntax Rules and Parsing of queries, along with Wild Cards, Operators and Aliases used in constructing a PI Point Query String to find the desired PIPoint objects. PIPoint Search Utility is used as an aid to accompany the examples shown in the blog post to demonstrate the syntactic and search aspects of query strings.

 

Let us look at some typical examples one might come across while performing tag searches and their query strings.

 

Below are some Invalid queries. We need to be aware of the reasons that make them invalid and avoid such mistakes in the future.

 

Query Syntax

A query is one or more AND condition filters that are ORed together. Each AND condition contains one or more query items. A query item consists of a query filter name, an operator, and the query filter. This allows multiple conditions to be specified with a single query. Query syntax described in Extended Backus-Naur Form (EBNF)

 

It is important grasp the ENBF syntax rules to construct correct and effective queries. As we go along this blog we will take a look at examples on how to do this and how to avoid potential pit falls one may encounter with query strings. There are a large number of possible constructs filled with many nuances, however if we gain an understanding of some standard rules this the task becomes a lot easier.

As an example, the below query strings(non-exhaustive list) represent the exact same query even though they vary syntactically

  • sin* AND PointType:Float
  • (tag:=sin* AND PointType:=Float16) OR (tag:=sin* AND PointType:=Float32) OR (tag:=sin* AND PointType:=Float64)
  • (sin* PointType:='Float16') OR (sin* PointType:='Float32') OR (sin* PointType:='Float64')
  • tag:=sin* AND PointType:Float
  • ("sin*" PointType:='Float16') OR ("sin*" PointType:='Float32') OR ("sin*" PointType:='Float64')

 

How can we parse a Query String?

Parsing can be viewed as decomposing a query string into separate conditions. Think of this as an 'exploded view' of the string where you can see how the individual components fit together. PIPointQuery is a structure in which PIPoint attribute specified by the AttributeName in the query is compared to the query's AttributeValue using the search Operator. PIPointQuery.ParseQuery Method parses the query string into PIPointQuery lists which can be used in used by the FindPIPoints(PIServer, IList<IEnumerable<PIPointQuery>>, IEnumerable<String>) method and also to verify the equivalence of search strings.

 

The example strings provided above would be transformed into the equivalent PIPointQuery list.

 

Note: Parsing the query string to PIPoint Query Lists in the examples are shown in order to help understand various aspects involved query string parsing. In most cases this is not necessary if one gains a good understanding of query syntax.

I highly recommended always using Query Strings which are more compact and can be used in code and as well as Tag Search dialogues for PI Point searches.

 

CAUTION: Parsed Query does NOT mean Valid Query (Syntax vs Semantics)

If a query string is parsed successfully it only indicates correct syntax. However, Syntactic correctness does not guarantee Semantic validity. In this trivial example, it is easy to see that Float1234 is not a valid point type, however it can still be parsed into a PointQuery structure as it conforms to the grammar rules.

 

The search performed using the query string will obviously fail as shown.

 

Use of Wild Card Characters

  • The string value of a filter can be enclosed in single quotes ('), double quotes ("), or without quotes. Quotations are required if non-escaped white space or quotation marks are desired within the filter string.
  • Single backslash (\) character is treated as a literal character unless followed by a wildcard character
  • Supported wild card characters are "*" to match any zero or more characters and "?" to match a single character. These characters cannot be escaped using the backslash ("\") character

 

Ex: Search tag names with pattern CD?1?8

 

Ex: Search all tags which have datasecurity of PI World (read or write, but not both) and which do not belong to point class with name starting with ba*

 

Alias Attribute Names

The following table lists the supported aliases for common PIPoint attribute names.These aliases can be used instead of the actual attribute name. The PICommonPointAttributes class contains the names of the common PIPoint attribute names.

 

Ex: Query strings 1 & 2 use aliases producing the same results

 

Notice the equivalent parsing for aliases.

Personal preference: I avoid using the aliases. One less thing to remember or make mistakes with.

 

Operators

  • EqualOperator can be specified either by ":" | ":="
    • Personal preference: I use := to be consistent with the use of other operators
  • PIPointValueFilter "Value" query if the PIPoint being queried is String type: LessThan, LessThanOrEqual, GreaterThan, GreaterThanOrEqual are not supported
  • PIPointValueFilter query with BooleanValue (i.e "Substituted", "Questionable", "Annotated", "IsGood"), only Equal and NotEqual are supported
  • The In operator is not supported. It will be implicitly translated as a filter value
    • Name:"IN("abc", "def")" this is implicitly translated to 'Tag:="IN("abc", "def")*"'

 

Syntax Rules: Cheat Sheet

  • AndOperator can be specified either by "AND" or <WHITESPACE>
    • Ex: AND is implied between pointtype and pointsource just by a space

  • EqualOperator can be either ":" or ":="

  • If a specific filter name is not specified, then the filter will default to the "Tag" filter and the operator will be "="

  • When a filter name is specified, no whitespace is allowed between the filter name, the ":" separator, and the optional operator.
    • If the operator is not specified, the default operator is "=".
  • If the type of a point attribute is DateTime, then the "TimeValue" format is supported for the filter value. This can be any recognized AFTimeString
  • Boolean can be specified by "True" or "False" or "1" or "0"
  • PointType:Float query is implicitly translated to 'PointType:=Float16 OR PointType:=Float32 OR PointType:=Float64'
  • PointType:Int query is implicitly translated to 'PointType:=Int16 OR PointType:=Int32
  • Starting in AF 2017, it also supports querying based on PIPoint Value. OR condition is not supported if querying based on PIPoint value.
  • Queries with OR condition are not supported for PIPointValueFilters.

  • A filter name may only be referenced one time per AND condition of the query string.
    • This example would cause an error: PointId:>5 AND PointId:<10
  • It is possible to construct queries which include multiple attributes and query conditions

  • Certain PIPoint Attributes are specific to a PIPointClass (Eg. AutoAck is applicable to ALARM & SQC_ALARM)
    • See this attachment (ptclassattr.txt) for each PointClass attributes and their typical values
  • Future point attribute, which is invalid, to a PI Data Archive version < 3.4.395
  • Security point attributes (e.g. "PtSecurity" and "DataSecurity"), are invalid for PI Data Archive version < 3.4.380
  • Query strings are Case Insensitive
  • On improving readability
    • Don't use quotes unless you need them, single quotes better than double
    • Don't use parenthesis unneccessarily

 

 

Additional searches options

SearchNameAndDescriptor

If True and the Tag attribute name is specified and the Descriptor attribute name is not specified in the query, then both of these attributes will be searched using the Tag query value

 

AFSearchTextOption

Indicates the text search option to be applied to the search pattern.

1. StartsWith 2. Contains 3. ExactMatch 4. EndsWith

 

Tag Search Dialog in AF Explorer

A good way to perform these same searches and check your queries used in your application is through Tag Search dialog in AF Explorer. You can open this by being in Elements View -> Search -> Tag Search.

 

 

Additional Search criteria can be specified through the UI. However all the attributes are not available through this. They can only be supplied when using the search string.

 

Bonus: Peek into PI Server

AF SDK makes a remote procedure call to the PI Server (PI Base Subsystem) which takes in the search parameters and returns the requested PI Points along with additionally specified attributes.

As a bonus you can run piartool -thread pibasess -history in your PI Server command line to track the RPC and see the number of points returned and the amount of time it took for it to run.

Example RPC output: 4452, 0, 14-Aug-18 13:37:01.63263, 1, piptsdk|1|GetPoints, 544, Return Count: 55. Returned Status: [0] Success

tramachandran

PIPoint Search Utility

Posted by tramachandran Employee Sep 5, 2018

Overview

This console utility was developed to demonstrate PIPoint Search Syntax in AF SDK and as a aid to accompany the examples shown in the blog post  PIPoint Search Query in AF SDK

As a stand alone tool, it provides a quick way to perform searches and verify syntactic correctness of query strings.

 

Usage

 

0. Connect to PI Data Archive

This is required for both searching for PI Points and Parsing Query Strings as certain attributes depend on the version of the server.

 

1. Search PI Points using a Query String

Output columns: Tag name, Point ID, PointType, PointClass

 

2. Parse Query Strings into individual PI Point Queries

 

3. Specify SearchNameAndDescriptor

If true and the Tag attribute name is specified and the Descriptor attribute name is not specified in the query, then both of these attributes will be searched using the Tag query value. Default = false

 

4. Specify AFSearchTextOption

Indicates the text search option to be applied to the search pattern. Default = StartsWith

 

Source Code and Download

GitHub: GitHub - ThyagOSI/PIPointSearchSyntax

In this blog we will have a look at the new features included in the OSIsoft.AF.PI Namespace, namely

  • Methods added to find PIPoints in bulk from a list of IDs.
  • A change event that can be checked if it was the result of a rename

 

Finding PI Points using IDs

The PIPoint. FindPIPoints(PIServer, IEnumerable< Int32> , IEnumerable< String> ) and PIPoint. FindPIPointsAsync(PIServer, IEnumerable< Int32> , IEnumerable< String> , CancellationToken) methods were added to find PIPoints in bulk from a list if IDs.This helps to retrieve a list of PIPoint objects from the specified point ids without the requirement of knowing the PI Point names apriori.

 

Snippets:

var piDataArchive = new PIServers()[serverName];
var pointIDs = new List<int>() { 1, 3, 4, 6, 10000 };
var pointAttributes = new List<string>() { "Name", "PointType", "PointID" };
IList<PIPoint> myPoints = FindMyPIPoints(piDataArchive, pointIDs, pointAttributes);

 

private static IList<PIPoint> FindMyPIPoints(PIServer piDataArchive, IList<int> pointIDs,  IList<string> pointAttributes)
{
            return PIPoint.FindPIPoints(piServer: piDataArchive,
                                                  ids: pointIDs,
                                                  attributeNames: pointAttributes);
}

 

Asynchronous method (This call might use a background task to complete some of its work. See the Threading Overview for some matters to consider when execution transitions to another thread)

var tokenSource = new CancellationTokenSource();
var token = tokenSource.Token;
var findpointstask = FindMyPointsAsync(piDataArchive, pointIDs, pointAttributes, token);

 

private static async Task<IList<PIPoint>> FindMyPointsAsync(PIServer piDataArchive, IList<int> pointIDs,  IList<string> pointAttributes, CancellationToken token)
{
            return await PIPoint.FindPIPointsAsync(piServer: piDataArchive,
                                                      ids: pointIDs,
                                                      attributeNames: pointAttributes,
                                                      cancellationToken: token);
}

 

NOTE:

  • The PIPoint attribute names to be loaded from the server as the PIPoint objects are found. The GetAttribute(String) method can be used to access the loaded attribute values. If null, then no attribute values are loaded for the returned PIPoints.
  • Asynchronous methods throw AggregateException on failure which will contain one or inner more exceptions containing the failure
  • A cancellation token used to abort processing before completion. Passing the default CancellationToken.None will run to completion or until the PIConnectionInfo.OperationTimeOut period elapses.

 

Check if PI Point has been renamed

PIPointChangeInfo.IsRenameEvent Method is used to check if PIPointChangeInfo instance is a rename event. Previously this information was not possible to obtain through AF SDK and required further digging into the PI Data Archive.

 

Method Signature

public bool IsRenameEvent(
               out string oldName,
               out string newName
)

 

Implementation through PIServer.FindChangedPIPoints Method

IList<PIPointChangeInfo> changes = piDataArchive.FindChangedPIPoints(largeCount, cookie, out cookie, null);
if (!(changes is null))
{
     foreach (PIPointChangeInfo change in changes)
     {
          if (change.IsRenameEvent(out string oldname, out string newname))
           {
                Console.WriteLine($"\tRenamed New name: {newname} Old name: {oldname} ");
           }
    }
}

 

NOTE:

  • PIServer.FindChangedPIPoints method involves various aspects like cookies, filterpoints and  PIPointChangeInfo structure, which the reader is encouraged to explore in detail through the AF SDK documentation
  • Cookie: Use the return from a previous call to this method to find all changes since the last call. Pass null to begin monitoring for changes to PI Points on the PIServer. Pass an AFTime to find all changes, since a particular time
  • filterPoints (Optional): A list of PIPoint objects for which the resulting changes should be filtered

 

The code that demonstrates the use of the the above methods in the form of a simple console application can be obtained at: GitHub - ThyagOSI/WhatsNewAF2018

A sample output from the console application

 

Conclusion

Hope this post helps you, the PI developer to explore these methods further and implement them in your future projects.

Please feel free to provide any feedback that you may have on the new methods, its documentation or this blog post.

"IoT and Fog Computing: Develop Data Ingress Applications from Edge to Cloud" hands-on lab at PI World SF 2018:

 

Just one year ago, at the 2017 OSIsoft User Conference in San Francisco, we had long discussions and debates around the present and future of operational intelligence: thousands of new sensors and devices, petabytes

of data generated every day, fragmented in an endless number of incompatible protocols and interfaces. Furthermore, new and old players want to provide their own, isolated service, leaving developers and end users with

the [almost] insurmountable problem of putting all this stuff together and making it run in a smooth and cost-effective fashion.

That was the time when the idea of an Open Edge Module, that then became FogLAMP , was conceived.

 

One year later, at PIWorld 2018 in San Francisco, we are able to demonstrate the result of twelve months of hard coding on this concept. We have worked on a thriving open source project to bring any data coming

from new and old devices, sensors, actuators etc. into PI. This project is called FogLAMP (available on GitHub): free to learn, use, and adopt as every open source project is.

 

These are really exciting times, and for a good reason. PIWorld is a milestone for FogLAMP and a game changer for the whole Community involved in the development and implementation of IoT and IIoT projects.

For the first time, industrial-grade solutions can combine decades of investments in existing infrastructure with new and innovative technologies.

 

We will talk about this and more at the FogLAMP Community booth, where you will be able to meet developers and contributors from OSIsoft, Dianomic Systems, software providers and hardware manufacturers.

 

We also have two talks, one on the features of FogLAMP (with a short demo) and one on Fog Computing architectures.

 

North/South and East/West components interaction in a Fog Computing Architecture.

 

Certainly, the most important appointment for developers, the one that must be added to your agenda, is the IoT Lab titled “IoT and Fog Computing: Develop Data Ingress Applications from Edge to Cloud”.

The IoT and Fog Computing Lab

If you are a developer and you are interested in learning more about collecting data from sensors and devices and sending data to PI and Cloud systems, the IoT and Fog Computing Lab is for you.

 

The lab is packed with exercises that will help you understand and learn about problems and solutions associated to data collection, transformation, buffering and transfer. Put your hands on code and hardware that can

simulate typical scenarios in manufacturing, transportation, and in other industrial and infrastructure sectors.

 

But there is more! Participants will have access to a Raspberry PI Zero W and a set of sensors packed into a board called Enviro PHAT. We will place the boards and a battery pack on a remote controlled truck

to simulate an installation on moving devices, where you may experience intermittent connectivity with other layers of the data infrastructure.

 

Our Lab environment.

 

Last but not least, we will have a bit of fun with a game we have organized for you. We will run our RC trucks with the Raspberry PIs mounted on it, on a track where you can score points and race for the highest number of collected data in the room!

It will be fun to learn and play!

 

Don’t forget to register and bring your laptop. All you need to install is:

- A SSH client, such as PuTTY (Windows) or Terminal (MacOS)

- An API client, such as Postman (for Windows and MacOS)

- A Microsoft Remote Desktop client

 

The rest is on us for you to try, use and take home to learn even more!

 

Talks and Lab Schedule - Thursday, April 26, 2018:

- 10.30am - 11.15am: Introduction to the Open Edge Module (FogLAMP)

- 1.30pm - 4.30pm: IoT and Fog Computing: Develop Data Ingress Applications from Edge to Cloud

- 2.30pm - 3.15pm: Fog Computing and OSIsoft

 

Overview

We often come across scenarios in which we would like to extract large data sets into files (.txt, .csv etc) from the PI Data Archive but run into parameters which are put in place to protect the PI Server from trying to satisfy a huge query. The server can handle larger and larger queries with more resources but these safeguards prevent queries from straining the archive leading to performance issues. There are valid cases where these safeguards become limitations making it difficult in achieving our intended objectives.

 

There are three PI tuning parameters that can help (For a more detailed discussion refer to KB 3224OSI8)

(1) ArcMaxCollect to limit the quantity of data retrieved by a single client

(2) MaxThreadsPerClientQuery to restrict the number of repeated queries accepted from a single client

(3) Archive_MaxQueryExecutionSec to cancel long-running archive queries.

 

Working around the ArcMaxCollect contraint

The most common scenario of running into a limitation is with ArcMaxCollect which causes PI Clients to return "Failed to retrieve events from server. [-11091] Event collection exceeded the maximum allowed" after large or filtered queries. The problem is that you are asking the PI Data Archive for too large an amount of information at once.

 

In order to get around we need to break down the underlying query into smaller queries. To preserve data integrity we need to make sure no values are excluded and more values are not included from the boundaries while collating the chunks. Rick Davin has addressed this issue in his blog post GetLargeRecordedValues - working around ArcMaxCollect and the users interested in the code implementation are highly encouraged to check it out. This is incorporated into the PIEventsNovo utility's downloadcsv option to get the values from PI Points into csv files.

 

Usage of the Utility

pieventsnovo.exe -downloadcsv <tagmasks> <starttime,endtime>[,pageSize] [-server Archive]

 

Example:

 

The pageSize here refers to the "chunking" of the data retrieval from the server. The Defaults, Min and Max can be set in App.config. When it is not specified in the command line the Default is used.

The output folder is also specified in the App.config. If it is left as an empty string the the applications current directory is used.

 

For technical documentation, other available features & to download use the following PI DataPipe Events Subscription and Data Access Utility using AF SDK - PIEventsNovo

 

A note of caution

Retrieving larger number of values may potentially result in a higher load/resource usage on the server. Make sure adequate resources are available in order to protect the server and the network (and indirectly, client applications) from enormous amounts of data being retrieved.

Opening .csv files using MS Excel may run into specifications and limits set by Microsoft. For 2016 version this is 1,048,576 rows. Alternatively use editors like NotePad, Emacs, gedit, Notepad++ etc.

Overview

The ability to subscribe to PI point updates through data pipes is a convenient feature which helps in both understanding and troubleshooting data flow.

PIEventsNovo is a console application that can be used to sign up for data pipe (snapshot/archive/timeseries) events and additionally provides useful data access features.

 

Scenarios where this tool might be useful

- Set up data pipe subscription for pi events (including timeseries and future data)

- Fetching current (end-of-stream) values

- Fetching archive values over a particular time range

- Generating interpolated and plot values based on sample duration and pixels in trend

- Summary calculations (Event and Time weighted) over a time range

- Update/Annotate pi events specifying write mode (replace,insert, no compression etc) and buffer option

- Upload values to a tag from a csv file

- Downloading large number of events from the archive into files

- Delete recorded values over a time range

 

Note: most output formats are comma separated making it convenient to redirect the console output to csv/txt files for further investigation.

 

 

Sample outputs

 

Sign up for snapshot and archive events

 

TimeSeries data

 

Current Values

 

Archive Values

 

Summary (Event Weighted)

 

Update (with no compression and buffer event)

 

Upload data to a pi point from csv file (with mode and buffer options)

 

 

Source Code and Technical Documentation

GitHub - ThyagOSI/pieventsnovo: Application to mimic the some of the functionalities of pievents.exe. Uses AF SDK to han…

 

Change log (refer to GitHub repo for a complete list of modifications)

Version 1.5.6732

  • pointchanges include renames as output for AF 2.10+
  • App.config defaults additionally built into source code
  • switches -v/--version and -p/--params to expose version info and parameters respectively

 

Minimum Requirements

.NET Framework 4.6.1

OSIsoft.AFSDK 4.0.0.0 Version 2.8.5.7759

 

Trivia

During your interactions with our technical support team you might have come across a certain 'pievents' application (inspiration for the current name and functionalities) which performs snapshot and archive data pipe subscription. However, this is not distributed or available for download and it lacks support for relatively modern features like TimeSeries and Future Data.

 

Feedback

If you have comments, find issues with the tool, or would incorporate a new feature in this utility, please post them below.

 

Download Version: 1.5.6732

Filter Blog

By date: By tag: