Skip navigation
All Places > PI Developers Club > Blog > 2012 > August

More AFTime extensions

Posted by rdavin Employee Aug 31, 2012

As my Friday is winding down before a 3-day weekend, I'm in limbo.  Too early to head out to happy hour but too late to start with a new project.  Sounds like a good time to do a quick blog.  I've been working with AF 2.5 and discovered that 3 of my 12 custom data references will need to override some RDA methods.  In my code I frequently needed to widen a time range by 1 second on each end.  This isn't hard to do.  It would look something like:

AFTimeRange tr = new AFTimeRange(new AFTime(timeContext.StartTime.UtcSeconds - 1.0), new AFTime(timeContext.EndTime.UtcSeconds + 1.0));



If one carefully reads across the line, one should be able to see what is going on there.  One nit picky issue I have it that its an eyeful.  One must carefully read across it.  It would be nicer to comprehend what is going on without paying so much attention to it.  Compare that to:

AFTimeRange tr = new AFTimeRange(timeContext.StartTime.AddSeconds(-1.0), timeContext.EndTime.AddSeconds(1.0));



To me, its just a little easier read.  While there isn't actually an .AddSeconds method available to the AFTime object, there is nothing stopping me from creating my own extension method.  Be sure to put any extension methods in a static class.  Note to VB users: a static class in C# is the equivalent of a Module in VB.

static public AFTime AddSeconds(this AFTime time, double seconds)
    return new AFTime(time.UtcSeconds + seconds);



If I'm going to take time to create such a method, I want it to do just a little bit more than be a convenient method than make it easier on the eyes.  I probably should have it safely add seconds to a given time.  There are 2 general possibilities for an unsafe operation.  One would be if resulting sum of UtcSeconds and seconds would be outside the range of an AFTime object.  The other would be if seconds would ever be NaN, +/- infinity, or if the sum of seconds + UtcSeconds was greater than a double's MaxValue.  I choose to ignore the 2nd possibility within my code because it will never happen. As for the first case, I seriously doubt the output time would ever exceed AFTime.MaxValue but I will include it for completeness.  However there is a rare but likely enough situation where the input time could be AFTime.MinValue (i.e. 1/1/1970) and you do not want to subtract 1 second from it.  So one possible safe version would look like:

static public AFTime AddSeconds(this AFTime time, double seconds)
    double sum = time.UtcSeconds + seconds;
    if (sum <= AFTime.MinValue.UtcSeconds)
        // Rare but still very likely possibility that variable ‘time’ could be 1/1/1970. 
        return AFTime.MinValue;
    else if (sum >= AFTime.MaxValue.UtcSeconds)
        // Seriously doubt this would ever happen but included for completeness. 
        return AFTime.MaxValue;
        return new AFTime(sum);



I really do love extension methods.  Not long after creating the above, I needed an .AddHours method too!  Here's what it looked like:

static public AFTime AddHours(this AFTime time, double hours)
    return AddSeconds(time, hours * 60.0 * 60.0);



That about wraps this up.  Still got some time left in my work day.  Guess I will go sharpen pencils or something.

When joining the vCampus team 2 weeks, I was looking for something simple to refresh my programming skills. It turned out that porting the PIAsynchStatus Example (see pisdk.chm) from VB6 to VB.Net was challenging enough. I believe the result is a good example for asynchronous PI Data Access. So please allow me using it for my first blog post even there will be nothing new for most of you.




The original example is using a Timer to check for status changes from time to time each 0.5 seconds). The advantage is that this allows checking the progress but my idea was using asynchronous callback instead of the Timer. So I have removed the timer and changed the definition of the routine that is called by the Timer in the original VB 6 example to make it handled (triggered) by the OnStatusChange event fired by the asynchronous GetPoints() call:



    Private Sub asynchevents_OnStatusChange(ByVal NewStatus As CallStatusConstants)


    Private Sub asynchevents_OnStatusChange(ByVal NewStatus As CallStatusConstants) Handles asynchevents.OnStatusChange

Updating a ProgressBar with OnProgressChange() event gave me an issue. The first OnProgressChange() was with 1% and the second with 10%. Afterwards my little application was hanging even after disabling the handler for OnStatusChange(). If you’ve managed using OnProgressChange() succesfully, please let me know how. I decided removing the ProgressBar for now.


