Skip navigation
All Places > PI Developers Club > Blog > 2016 > April > 08

Hello Everyone,

 

Today I am presenting you a technical solution to query PI Web API data from Excel.  I was thinking of this for a while now and I think this may be very valuable for our community because:

  • I know you love Excel , and I am also quite certain we will still see VBA around for a while, until everything becomes Javascript maybe? (you should start to learn it if you have not started yet!).
  • It brings access to AF from VBA via PI Web API, something that up to now could only be done using a COM Wrapper (for Processbook VBA or Excel VBA).
  • It also brings a solution to avoid using the PI SDK, even though this is still a good piece of technology, we have to prepare and detach ourselves a little bit from it...

 

What to expect?

At this time, I am just showing you the proof of concept, it covers having everything setup to make API calls and get the results.  It is to be expected that this will require some work to parse the results returned by the API.

I'd like to hear from you if you would like it to be a community project, a library that we could build together, I am up to it.

 

Library used

I am using an Excellent library called VBA-Web.  It is well documented and made to work with complex web services.

GitHub Repository and Download    |     Documentation

 

Authentication

The library seems quite mature and has many authenticators available.

I have used the Windows Authenticator in my environment and it worked well.  I am not yet certain though that this Windows Authenticator will work in all situations as it seems to be limited to NTLM.

Please let me know if you find that this does not work for you I will update the content in this section.

 

Depending on your installation, PI Web API can be configured for :

  • Basic Authentication
  • Windows Authentication (Kerberos)
  • Anonymous Authentication (basically, no security.)

 

So this mean you may need to select a different Authenticator, they are available here.

 

Step by Step Approach

Setting up the Excel File

  • First, you'll need to download the VBA-Web .zip file, available on GitHub.
  • Unzip the content, and copy the file "VBA-Web - Blank.xlsm" to a working directory.

  • Rename the file to something more meaningful to your intention, e.g. PIAPIWeb, AFStats, etc.
  • Open the file
  • Copy the content of the Authenticator from GitHub, here. (The rest of these steps are assuming the usage of the Windows Authenticator).  In GitHub, Click on the Raw view to copy the content easily.
  • Create a new text file on your computer, called WindowsAuthenticator.cls
  • Paste the content into the file and save the file.
  • Open the VBA Editor in Excel (Alt+F11) and navigate to Class Modules.
  • Right Click on "Class Modules" then select import file.  Then Select the file WindowsAuthenticator.cls you just created

 

Creating the module to get data from the PI Web API

Ok, so now we are ready to write some code for our own purpose: PI Web API.

  • Create a new Module in the Project, I called it ModPIWebAPI, you can give it the name you like.
  • Then copy paste this code into the Module  and change the PI Web API Address on line 2:
' Base URL of the PI Web API Installation - you should configure it to your server
Const BASE_URL As String = "https://megatron/piwebapi/"

Private Client As WebClient

' initializes the Web Client
Private Sub Init()
        
    ' set authentication to windows authentication
    ' Check the documentation if you need to set a different authentication type.
    Set Authenticator = New WindowsAuthenticator
    
    Set Client = New WebClient
    Client.baseUrl = BASE_URL
    Set Client.Authenticator = Authenticator
    
    ' use only for trusted servers, you should need this only for self signed certificates or with your development server
    ' Client.Insecure = True

End Sub

' This is a "generic function to get content from the PI Web API
' it returns a Web Response object
' see details here: http://vba-tools.github.io/VBA-Web/docs/#/WebResponse
'e.g. makeRequest("assetservers")
'     makeRequest("system/userinfo")
Public Function MakeRequest(resource As String) As WebResponse

    Call Init
    Dim Request As New WebRequest
    Request.Method = WebMethod.HttpGet
    Request.resource = resource

    Dim Response As WebResponse
    
    ' You should comment the line below after you are done with your development
    ' this is quite useful
    WebHelpers.EnableLogging = True
    Set Response = Client.Execute(Request)
    
    ' returns the results
    Set MakeRequest = Response


