Gregor

How to retrieve connection statistics from the PI Server message log using PowerShell Tools for the PI System

Blog Post created by Gregor on Aug 22, 2013

Introduction

This blog post is inspired by a post titled PI System adoption rate metrics at Generic vCampus Discussions forum but it takes also its root from many questions that we were asked on how someone could identify suspicious connections to PI Server or compare the amount of licensed clients against the actual usage.

 

When I received this question the first time many years ago, while working at OSIsoft Technical Support, the solution that I suggested was to create a scheduled task that would periodically hit a batch file. The batch file would query PI Network Manager Statistics through the execution of a piconfig.exe script and dump information into a text file. Anyone can think of various solutions for further treatment of data by a custom application or by the usage of Microsoft Excel.

 

The major disadvantage of this solution is that it only takes a sample at certain times and may miss what has happened between the sampling periods.

 

Times have changed. With PowerShell, Microsoft has introduced a powerful scripting tool that also supports the usage of .NET objects directly. OSIsoft development was so kind to offer PowerShell Tools for the PI System to the vCampus community; this new tool is building the bridge between PowerShell and the PI System.

 

I must admit that I am a beginner with PowerShell and don’t yet really feel “comfortable”. During my research I found people praising PowerShell with the highest sounds. Meanwhile I have recognized how it can be easy to do what you want and feel PowerShell has a bright future.

 

My team mate Mathieu Hamel has created the major part of the solution that we would like to introduce to the vCampus community with this post. While discussing the script concepts and details we recognized potential unexploited areas of PowerShell scripting with the PI System. While reading further to this blog post you might be interested to download and use the project files discussed. Please find them attached to this post or use this link.

 

As you know, vCampus has a section for community projects. We – the vCampus team – are a little sad that they are not really alive as they could be. We believe that community projects are great and enable a group of individuals and up to the whole community in certain cases to work together to create something that delivers value to the community. Doesn’t that sound great? Doesn’t that sound fun? I couldn’t think of something else delivering a higher social value within a forum.

 

To get to the point, we believe that PowerShell Tools for the PI System are not only a powerful way of scripting but has the potential for one or more great community projects. We would love to get your opinion about this. Please also let us know what you are interested in but please bear in mind that we don’t just want to serve you with examples. When you suggest something please expect playing an active part if it comes to a community project.

 

We are enthusiastic about the idea of seeing PI geeks like you more active on community projects with PowerShell Tools for the PI System soon. Hence we have limited the scope of the solution we present here today accordingly. The script is designed to provide basic functionality and leave room for extensibility. It also aims at users who are just interested to test or use it as is without diving into the code. Please document your interest by replying to this blog.

 

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

 

 

Concept

The concept mainly involves the following steps:

 

- Retrieval of message attributes from source PI Network Manager through PowerShell Tools for the PI System (used cmdlets GET-PIServer Get-PIMessage)
- Looking up the message ID as the first message attribute, comparing it against the “known” ID’s and deciding about further message treatment
- Creation of Custom Objects table in memory
- Refining information using Regular Expressions and storing results as facts into the Custom Objects table
- Exporting these facts into a comma separated file (CSV) or a self describing XML file that can be used to do further analysis in other software such as Microsoft Excel
- [Optional] Do further analysis to generate certain KPI’s and store them in separate comma separated (CSV) files.

 

 

 

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

Solution

The solution consists of the following files and folders:

Type

Name

Usage

Folder (1)

\PISystemstats

Log file target

Folder (2)

\PISystemstats\bin\PISYSSTAT

Holds the PowerShell module files

Folder (3)

\PISystemstats\export

Target folder for results

Folder (4)

\PISystemstats\Unit Tests &  Examples

Holds files that can be used for testing and automation

File

PISYSSTAT.psd1

PISYSSTATS Module Manifest (in Folder 2)

File

PISYSSTAT.psm1

PISYSSTATS Core Library (in Folder 2)

File

Automatic Debug - Test Unit.bat

Windows Batch to launch “Test Unit.ps1” (In Folder 4)

File

Test Unit.ps1

Test script

When you apply modifications to the module script, all you need to do is save it and reload the module again in a new PowerShell session. Alternatively you can use Remove-Module followed by Add-Module command to load the modified module script. If you are just interested in using the solution there is no need for editing the module script but we hope many of you are curious enough to at least read through the code. There are a lot of comments intended to make it easier for you to understand what is done within certain functions.

 

To make the usage easier we have created a Windows Batch file (Automatic Debug - Test Unit.bat) and a test script (Test Unit.ps1). When you are interested to use them, you will have to modify the paths specified inside both files. The Windows Batch file refers to the test Unit script while “Test Unit.ps1” refers the folder where the module files are located (Folder 2).

 