The next issue I ran into is the following error when attempting to update a control on Form1 from within asynchevents_OnStatusChange():




InvalidOperationException was unhandled by user code: Cross-thread operation is not valid …




The callback from the asynchronous function call is handled in an additional thread that does not have access to the controls that are handled with the main application thread.


The solution is delegating control updates to a separate sub and invoking methods if needed. The OnStatusChange() sub handles different statuses and delegates control updates to separate subs:

    Private Sub asynchevents_OnStatusChange(ByVal NewStatus As CallStatusConstants) Handles asynchevents.OnStatusChange
        Select Case asynchevents.Status
            Case Is = csInProgress
            Case Is = csComplete
            Case Is = csCancelComplete
            Case Else
        End Select
    End Sub

Let's have a look at ShowInProgress() and the conditional invoke. 

    Private Sub ShowInProgress()
        If Me.InvokeRequired Then
            Me.Invoke(New MethodInvoker(AddressOf ShowInProgress))
                lbStatus.Items.Add("New Status: ... in progress")
            Catch ex As Exception
                lbStatus.Items.Add("ShowProgress: " & ex.Message)
            End Try
        End If
    End Sub

The complete Visual Studio .NET 2010 project is attached to this post. I am excited about your replies espacially about your suggestions for improvements.


Last but not least I like to discuss the where clause that is passed with the GetPoints() call. This is a powerful way searching tags by its attributes. Please review the following examples.


1. Non-case sensitive search for all tags that have a specific character or a sequence of characters at first place of their name:



2. Search for tags that have a specific point source and a specific Location1 value. This query is very useful when one would retrieve all the tags serviced by a specific interface instance:

pointsource='OPC' AND Location1=1

3. Query for all tags having a specific pointtype assigned:


Imagine you are the OSIsoft technical support engineer answering an urgent call for help from a customer who has discovered malware on their PI Systems. Yikes!


This is not a drill; a few cases of this type have been reported recently. Given the rarity of such calls we don't have a set playbook. Formal incident response plans are something we are actively working on. So what was observed and how did we do in these cases?


Observation #1 - OSIsoft 24x7 technical support access was essential for timely response.


Perhaps it's just my skewed memory but emergencies seem to occur more frequently afterhours, weekends and holidays. Irrespective of coincidence or malicious intent, problems with mission critical systems demand a timely response.


In one of the recent cases, OSIsoft communication with the customer 'followed the sun' to prescribe advice and address concerns raised in follow up questions.


Observation #2 - Disaster recovery takes on some extra considerations.


Lingering concerns about system security are among the more insidious consequences of cyber breach. Extra security tasks after disaster recovery include making sure credentials that could have been exposed are changed.  In addition to Windows service accounts, attackers gaining full administrative access could potentially harvest credentials from the PI System. Of particular concern are credentials configured for external data sources (AF tables, data sets, and interfaces).  These credentials should be changed regardless of encryption technique. Auditing and security monitoring should be instrumented to alert on attempts to use the presumed compromised credentials.


Observation #3 - Reluctance to share indicators of compromise is common.


While it is good advice to avoid sharing unnecessary details of cyber breach this posture can partially hamstring subject matter experts (kind of like a patient not sharing all symptoms with the doctor).  My advice for critical infrastructure operators is to seek guidance from a professional incident response team. Certainly if there is any indicator specifically related to an OSIsoft application it is appropriate to facilitate community level awareness and response.




Corporate IT departments and anti-virus companies have carried the ball for years in responding to 'run of the mill' computer viruses. Today, this seemingly endless game of cat and mouse has expanded into the realm of industrial software applications and critical infrastructure operators.


Metaphorically like risk of fire... strategies to help prevent, detect and respond to cyber incidents make sense. Specifically for response we intend  to follow a well-developed incident response plan and avoid the proverbial chaos of 'shouting fire in a movie theater'. While cyber incidents affecting our customers continue to be quite rare, recent trends suggest OSIsoft technical support engineers will indeed be among first responders.  From these cases we've identified opportunities to improve especially along the lines of communication norms and disaster recovery procedures.

A blog post Andreas made recently reminded me of this job I did for one of our clients some months ago. They had just started using AF, and we created some calculations for them using the cool interpolation feature of an AF table. After everything was set up we demonstrated the setup, and showed how to trend the calculations using ProcessBook.


And then one of the engineers raised her hand and asked: How can I see the values in Excel, using DataLink?


Knowing that I couldn't store the data from the AF calculation to a PI tag, and that DataLink wasn't AF enabled yet, I had to come up with a solution quickly. Performance Equations didn't have the strength of being able to interpolate values the way we could in AF, and I didn't really want to create the entire thing (again) in ACE.


But how about simply using ACE to read the value of the AF attribute, and store the value directly to a PI tag? That sounded like a quick solution, and by making it module dependent I could easily create a utility which could store multiple AF attributes to PI tags. It was sufficient to have it clock scheduled, which reduces the time to implement. (Naturally scheduled is also possible, but then you need to gather up all the PI tags from the PI Point data references in AF, from which the calculation is based upon, and put these as input tags to the ACE calculation).


Step 1


Let's start by defining the structure we need in the Module Database, like in the picture below:

  • Output: PIAlias pointing to the PI tag used for storing the output value
  • Logging: PIProperty, boolean value to determine whether or not logging to the event log is enabled
  • AF_Calculated_Attribute_Path: PIProperty, string value containing the full path to the attribute you wish to store. Example: "\\PISystem\AFDatabase\Element\Element\Element|Attribute"  





















The PIModule under which these are created, will be the context to use when applying the calculation to new setups.


Step 2


Create a new ACE project, name it accordingly, select the output section and click the "Alias search" button to locate the "Output" tag alias we just created. The input section should remain empty (unless you want to attempt to create a naturally scheduled calculation, as mentioned).


Step 3


Make sure you have the necessary references added. Since we need to access AF attributes we'll need to have a reference to the AF SDK.




Step 4


Add the code. It's well documented, you can use it all or pick out the parts you need:

