Search AFDatabases for AFAttributes using the PI Point data reference. Output may be written to an Excel workbook or a tab-separated-values (tsv) text file. If you expect less than 1 million rows, then Excel may be a suitable choice. If you expect more than 1 million rows, then you should output to Text file.
This uses .NET Framework 4.8 and AF SDK. You do not need Excel installed on the client PC running this app in order to create the workbook. However, you will need Excel on whichever PC is used to view or edit the final workbook.
If building the app with your own copy of Visual Studio, be sure to disable the Prefer 32-bit apps option for Build.
Copyright 2024 Rick Davin
This repo uses the Apache 2.0 license.
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
Maybe.
Have I tested my app in different scenarios? YES. Have I rigorously tested my app in different scenarios and confirguration options? NO.
As I have not thoroughly tested application for too many configurations so I am inclined to take the safe way out and just issue a caution that there may be bugs. Nothing destructive is done to your AFDatabases or PIPoints. Worst that may happen is your Excel spreadsheet is garbage. I hope this caution does not persuade you to not try the app.
I only explicitly loaded 3 NuGet packages and discovered over 60 were installed! This is a quirk with .NET Framework. When AF SDK is released for .NET 8, a new version of this app may be released and the NuGet packages may be less intimidating.
The packages that I explicitly loaded:
- ClosedXML, for all things Excel
- System.Text.Json, for reading the appsettings.json file
- Costura.Fody, an assembly weaver
One big warning is that ClosedXML uses SixLabors.Fonts, which requires an older version to run in .NET Framework. Therefore, do not upgrade SixLabors.Fonts.
For .NET Framework, you may also need to explicitly load DocumentFormat.OpenXml. ClosedXML is a more user-friendly wrapper that sits on top of OpenXml.
This is an assembly weaver which merges all referenced DLL's into a single EXE. That said, when building the app you may still see 2 dozen or more files in your build directory. To actually run this application, you only need to keep:
- appsettings.json (you will need to edit this for your environment)
- Search_AFDatabases_For_PIPoints.exe
- Search_AFDatabases_For_PIPoints.pdf
- Search_AFDatabases_For_PIPoints.exe.config
Side note: it is a common practice not to include the OSIsoft.AFSDK assemblies in the local build, so those are will not be bundled into the final exe. It is required that you have properly installed the AFClient AF SDK to your PC.
This is not a standard AF SDK concept. Some companies employ naming patterns for PIPoints, particularly on an enterprise server supporting multiple sites or multiple customers. Such a company may employ putting a prefix for each tag followed by some delimiter. For example, "Site 1/tagname" or maybe "Site2-tagname". Over a dozen years, such naming standards may change and you may have multiple standards historically, which calls for multiple separators. Using the previous examples, the returned groupings would be "Site 1" and "Site2", which can be immensely helpful when filtering on Excel columns.
The logic is to find the earliest separator in the tag name and consider it to be the Tag Group name.
If you do not wish to use such separators, then the appsettings.json file can have a null or empty value for "TagGroupingSeparators", which will suppress it from being an output column.
I trust you will not be like me and run in Debug mode across a slow VPN. If you run in Release mode as close to the hosting PISystem and PIServer, I think you will be pleased with the performance. Remember to disable "Prefer 32-bit" in Build options.
Running in Debug across a slow VPN for 17 AFDatabases and 19K AFAttributes, it took 3 minutes and 40 seconds. I moved the 4 key required files from Release mode to a VM sitting on same network as the PIServer. This took 23 seconds. I freely admit, however, that my AFDatabases are relatively simple. There is no more than 50 elements, 200 - 3000 PIPoints, and ZERO analyses, 0 child elements to a data element, etc.. All of which are reasons to have more testers with more diversity of databases. Keep in mind that the AFSearch requires a Full Load and the "heaviness" or bloat for a particular search really depends upon the specific AFDatabase.
Despite using .NET Framework, I do not put settings in the App.Config file. I originally did but I do desperately hope this will one day be converted to AF SDK .NET 8+. With that in mind, I wanted to have a consistent interaction for the user running the app, so I implemented a rudimentary appsettings.json input file.
I am hoping the app can achieve these minor goals:
- Be functional, i.e. the app needs to indeed find PIPoints inside AFDatabases
- Be performant, i.e. employ paging, bulk calls, or async where possible
- Be flexible, i.e. someone can read through the code and feel capable of making some custom changes
- and finally, Be educational. I hope someone can see techniques and learn from them or at least learn what not to do