You will likely have to unblock both PowerShell module files (Folder 2). To do so open Windows file explorer, browse for the module files (\PISystemstats\bin\PISYSSTAT), right click one, select properties and click the [Unblock] button (at the very bottom at the General tab).

 

 

 

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

Limitations

Starting with PI Server 3.4.380, structured messages were introduced to the PI Message Log; messages now include fields such as “Message ID”. Hence our example requires PI Server 3.4.380 or later. Please note this is also requirement for PowerShell Tools for PI System.

 

Because the Message ID is a unique identifier for the kind of message, our example is using to select the pattern of information exposed in the message body.

 

Furthermore, because the approach is generating statistics based on log messages from PI Network Manager and that PI Network Manager does not log messages about existing connections periodically, we can expect only complete information for those connections that were established after the query period start and closed before the query period ends. Per default (tuning parameter MessageLog_DayLimit) PI Message logs are kept for 35 days only. Please keep this in mind when defining the query period and when interpreting the results.

 

Connection ID’s are uniquely used within a PI Network Manager session but after a restart PI Network Manager will start over again counting from 0. To avoid confusion we have built in a mimic that recognizes based on connections start- and end-times that PI Network Manager has been restarted. When this happens the index field within the connection facts object is increased by one. All connections with the same index fall into the same period between 2 PI Server restarts (if applicable).

 