Imports OSIsoft.PI.ACE Imports OSIsoft.PI.ACE.PIACEBIFunctions Imports PITimeServer  Imports OSIsoft.AF Imports OSIsoft.AF.Asset  Imports System.Collections.Generic  Public Class AFAttributeToPITagStorage     Inherits PIACENetClassModule     Private OutputTag As PIACEPoint     Private Mdb_Module As PISDK.PIModule     Private Shared log As EventLog     Private Shared allPiSystems As PISystems     Private Shared myPiSystem As PISystem     Private Shared myAFDatabase As AFDatabase      'Temporary calculation variables     Dim piSystemName As String     Dim afDatabaseName As String     Dim myAttribute As AFAttribute     Dim afAttributePaths As IEnumerable(Of String)     Dim afErrors As IDictionary(Of String, String)       'Properties     Dim AF_Attribute_Path As String     Dim Logging As Boolean       '     '      Tag Name/VB Variable Name Correspondence Table     ' Tag Name                                VB Variable Name     ' ------------------------------------------------------------     ' OutputTag                               OutputTag     '     Public Overrides Sub ACECalculations()         'Get PI System name from input path         If (AF_Attribute_Path.StartsWith("\\")) Then             piSystemName = AF_Attribute_Path.Substring(2).Split("\")(0)         End If          'Get AF Database name from input path         If (AF_Attribute_Path.StartsWith("\\")) Then             afDatabaseName = AF_Attribute_Path.Substring(2).Split("\")(1)         End If          'Add the path to the element to an IEnumerable list         afAttributePaths = New List(Of String)(New String() {AF_Attribute_Path.Split("|")(0)})          'Make sure PI System name is properly configured in the input path         If (piSystemName Is "") Then             If Logging Then                 log.WriteEntry("Error: PI System name is not properly configured in the MDB Property: " + DateTime.Now.ToString())             End If              'IMPORTANT to avoid CalcFailed in the archive             OutputTag.SendDataToPI = False         Else                         myPiSystem = GetPISystem(piSystemName)              If (Not myPiSystem.ConnectionInfo.IsConnected) Then                 myPiSystem.Connect()             End If              myAFDatabase = myPiSystem.Databases(afDatabaseName)              If (IsDBNull(myAFDatabase)) Then                 If Logging Then                     log.WriteEntry("Error: No PI System or AF Database found: " + DateTime.Now.ToString())                 End If                 OutputTag.SendDataToPI = False             Else                 'Count the number of found elements. Should be only 1 since path is absolute!                 If (AFElement.FindElementsByPath(afAttributePaths, Nothing, afErrors).Count = 1) Then                     myAttribute = AFElement.FindElementsByPath(afAttributePaths, Nothing, afErrors).First.Attributes(AF_Attribute_Path.Split("|")(1))                      If (Not IsDBNull(myAttribute)) Then                         If Logging Then                             log.WriteEntry("Debug: Attribute " + AF_Attribute_Path.Split("|")(1) + " is " + myAttribute.GetValue().ToString() + " at: " + DateTime.Now.ToString())                         End If                          'Important: Cast to PIValue since AFValue gives "Wrong type" in the archive                         OutputTag.Value = myAttribute.GetValue().ToPIValue()                     Else                          If Logging Then                             log.WriteEntry("Error: Attribute " + AF_Attribute_Path.Split("|")(1) + " is null: " + DateTime.Now.ToString() + ". Error returned from AF: " + afErrors.First.ToString())                         End If                         OutputTag.SendDataToPI = False                     End If                 Else                     If Logging Then                         log.WriteEntry("Error: More than one attribute by the name of " + AF_Attribute_Path.Split("|")(1) + " found: " + DateTime.Now.ToString())                     End If                     OutputTag.SendDataToPI = False                 End If             End If         End If     End Sub      Protected Overrides Sub InitializePIACEPoints()         OutputTag = GetPIACEPoint("Output")         Mdb_Module = GetPIModuleFromPath(Context)          Init()     End Sub      Private Sub Init()         OutputTag.ArcMode = PISDK.DataMergeConstants.dmReplaceDuplicates          log = New EventLog()         log.Log = "Application"         log.Source = "PI-ACE"          If Logging Then             log.WriteEntry("Initializing calculation " + Mdb_Module.Name + ": " + DateTime.Now.ToString())         End If     End Sub      '     ' User-written module dependent initialization code     '     Protected Overrides Sub ModuleDependentInitialization()         AF_Attribute_Path = Mdb_Module.PIProperties("AF_Calculated_Attribute_Path").Value         Logging = Mdb_Module.PIProperties("Logging").Value     End Sub      '     ' User-written module dependent termination code     '     Protected Overrides Sub ModuleDependentTermination()         AF_Attribute_Path = String.Empty         Logging = False     End Sub      '     ' Return the PI System with the requested name, or the default one if no name is passed     '     Private Function GetPISystem(ByVal name) As PISystem         allPiSystems = New PISystems()          If (name Is "") Then             GetPISystem = allPiSystems.DefaultPISystem         Else             GetPISystem = allPiSystems(name)         End If          If Logging Then             log.WriteEntry("PISystem: " + GetPISystem.Name + ": " + DateTime.Now.ToString())         End If      End Function End Class



Step 5


Test and register the ACE module. If you want to add new calculations you can deploy the calculation to new contexts, which should be set up in the Module Database in the same way as in step 1.


ACE will now read the current value from the AF attribute at the given clock scheduled times, and save the value to the output tag.


A small caveat in the Init() method, where you specify the log.Source. If you're in a Windows 2008 Server environment and the source doesn't exist, it won't be created automatically as it used to with previous versions. You'll have to explicitly create it (using PowerShell, google it) or possibly use another source which already exists.




So, while we're awaiting the arrival of the Configured Analytics, this is something you can do to store the values from AF attributes to PI tags.

What did you learn this year about the PI System that everyone needs to know? 


vCampus Live! 2012 preparations are at full speed and it’s time to gather presentations from you, members of the vCampus community.  This is your chance to engage professionally with your peers alongside presentations from OSIsoft. 


In selecting presentations we’ll favor proposals that will send the audience home from this pure technical event with ideas and inspiration.  Case studies into novel projects are acceptable, but they should support the focus of your presentation: how the attendees can apply what they learned. 


Presenters will receive complimentary admission to vCampus Live! 2012.  Have an idea and don’t quite know if it fits?  Write up a short description and send it to us at  – we’ll give you feedback. We hope to see you there on November 28 in San Francisco!


On the similar topic about sharing your experience, we are going to have PI Technology Adoption Challenge this year. This is a continuation of last year's Partner Technology Adoption Challege. Like the Partner Technology Adoption Challenge, we want to you to share with us how PI System technology is adopted and used in your solution =)


Given the success of the Partner Technology Adoption Challenge, we are opening up 2 categories this year, for Partners and End-Users. So whether you are a partner, end user, or developer, you are more than welcome to share your experience with us and get a chance to win the challenge.


The registration website for OSIsoft vCampus Live! 2012 will be up very soon. In the meantime, stay tuned for more information about OSIsoft vCampus Live!

This is a two-part post. 


Part One, below, is an introduction into obtaining PI summary data (statistics etc.) within PI OLEDB Enterprise.
Part Two is where things will get interesting: a look at how to exploit the nuances of PI Time Syntax when creating reports containing summary data. In other words – how to get statistics for different types of time ranges, like month-to-date.



Raw data or statistics?

As many here know, PI OLEDB Enterprise (as of 2010/2012 versions) does not directly support statistical calls. If I have an AF attribute giving me raw production flow…




…then within PI OLEDB Enterprise I’m limited to getting various renditions of those archived values.










For production flow, though, what really matters to me is total production – e.g. the total for today, or the month-to-date total, or some such thing. Total is key; the flow at given points doesn’t matter for my use case. So how do we coax a total out of PI OLEDB Enterprise?



What not to do

One forbidden fruit is the included aggregate SQL functions: SUM, AVG, MAX, MIN, COUNT.






Don’t get me wrong – there are definitely uses for these functions – but more often than not, they don’t fit the use cases of PI OLEDB Enterprise. What if we tried taking the SUM() of today’s values for Production flow? First off, we’d get an event-based result – senseless here – though we could fudge things with some interpolation. Second, this would be wildly inefficient! Why bring back a mountain of data from the PI Data Archive, only to aggregate it locally – when the PI Data Archive can do aggregations itself?



Adding statistics to the AF model

Thus the question becomes: How do we coerce the PI Data Archive into doing our aggregations for us? Thankfully, the PI Point Data Reference has this capability. So back in AF, we’ll add some attributes below Production Flow which will eventually show the numbers as desired.






If we begin by configuring Total 24h, note that we already have a perfectly good PI tag to go and totalize – whichever tag Production flow is pointing at! So I’ll set this data reference to use Production flow’s tag as its source.








But the interesting part is below, where if time mode is set to be a time range, we get a wealth of “value retrieval” modes: among them, Total, just as we need in this example.






Our totalization requires some parameters – source units and rate, and the relative time range to use. Since our attribute is specifically a “past 24 hour total,” the appropriate time range is “-24h”






This attribute, which does totalizations relative to an end time, will be directly accessible in PI OLEDB Enterprise. Here, we see it in PI System Explorer:





Querying the statistical attributes

I threw together a query to query my wells for their 24-hour Production flow total, for two different time stamps:


between * (now) and 24 hours before now


between the beginning of today (t) and 24 hours before then


Remember, the “24 hours before then” part is what we set in the Total 24h attribute configuration. What we’re specifying in the query is what “then” means.



SELECT e.Name as Well
, REPLACE(ea.Path,'\','') as Metric
, ea.Name as Aggregate
, di.[Time] as [Time]
, di.ValueDbl as Value
, UOMAbbreviation(ea.DefaultUOMID) as UOM
FROM Asset.ElementHierarchy eh
INNER JOIN Asset.Element e
ON eh.ElementID = e.ID
INNER JOIN Asset.ElementTemplate et
ON et.ID = e.ElementTemplateID
INNER JOIN Asset.ElementAttribute ea
ON ea.ElementID = e.ID
INNER JOIN Data.ft_InterpolateDiscrete di
ON di.ElementAttributeID = ea.ID
WHERE eh.Path = '\Wells\' --restrict by Path
AND et.Name = 'Well Template' --Template
AND ea.Name = 'Total 24h' --Attribute
AND ea.Path = '\Production flow\' --Parent Attribute
AND di.Time IN ('*','t') --Two different timestamps to query
ORDER BY e.Name ASC, di.Time ASC







So there you have it – the ability to request all of the native PI Data Archive statistics from within PI OLEDB Enterprise (and any other PI AF client) relative to the time of your choosing.



First caveat

Flexibility and overhead. Quite obviously, this is not a flexible way of doing things. It requires the infrastructure (PI AF model) to contain pre-configured, aggregated attributes to be queried. This adds overhead and removes flexibility, but the good news is that the AF SDK contains (as of version 2.5) the underpinnings for PI OLEDB Enterprise to offer native PI statistics in the future. I dream of a future without need of this workaround… but until then, this can work well.




Can work well?”…. Ah! Thus, we arrive at the...

Second caveat

Cost. Remember that these statistical calls are done on-demand. The magic formula for cost is:


(Attribute count) * (Data rate) * (Time range) = (Cost)


So if you’re storing 60Hz data in your PI archive, please don’t call an on-demand daily average for 1000 attributes at once! Your data rate is probably much saner, but regardless: please don’t do (e.g.) year-to-date totalizations on-demand! That type of expensive call is why we have PI Totalizer tags. Or if Totalizers make you squeamish, then PI ACE. Either of those solutions can write year-to-date totalizations to a PI tag at the interval of your choosing, which you can expose in your PI AF elements and query (raw values) from PI OLEDB Enterprise. It is additional configuration overhead, but your PI Data Archive server will thank you. On-demand statistics follow the universal on-demand mantra:


if it’s cheap, do it on demand; if it’s expensive, schedule and store.



To get PI data statistics within PI OLEDB Enterprise, create AF attributes which expose a certain "value retrieval method" (average, total, etc) for a relative time range (e.g. -1h for the past hour). Then query those on-demand statistical attributes, interpolated, within PI OLEDB Enterprise. This works well for cheap/small statistical calls, but for expensive calls, use a calculate-and-store option like PI Totalizer tags or PI ACE.



Up next

Stay tuned for Part 2, a dive into exploiting the nuances of PI Time Syntax when we write reports – to get, for example, a week-to-day total in PI OLEDB Enterprise.



PI ACE is a nice and convenient tool to create programmable analytics with tags and the Module Database. However – the PI System goes asset centric, so how can we exploit PI ACE to work with PI AF?


In the following I would like to show you one way – using the PI AF SDK from PI ACE.


First – we want to use the .NET Framework 4 profile, that requires some additional configuration for the PIACEClassLibraryHost executable. You will need to edit/create the PICAEClassLibraryHost.exe.config file located in PIPC\ACE\Scheduler and PIPC\ACE\Scheduler\x86. Here is what you need:


<?xml version="1.0"?>
supportedRuntime version="v4.0" />


Let’s start thinking what we want to do. In my simple example, I am going to check all elements below a defined root in a given hierarchy and count the elements where a specific attributes (Common_Alarm) value is “Alarm”. In my case I am going to have an output tag that will receive the result – I leave it to you to write the result to an AF Attribute if you prefer .




Now it’s time to open visual studio and start with our PI ACE Module. We use the above MDB module as the context and the AlarmCount alias as output:




To use the AF SDK – we have to add the reference to the AF SDK and we need the PI SDK as well to access our configuration module, the references should look like this:




Some objects will stay at the class level. These are the objects that we don’t want to initialize at every calculation:

    Private myPISystem As OSIsoft.AF.PISystem
    Private myAFDatabase As OSIsoft.AF.AFDatabase
    Private myRootElement As OSIsoft.AF.Asset.AFElement

The configuration stored in the MDB should be initialized only once, and we also want to keep the connection to the AF Server here. So let us look at the ModuleDependentInitialization:

    Protected Overrides Sub ModuleDependentInitialization()

        ' this is the configuration module
        Dim myModule As PISDK.PIModule
        myModule = PIACEBIFunctions.GetPIModuleFromPath(Context)

            ' Connecting to the PI System
            Dim ppAFServer As PISDK.PIProperty
            ppAFServer = myModule.PIProperties.Item("AFServer")
            myPISystem = New OSIsoft.AF.PISystems().Item(ppAFServer.Value)

            ' Connecting to the AF database
            Dim ppAFDatabase As PISDK.PIProperty
            ppAFDatabase = myModule.PIProperties.Item("AFDatabase")
            myAFDatabase = myPISystem.Databases.Item(ppAFDatabase.Value)

            ' Starting from the root element
            Dim ppRootElement4Calc As PISDK.PIProperty
            ppRootElement4Calc = myModule.PIProperties.Item("RootElement4Calc")
            myRootElement = myAFDatabase.Elements(ppRootElement4Calc.Value)
        Catch ex As Exception
            PIACEBIFunctions.LogPIACEMessage(OSIsoft.PI.ACE.MessageLevel.mlErrors, ex.ToString(),MyBase.Name)
        End Try
    End Sub


As mentioned in the beginning, I want to count elements that fulfill a certain condition. Here is my counting function:

    Function myCalc(ByVal myRootElement As OSIsoft.AF.Asset.AFElement, ByRef myCount As Integer) As Integer
        ' The Alarm
        Dim iAlarm As Integer
        iAlarm = 0
        ' Try to find a "Common_Alarm" attribute
            If (myRootElement.Attributes.Item("Common_Alarm").GetValue().Value = "Alarm") Then
                ' Found an alarm
                iAlarm = 1
            End If
            ' Count the number of elements with a Power attribute
            myCount = myCount + 1
        Catch ex As Exception
            ' no "Common_Alarm" attribute, nothing to report
            iAlarm = 0
        End Try
        ' Do the same for every child element and sum up
        For Each myChildElement As OSIsoft.AF.Asset.AFElement In myRootElement.Elements
            iAlarm = iAlarm + myCalc(myChildElement, myCount)
        ' return the Value
        Return iAlarm
    End Function

Please be aware that it will run recursively through the complete hierarchy below the myRootElement – this could be time consuming and you don’t want to run that on a complex hierarchy every second!


So what is left is to call the myCalc function in the ACECalculations. I have included some basic reporting to the message log. This should give you a good indication on how much work your calculation has to do and how long it takes. Finally this should give you some guidance on how often you want to run this :

        ' this is for time measurement only
        Dim lStart, lStop As Long

        ' Calculating the sum of all Alarm conditions
        Dim iAlarm As Integer
        iAlarm = 0
        ' Count the elements with the "Common_Alarm" attribute
        Dim iCount As Integer
        iCount = 0

            ' start of calculation
            lStart = Date.Now.Ticks()
            ' calculate
            iAlarm = myCalc(myRootElement, iCount)
            ' end of calculation
            lStop = Date.Now.Ticks()
            ' report to messagelog
            PIACEBIFunctions.LogPIACEMessage(OSIsoft.PI.ACE.MessageLevel.mlUserMessage, "Alarms:" & iAlarm & "/Count:" & iCount & "/Time:" & ((lStop - lStart) / 1000000.0).ToString("#,###.000"), MyBase.Name)
        Catch ex As Exception
            ' report to messagelog
            PIACEBIFunctions.LogPIACEMessage(OSIsoft.PI.ACE.MessageLevel.mlErrors, ex.ToString(),MyBase.Name)
        End Try

        If (iCount = 0) Then
            ' something wrong, or nothing to report :-(
            SendDataToPI = False
            ' set the value to the output tag
            AlarmCount.Value = iAlarm
        End If




I decided to break this part into two videos.  Part 2a is implementing the PI SDK into the WCF service created in Part 1 of this series.  Part 2b creates an ASP.Net Web API that connects it all together.  At the end of the two videos I have a working, but simple, RESTful type API which various mobile device can use to access PI data from an on-prem server.


PI to Mobile Part 2a - Azure Service Bus with OSIsoft PI SDK


In this video I extend our WCF service to use the PI SDK to access a PI System.  Data is relayed to Azure using a service bus.  In Part 2b I use this service bus to feed an ASP.Net Web API.  The goal is provide a Rest API PI service for mobile device to access PI data.


Here is 2a video




PI to Mobile Part 2b – ASP.Net Web API


In this video I create a RESTful API using ASP.Net Web API.  I connect to our service bus that was created in Part 1 and extended in Part 2a.  I cover how to deploy the entire project to Azure and end the video with a working example of a Web API service that provides data formated as JSON from a PI System.


Here is part 2b video






Source code is in two zip files because of file size limitations.  Unzip them in a common directory.


Download zip file 1 (11.8M)


Download zip file 2 (8.3M)


If you would like to test drive the API just open your browser and type the following:


This will return the last hour of values and times for simulation tag cdt158. Access is via Azure and then to the example PIConnect application running on my test server. You are not connecting to my server directly, but to the Web API hosted in Azure.  The range is in hours from now, so -1, returns the last hour of data.  Feel free to change the tag name and range.  If you have issues, please let me know, as this is kinda an experiment :)


I hope this inspires you to start thinking about how the cloud and mobile technology can help you innovate, and look good to the boss :)




