Hi everyone,

 

A while ago, I asked a follow-on question in this post Start trigger based upon how long a tag has been at a certain value about creating an alert for when an outlier was repeatedly sent, but not for long periods. It wouldn't trip a duration notification (e.g., a value of 1 for 2 days), but may still indicate a problem if it kept occurring (e.g., 15 "bad" values out of 100 values in an hour).

 

Some options I found to track this:

AF Analytics: Re: Equation to count specific value in Analysis every 8 hours, Re: Analysis Expression to Sum # of Points = "1"

Totalizer tags: Pi count expression

Various: How to automate the list of stale and bad points in PI 2012?

 

Since I am limited in the amount of tags available to me, OSIsoft specifically recommends against using PI Notifications on every point to monitor them (KB00384), and I wanted a bit more flexibility in determining what constituted a "bad" value, I decided to create my own report using the AF SDK FilteredSummaries method. For reference, I'm using AF Server 2016 R2.

 

Some of the resources I used to develop the whole process:

Track changes happened on AF Database with AF SDK

AF SDK Get All Attribute Values for All Elements in a Specified TimeRange

Splitting, rearranging, excluding strings from a text file in Powershell to output to another text file

 

using OSIsoft.AF;
using OSIsoft.AF.Data;
using OSIsoft.AF.PI;
using OSIsoft.AF.Time;
using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using OSIsoft.AF.Asset;


namespace BadValuesReport
{
    class Program
    {
        // List of equations to evaluate against (uses PE syntax)
        private static List<string> Equations = new List<string>(){
                                                                    "TagVal('.') <= 1",
                                                                    "TagVal('.') = 2.5 * PrevVal('.','*') AND TagVal('.') > 1",
                                                                     "TagVal('.') = PrevVal('.','*-1mo')"
                                                                    };
        static void Main(string[] args)
        {
            // Delete yesterday's file
            string oldFile = "C:/TagReports/raw-bad-value-report.txt";
            File.Delete(oldFile);
                
            FileStream fstrm;
            StreamWriter writer;
            TextWriter txtOutput = Console.Out;

            try
            {
                fstrm = new FileStream("C:/TagReports/raw-bad-value-report.txt", FileMode.Append, FileAccess.Write, FileShare.Write);
                writer = new StreamWriter(fstrm);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Cannot open text for writing");
                Console.WriteLine(ex.Message);
                return;
            }

            // Connect to the AF server and Database
            PISystems myPISystems = new PISystems();
            PISystem myPISystem = myPISystems.DefaultPISystem;
            PIServer myPIServer = PIServer.FindPIServer(myPISystem, "AFServer");
            AFDatabase myDB = myPISystem.Databases["AFDatabase"];

            string startTime = "*-3d";
           string endTime = "*";

            PIPagingConfiguration config = new PIPagingConfiguration(PIPageType.TagCount, 100);
                
            // Find all attributes that meet the criteria
            AFAttributeList al = AFAttribute.FindElementAttributes(myDB, null, null, null, null, AFElementType.Any, "Attribute Template",
                null, TypeCode.Empty, true, AFSortField.Name, AFSortOrder.Ascending, 1000);

            // Count the number of times an equation evaluates to True for each attribute over the TimeRange
            Console.SetOut(writer);
            foreach (string eq in Equations)
            {
                var FilteredResults = al.Data.FilteredSummaries(
                new AFTimeRange(startTime, endTime),
                new AFTimeSpan(days: 3),
                eq,
                AFSummaryTypes.Count,
                AFCalculationBasis.EventWeighted,
                AFSampleType.ExpressionRecordedValues,
                AFTimeSpan.Zero,
                AFTimestampCalculation.Auto,
                config);                                                         
                    
                {                        
                    foreach (var result in FilteredResults.ToList())
                    {
                        string point = (result[AFSummaryTypes.Count].PIPoint.Name).ToString();
                        int count = Convert.ToInt32(result[AFSummaryTypes.Count].First().Value);
                            
                        Console.WriteLine(String.Format("{0},{1}", point, count));               
                    }                                              
                }
            }
            Console.SetOut(txtOutput);
            writer.Close();
            fstrm.Close();
                
            // Sort output so equation results for each point are grouped
            string inFile = @"C:/TagReports/raw-bad-value-report.txt";
            string outFile = @"C:/TagReports/raw-bad-value-report.txt";
            var contents = File.ReadAllLines(inFile);
            Array.Sort(contents);
            File.WriteAllLines(outFile, contents);
        }
    }
}

An example of the sorted output is attached.

 

For anyone unfamiliar with using PE syntax, check out the Live Library. To test your equation, go to SMT > Points > Performance Equations > Equation and enter your equation in the box. Replace the period (.) in '.' with a tag to test whether the function works. For example, TagVal('.') <= 1 would become TagVal('L01_B000_Building0_000AMW_ALC') <= 1. You'll get a parsing error if something is not right.

 

I then process it using PowerShell to sum the totals from each equation for each Attribute, then filter it so only those with 2 or more "bad" values are kept in the report.

 

$BadFile = "C:\TagReports\raw-bad-value-report.txt" 

$BadOutput = import-Csv $BadFile -Header Tag,Count 

$BadProcess=
($BadOutput | Group-Object Tag | %{
    New-Object psobject -Property @{
        Tag = $_.Name
        Count = ($_.Group | Measure-Object Count -Sum).Sum}
 })
$BadProcess| Where-Object { $_.Count -gt '1' -and $_ -notmatch 'OSIsoft'} | 
    Select-Object *,@{Name='Building';Expression={"{0} {1}" -f $($_.Tag -split '_')[1..2]}}|
       Format-table -Groupby Building -Property Tag,Count | Out-File C:\TagReports\daily-bad-values-report.txt -width 150

$BadFileContents = Get-Content "C:\TagReports\daily-bad-values-report.txt"

$BadMatch = Select-String -InputObject $BadFileContents -Pattern "L01" -AllMatches

$BadReport = "C:\TagReports\daily-bad-values-report.txt"

#Rename report to include date
$BadDatedReport = "C:\TagReports\Bad_Values_Report_{0}.txt" -f (get-date -Format MM-dd-yyyy)
rename-item $BadReport $BadDatedReport

 

An example of what the final report would look like is attached.

 

In case anyone finds this helpful, below is the rest of my code, which emails the final summary report I just created. I automate this whole process using Windows Task Scheduler.

 

#Create email
$smtpServer = "mail.server.com"

$body = @"
Attached is the tag report for today, $(Get-Date -format 'D').<br>
<br>
<br>
There are <b>$($BadMatch.Matches.count) tags</b> that have received multiple bad values (negatives (-), zeros (0), ones (1),  values without increase in the past month, and/or values greater than 2.5 times the previous reading) in the past <b>three days</b>.
<br>
"@

$att = new-object Net.Mail.Attachment($BadDatedReport)

$msg = new-object Net.Mail.MailMessage

$smtp = new-object Net.Mail.SmtpClient($smtpServer)

$msg.From = "email@mail.com"

$msg.To.Add("test@email.net")

$msg.Subject = "Tag Reports for $(Get-Date -format 'D')"

$msg.Body = $body

$msg.IsBodyHtml = $true

$smtp.Send($msg)

$att.Dispose()

I welcome any comments or suggestions This is my first blog post, so feedback on how I can improve future ones is welcome!