PISYSTATS module may only work against Primary PI Servers in a collective environment. This is due to a bug in PowerShell Tools for the PI System introduced with version 1.1.0.0 (doesn't exist in 1.0.0.0) that will likely be fixed in the next release. I expect this will also allow PISYSTATS to work against Secondary collective members.

 

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

Requirements

For those users new to PowerShell, please read Jay’s blog post about PowerShell Tools for the PI System

 

- Windows PowerShell is supported on Windows XP, Windows Server 2003, Windows Vista, Windows Server 2008, Windows 7, Windows Server 2008 R2, Windows 8 and Windows Server 2012. Please note: While recent Windows versions such as Windows 2012 Server ship with Windows PowerShell included, it has to be installed separately on older Windows platforms such as Windows XP.
- Windows PowerShell 2.0 or later
- PowerShell Tools for the PI System 1.0.0.0 or greater

 

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

Preparation / Installation

Some of the following steps may be obvious for experienced PowerShell users but may puzzle one or the other user at "beginner" level.

 

1. Make sure you have Microsoft PowerShell for Windows 2.0 or higher installed ("Add or Remove Programs" / "Programs and Features") on the machine that you would like to use. If this machine is remote to your PI Server, please note that you don't have to install Microsoft PowerShell on your PI Server node.
2. Download and install OSIsoft the recent version of OSIsoft PowerShell Tools for the PI System available exclusively at vCampus Download Center under "Extras".
3. Download and install the package attached to this post. Please create a folder on the target machine (e.g. D:\PIPC\PISystemStats) and paste the content from attached ZIP file into this folder.

 

We have some additional recommendations you may find useful in particular as well as in general when working with PowerShell, OSIsoft PowerShell Tools for the PI System and PowerShell scripts.

 

1. Creating a Profile for PowerShell initialization
You may have recognized that after installing OSIsoft PowerShell Tools for the PI System that the belonging cmdlets are not available within new PowerShell sessions until you execute

 

 

 

Add-PSSnapin OSIsoft.Powershell

Things like this can perfectly be automated by creating a Windows PowerShell Profile. Please see e.g. MSDN Library for additional information about the Windows PowerShell Profile.
To create yourself a PowerShell Profile, please create profile.ps1 at C:\Users\<username>\Documents\WindowsPowerShell where <username> refers your logon name. Edit, enter above command and save profile.ps1
To verify if it is working, please launch a new PowerShell session and see if you can immediately use the PowerShell Tools for the PI System cmdlets e.g.:

 

 

 

Get-PIServer

Instead of loading the profile, you may see an error like the following one:

 

                                                                                                       
File C:\Users\<username>\Documents\WindowsPowerShell\profile.ps1 cannot be loaded because the execution
of scripts is disabled on this system. Please see "get-help about_signing" for more details.           

At line:1 char:2                                                                                       
+ . <<<<  'C:\Users\<username>\Documents\WindowsPowerShell\profile.ps1'                                
    + CategoryInfo          : NotSpecified: (:) [], PSSecurityException                                
    + FullyQualifiedErrorId : RuntimeException                                                         
                                                                                                       

 

The reason for this error is that script execution is disabled by default. Please scroll down to learn how you can enable script execution but before moving on to the next topic you may be interested in how to add the Get-PISystemStats module to your profile. For this purpose you need to provide the path to the PISYSSTATS library and manifest file which depends on where you've extracted the package to. In order to do something new, let's introduce the usage of a PowerShell variable for this purpose. Please add the following to lines to your profile.ps1 file:

 

$modulePath = "D:\PIPC\PS\bin\PISYSSTAT"
Import-Module $modulePath

Save the changes to disk and launch a new PowerShell session. To see if the profile has loaded properly, you can look for the PISYSTATS help:

 

Get-Help Get-PISystemStats

 

 

2. Enable script execution

 

There exist several different levels with Script Execution Policy. To see details you could e.g. use

 

Get-Help Set-ExecutionPolicy

There are again many additional options. I personally like using the online help:

 

Get-Help Set-ExecutionPolicy -online

For sure this doesn't work with Windows core installations but back to the topic... Our recommendation for setting the execution policy is the following one but you may want to check with your local IT:

 

Set-ExecutionPolicy RemoteSigned

If you run into any issues, please try if launching PowerShell as Administrator helps. Please feel free to open a thread at Scripting with Windows PowerShell forum for any kind of issues you experience.

 

 

 

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

 

 

Usage

At this point we expect you to have the Get-PISystemStats module loaded already. If the following examples do not work for you, please revisit the Preparation / Installation section. If you still see errors, please post them at the Scripting with Windows PowerShell forum.

 

I would like to start looking at the cmdlets help first what usually is a good idea with PowerShell:

 

PS C:\Users\Gregor> Get-Help Get-PISystemStats

 

NAME
    Get-PISystemStats

 

SYNOPSIS
    This function returns PI System statistics.

 


SYNTAX
    Get-PISystemStats [-ServerName] <String> [-StartTime] <String> [-EndTime <String>] [-OutputMode <Int32>] [-ShowUI <Boolean>] [-DBGLevel <Int32>] [<CommonParameters>]

 


DESCRIPTION
    This function returns PI System statistics. The Syntax is ...
    Get-PISystemStats ([-ServerName | -sn] <string>)
                      ([-StartTime | -st] <string>)
                      [[-EndTime | -et] <string>]
                      [[-OutputMode | -om] <int>]
                      [[-ShowUI] <boolean>]
                      [[-DBGLevel | -dbgl] <int>]

 


RELATED LINKS
     http://vcampus.osisoft.com/discussion_hall/generic_forums/f/101/t/4365.aspx

 

REMARKS
    To see the examples, type: "get-help Get-PISystemStats -examples".
    For more information, type: "get-help Get-PISystemStats -detailed".
    For technical information, type: "get-help Get-PISystemStats -full".

 

You should also try the following and look at the results:

 

Get-Help Get-PISystemStats -Examples
Get-Help Get-PISystemStats -Detailed
Get-Help Get-PISystemStats -Online

The link to the online help directs you to this thread that we will add a link to this blog to as soon as it is published. You may have to sign in with your SSO account but isn't that cool?

 

Ok, let's calm down and continue discussing the cmdlets parameters:

Parameter

Alias

Datatype

Usage

-ServerName

-sn

String

Name of the PI Server

-StartTime

-st

String

Period start

-EndTime

-et

String

Period end

-OutputMode

-om

Int

Report type definition

-ShowUI

 

Boolean

Show output No / Yes

-DBGLevel

-dbgl

Int

Debug Level

The -ServerName parameter is required and specifies the PI Server to retrieve the messages from. It's also valid to use the name of a Collective but the connection will always go to the Primary because of a bug in PowerShell Tools for the PI System.

 

Messages from PI Network Manager will be retrieved for the period specified by -StartTime and -EndTime. PI Server time format is used e.g. "*-1d", "*" and "10-Apr-2013 12:23"

 

The -OutputMode parameter is optional and can be used to specify how PISYSSTATS treats and aggregates results.

 

- 0 (default) manipulate internally the facts table; will generate up to 5 different reports in CSV format (Application Stats.csv, Applications.csv, IP Addresses.csv, Total Connections during uptime.csv and Trusts.csv).
- 1 will export the facts table in CSV format (connectionfacts.csv).
- 2 will export the facts table in XML format (connectionfacts.xml) including schema and data. 

 

With -ShowUI one can control if PISYSTATS generates a prompt ($true default) or not ($false). This parameter is useful when running Get-PISystemStats as a scheduled task. With ShowUI enabled PISYSTATS will display its progress and finally output to the prompt where reports have been generated.

 

Process PI Log messages 
    402 / 654             

 

With -DbgLevel  = 0 (default) only none verbose messages are logged. This is mainly Start and End of cmdlet execution. 1 is for showing warnings and internal messages, 2 for showing parsing results and internal variables

 

We found it useful using the combination of a batch file and a PowerShell script file for testing purposes. Another possible use case would be when scheduling PISYSSTATS together with one or more other tasks. Hence we enclosed the "Unit Tests & Examples" folder and it's content into our delivery. Before executing "Automatic Debug - Test Unit.bat" for the first time, please review the content and modify the path to "Test Unit.ps1" if necessary. 

 

@ECHO OFF
REM 64 bit
powershell -NoLogo -ExecutionPolicy RemoteSigned -WindowStyle Normal -NoExit -File "C:\DevProjects\PISYSSTAT\v1.0.0.8\Unit Tests & Examples\Test Unit.ps1"

Please note that the batch executes PoweShell with ExecutionPolicy set to RemoteSigned. The changed execution policy only lives inside the session launched by the batch. It is not persistent.

 

Please also review the path to "PISYSSTAT.psd1" and "PISYSSTAT.psm1" within "Test Unit.ps1" as well as the name of your PI Server node (here core-tex).

 

# Import the module
$modulePath = "C:\DevProjects\PISYSSTAT\v1.0.0.8\bin\PISYSSTAT"
Import-Module $modulePath

# See all available cmdlets
#Get-Command -Module "PISYSSTAT"

#Get-Help Get-PISystemStats -full
Get-PISystemStats "core-tex" "*-5d" -om 1 -dbgl 0

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

Reports

Except when executing Get-PISystemStats in silent mode (-ShowUI $false), the path to reports will show at the command prompt e.g.

 

The export process is completed. See the generated files under the folder: C:\DevProjects\PISYSSTAT\v1.0.0.8\Export\20130821_151611

 

Please note that the date and time is coded to the folder's name that is generated for a specific execution. You will find the resulting reports within that folder. What content you'll find depends on the selected OutputMode. I would have loved sharing an example of the connectionfacts report and to discuss some of the details but even after hiding several cells, there are by far too much to fit into the blog post format. The table is to wide to fit and the majority of the columns become cut. Just explaining the single columns doesn't appear necessary since my believe is that the headers are self explaining.

 

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

Frequently Asked Questions

As just said, I expect reports are being self explaining but you may have one or the other question. I'll try to answer the ones that I expect to come and reserve the freedom to add more later.

Q1: I see complete information for some connections but incomplete information for others. Why is this?

This is one of the limitations mentioned above. PI Network Manager recognizes incoming connection attempts, successful attempts and if a connection becomes closed. When reporting these messages to the PI Message Log on the PI Server, it uses different messages that have unique message ID's and contain certain kind of information. When we query the PI Message Log we need to specify a period by StartTime and EndTime. This introduces the risk to exclude related messages and the information they contain. This said, we pretty likely miss information that was logged before StartTime and after EndTime. This is also valid for connections that are currently alive when specifying EndTime with '*'.

Q2: Connectionfacts report contains an "Index" field that I couldn't locate in any of the messages from PI Network Manager. What is this about?

PI Network Manager counts connections as ID. It starts to count with 1 with the first incoming connection after PI Network Manager service start. With other words, if a PI Server is re-started for some reason, PI Network Manager will recycle connection ID's. To avoid information before and after a restart becomes confused, we introduced the 'Index' column that starts counting with 0 and is increased by 1 every time PI Network Manager reports that it just has been started. You can consider all information with the same Index belonging together while the same connection ID showing with a higher Index indicates PI Network Manager has been restarted. Since almost all PI Services have a service dependency on PI Network Manager you can easily consider a PI Network Manager restart a restart of the PI Server but not necessarily a reboot of the PI Server node.

 

 

 

 

 

 

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

 

 

 

 

 

 

Prospect

 

 

Have you tried PISYSTATS already? What information / report do you find valuable? Is it contained in one of the specific reports (OutputMode=0) or within the Connectionfacts (OutputMode 1 and 2)? If it's in the Connectionfacts, what's your idea of further refining (using Excel, enhancing PISYSTATS or other e.g. using the XML Connectionfacts report)? 

 

 

 

We are curiously looking forward to your feedback and encourage you to develop the "project" by introducing new functionality i.e. reports. When you do so we would appreciate you share your "stuff" with the community. Hence, please introduce a new OutputMode when implementing your own treatment of information. 

 

 

 

We also encourage you exploring the abilities and the power of PowerShell and PowerShell Tools for the PI System. This is cool stuff with a bright future! Why waiting for the future? Let's make it happen now!

 

[ Introduction ]     [ Concept ]     [ Solution ]     [ Limitations ]     [ Requirements ]     [ Preparation / Installation ]      [ Usage ]      [ Reports ]      [ FAQ ]      [ Prospect ]

Attachments

Outcomes