PI AF 2012 Beta 3

Posted by skwan Aug 3, 2012

A funny thing happened to me on my way to work this morning.  My good friend and fellow Product Manager, Mr. Steve Pilon, the esteen PM for our data access products, wanted me to eat my last post.


in which I mentioned the PI AF 2012 Beta 2 was going to be the last beta released to vCampus prior to the release.  Well, ignore that post, because PI AF 2012 Beta 3 is here and available for you to download at the download center under pre-release.  This beta 3 provides support for PI OLEDB Enterpise (beta) to access PI Event Frames.  Please see this blog post on PI OLEDB Enterprise


You will of course need to install the Beta 3 PI AF Server with event frames support.  The PI AF 2012 Beta 3 also provides additional bug fixes since beta 2 in the PI AF Client so you should find it fairly stable.  The release is coming up real soon


If you find any problems with this Beta 3, you can contact me at, post on the forum.


Thanks, Steve



I am very pleased to announce the immediate availability of PI OLEDB Enterprise 2012 Beta! I know a lot of you guys have been waiting for this release to take advantage of PI Event Frames capabilities in your reporting and BI type activities, as well as in your custom applications. Well, we are one (big) step closer to it and I invite you to go download the Beta from the "Pre-Release" area of the vCampus Download Center and give it a good try!