End Function

' This method returns the WebID of an AF Server
'  e.g.
' GetAssetServerWebId("megatron") --> S0glSFglj -OUWvbNrrcijzwgTUVHQVRST04
Public Function GetAssetServerWebId(ByVal name As String) As String


    Dim Response As WebResponse
    Set Response = MakeRequest("assetservers")
    For Each Item In Response.Data("Items")
        If UCase(Item("Name")) = UCase(name) Then
            GetAssetServerWebId = Item("WebId")
        End If
    Next Item
End Function

 

  • You can call the MakeRequest to Retrieve content from the PI Web API, any call.e.g:
makeRequest("assetservers")
makeRequest("system/userinfo")
  • GetGetAssetServerWebId implement some logic based on the MakeRequest method and it also shows how to parse the Response.Data property.  It is probably the beggining of more complex calls that you need to make with the PI Web API.  You can use this a a beggining for your own implementation

Client certificate and the insecure option

   Client.Insecure = True

You can use this option if you work in a development environment and you don't have proper certificates.  You need this option set to true when you see a screen like below in your browser when navigating to the PI Web API URL:

It is present in a commend in the Init() function of the above code.

Debugging

The WebClient provided in the library has a very nice debugging feature.

It is enabled on line 36 of the code above like this:

WebHelpers.EnableLogging = True

 

Here is an example of the log output after the client made a request - (in the immediate window of the VBA Editor (Ctrl+G))

--> Request - 5:14:29 PM
GET https://megatron/piwebapi/system/userinfo
User-Agent: VBA-Web v4.0.21 (https://github.com/VBA-tools/VBA-Web)
Content-Type: application/json
Accept: application/json
Content-Length: 0


<-- Response - 5:14:29 PM
200 OK
Date: Fri, 08 Apr 2016 15:14:29 GMT
Content-Length: 165
Content-Type: application/json; charset=utf-8
Server: Microsoft-HTTPAPI/2.0
WWW-Authenticate: oYGyMIGvoAMKAQChCwYJKoZIgvcSAQICooGaBIGXYIGUBgkqhkiG9xIBAgICAG+BhDCBgaADAgEFoQMCAQ+idTBzoAMCAReibARqRpisjsb4iwxaSPCsGU9VPnE48T7rSyN3Oyx0HWhqLAnDkITIAG085Jmmmg+2cEPhiCTNFtiy+gCUzl9LVUa3N0ZJDyf5kBHm0PZ6L6NFcFdTryz1NGXKhF9zGGPTR0vIIGA9A84FZRo80A==


{"IdentityType":"WindowsIdentity","Name":"DECEPTICONS\\megatron","IsAuthenticated":true,"SID":"S-1-5-21-1889902799-2021996611-654431455-1104","ImpersonationLevel":4}

 

More Features

Make sure that you have a look at the library documentation, is has many Web Helpers, such as UrlEncode/Decode that can be useful to prepare search queries and many many more.

 

Conclusion

As you can see, this implementation is very basic at the moment and you will need to work on parsing the results returned from the WebResponse object. In my next post I'll try to figure out how to make it more straightforward.

I also hope that you will like this library as much as me and I also believe that this opens a door to something new!

I am looking forward for your comments to see what you imagine can be done with it.

 

I attached my work on this post. Very basic as I said

--

I would like also to give a special thanks to Tim Hall, his VBA-tools library is just amazing!

His work his licensed under MIT License, this one of the most permissive license, you can do pretty much anything with it.

 

Edit 2016-05-27:

Did you ever try calling PI Web API from VBA within ProcessBook itself?

Raymond Verhoeff 

I just tested with PI ProcessBook and it also works, added the file to this post. Added details about the Insecure option as well that help when certificate is not secure, see Client certificate and the insecure option section above.

In the PI ProcessBook file, I implemented a small logic to retrieve the user info from the PI Web API.  To make it easier to make a quick test.

 

Filter Blog

By date: By tag: