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.
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.
- 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
- For composing time strings in AF SDK refer AFTime Constructor - Determining UTC versus Local time
- 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
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
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:  Success