***BEFORE YOU INSTALL: make sure you upgrade your AF Server toBeta 3, which is now also available in the "Pre-Release" area of the vCampus Download Center. This is necessary for the event frame functionality to work...


This Beta is a great opportunity to get familiar with the new functionality early on and provide us with feedback (i.e. if you find bugs or have enhancement requests you would like to submit). Of course we strongly encourage you to try it out and provide feedback as soon as you possibly can... the sooner you provide your feedback, the more likely it is that we have time to react in time for the actual release [;]


The main additions in this version are:

  • Event Frame tables have been added to a new EventFrame schema (read-only, as for the rest of PI OLEDB Enterprise)
  • Export/Import capability for custom database objects (e.g. Views, Transpose Functions) has been added to PI SQL Commander, which will come in handy when comes time to move from one AF Database to another (e.g. Stage to Production) - credits to Sergey for raising the issue over here!
  • Added a GetPIPoint Table-Valued Function, which is particularly useful if you are using a mix of the classic PI OLEDB Provider and PI OLEDB Enterprise, and you need to de-reference an Element Attribute or Event Frame Attribute and get its underlying PI Point.
  • The Asset.ElementReference table has been added. It complements the Asset.vElementReference table and simplifies queries in certain cases.

To help you with the new Event Frame tables, we added material to the PI SQL Query Compendium, available through the PI SQL Commander: first load the Event Frames contained in NuGreen_EventFrame.xml (using PI System Explorer) and then walk through and exercise the queries contained in EventFrame.sql:






Please see the User Guide and the Release Notes for more information on these new additions.


Aside from a few performance enhancements and (hopefully not too many) bug fixes, the only change you can expect between this Beta version and the final release, is the addition of the Transpose Function Wizard for Event Frame Templates (it's currently available for Element Templates only).


Keep in mind this is a pre-release version and as such, shouldn't be used in a Production environment - please refer to the OSIsoft vCampus End-User License Agreement for more details about CTP-specific licensing terms.


The PI OLEDB Enterprise product team looks forward to hearing from you! Please submit your feedback on the discussion forum or privately to

Filter Blog

By date: By tag: