Hi All,

 

Last Friday got one issue which needs to track changes done to AF Database in last few hours. My system configuration was such that it didn't allow AF Auditor to be enabled, so got to develop simple application using which fetched changes done to AF Database with its timestamp.

 

System Configuration :

AF Server: 2015 R2 (2.7.5.7166)

SQL Server: 2008 Standard Edition (Found via Knowledge Base Solution - How do I determine which version or edition of SQL is installed for CCH ProSystem™ fx Engageme…  )

 

As per AF Audit Trail prerequisite  , SQL Server 2008 Enterprise Edition is minimum one which is not being fulfilled in my environment.

 

The changes done to AF Server in last 7 days (default limit but configurable depending on requirement and AF Structure) is stored in FindChages_AT table of PIFD database, and it has 10 rows which make it hard nut to crack along with alien values  ,i.e. Guids and other ids .

With time constraint (because it was Friday ), I went for AF SDK to get the data and get task done .

 

 

Resources used to develop are:

  1. Find Elements by last modified date or recently changed/modified Elements  (Thanks Roger for this one )
  2. AFDatabase.FindChangedItems Method

 

Following is the basic code that has developed to get the data from FindChanges_AT table . It is console application:

 

  • Connect to AF Server and AF Database :

 

To save time I have taken defaults here, which can be replaced that contains AF server and database name to connect

 

PISystem myPISystem = new PISystems().DefaultPISystem;
Console.WriteLine("AF Server : {0}", myPISystem);

AFDatabase myDB = myPISystem.Databases.DefaultDatabase;
Console.WriteLine("AF Database : {0}", myDB);

 

  • Call FindChangedItems method :

     Here we are finding changes from specific start time, i.e. st.

 

            AFTime st = new AFTime("09/21/2016 08:00 AM",CultureInfo.CurrentCulture);
            Object op;


            Console.WriteLine("Start Time : {0}", st);

            // Find changes made
            List<AFChangeInfo> list = new List<AFChangeInfo>();
            list.AddRange(myDB.FindChangedItems(false, int.MaxValue, st, out op));

 

  • Get text writer to write console output into text file :

 

            FileStream fstrm;
            StreamWriter writer;
            TextWriter txtOutput = Console.Out;
            try
            {
                fstrm = new FileStream("./Output.txt", FileMode.OpenOrCreate, FileAccess.Write);
                writer = new StreamWriter(fstrm);
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot open text for writing");
                Console.WriteLine(e.Message);
                return;
            }


 

  • Go through list of AF Changes and have information in text file:

 

Here, my actual requirement is to get data of only first 2 hours, and I didn't find method in which we can put end time like start time in above point, so used goto here.

 

            AFObject myObj;
            int i=0;
            string parent;
          
  // Find the objects that have been changed.
            Console.SetOut(writer);
            foreach (AFChangeInfo info in list)
            {
                myObj = info.FindObject(myPISystem, true);
                try{
                    parent = AFElement.FindElement(myPISystem,info.ParentID).ToString();
                }
                catch
                {
                    parent=null;
                }
                Console.WriteLine("#{0}: Action-{1} ChangeTime-{2} Database-{3} Identity-{4} ChangedObject-{5} ParentObject-{6}",i, info.Action,info.ChangeTime,info.Database,info.Identity,myObj,parent );
                i++;

                if (i == 20001)
                    goto skip;
            }
skip:       Console.SetOut(txtOutput);                                  
            writer.Close();
            fstrm.Close();




 

So the data will be availbale in output.txt file. You can send data into SQL or Excel or any other source depends on your requirement.

 

using System;
using System.Globalization;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OSIsoft.AF;
using OSIsoft.AF.PI;
using OSIsoft.AF.Time;
using System.IO;
using OSIsoft.AF.Asset;

namespace Temp_FinChangesinAF
{
    class Program
    {        
        static void Main(string[] args)
        {
            FileStream fstrm;
            StreamWriter writer;
            TextWriter txtOutput = Console.Out;
            try
            {
                fstrm = new FileStream("./Output.txt", FileMode.OpenOrCreate, FileAccess.Write);
                writer = new StreamWriter(fstrm);
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot open text for writing");
                Console.WriteLine(e.Message);
                return;
            }            

            PISystem myPISystem = new PISystems().DefaultPISystem;
            Console.WriteLine("AF Server : {0}", myPISystem);

            AFDatabase myDB = myPISystem.Databases.DefaultDatabase;
            Console.WriteLine("AF Database : {0}", myDB);

            AFTime st = new AFTime("09/21/2016 08:00 AM",CultureInfo.CurrentCulture);
            Object op;
            Console.WriteLine("Start Time : {0}", st);

            // Find changes made
            List<AFChangeInfo> list = new List<AFChangeInfo>();
            list.AddRange(myDB.FindChangedItems(false, int.MaxValue, st, out op));=

            AFObject myObj;
            int i=0;
            string parent;
            
  // Find the objects that have been changed.
            Console.SetOut(writer);
            foreach (AFChangeInfo info in list)
            {
                myObj = info.FindObject(myPISystem, true); 
                try{
                    parent = AFElement.FindElement(myPISystem,info.ParentID).ToString();
                }
                catch
                {
                    parent=null;
                }
                Console.WriteLine("#{0}: Action-{1} ChangeTime-{2} Database-{3} Identity-{4} ChangedObject-{5} ParentObject-{6}",i, info.Action,info.ChangeTime,info.Database,info.Identity,myObj,parent );
                i++;

                if (i == 20001)
                    goto skip;
            }
skip:       Console.SetOut(txtOutput);                                    
            writer.Close();
            fstrm.Close();

            Console.WriteLine("Done");
            Console.Read();        
        }
    }
}











 

Always happy to have comments / suggestions .

 

 

Happy Learning !!!