17 Replies Latest reply on Jan 16, 2012 5:01 PM by mhamel

    Snippet / Delete all PT - Created Values from Snapshot/Archive

    wpurrer

      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using PISDK;
      
      
      namespace PIMS_RemovePTCreated
      {
          class Program
          {
              static void Main(string[] args)
              {
                  PISDK.PISDK g_SDK;
                  g_SDK = new PISDK.PISDKClass();
                  PISDK.Server _Server = g_SDK.Servers["xxxx"];
      
                  PointList points = _Server.GetPoints("Tag='*'");
      
                    int count = points.Count;
      
                    for (int i = 1; i <= count; i++)
                    {
                        PIPoint po = points [ i ] ;
      
                        PIValue pv = po.Data.Snapshot;
                        
      
                        string sValue;
      
                        if (pv.Value is PISDK.DigitalState)
                        {
                            sValue = ((PISDK.DigitalState)pv.Value).Name;
      
                            Console.WriteLine(po.Name + " " + sValue);
      
                            if (sValue == "Pt Created")
                            {
                                po.Data.RemoveValues(pv.TimeStamp, pv.TimeStamp, DataRemovalConstants.drRemoveFirstOnly);
                            
                            }
      
                        }
                        else
                        {
                            sValue = pv.Value.ToString();
                            Console.WriteLine(po.Name + " " + sValue);
                        }
      
                        
                    }
              }
          }
      }
      

       

        • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
          Asle Frantzen

          Thanks Wolfgang! Nice resource...

           

          You should probably click the "Insert Code" button in the toolbar before pasting, so that parts of the code won't get replaced by smilies

           

          Test:

           
          PIPoint po = points
          ;
          

           

           

          Edit: Nope,didn't work there either

              • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                Asle Frantzen

                It didn't work! (I sent Ahmad an email about it, hopefully they'll fix it)

                  • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                    Ahmad Fattahi

                    Asle,

                     

                    We have been aware of this. We have been trying to work around this by avoiding using i as the counter/iterator variable as much as we can. Let us look some more into it and see if there is a fix in the widget. thanks for bringing up the issue (this time I meant to have a smiley!)

                      • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                        Ahmad Fattahi

                        Wolfgang,

                         

                        Thanks for sharing the snippet. By the way, would you be able to post it here for future reference please?

                          • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                            ericvan

                            There is another way to do this in a more generic way that also works if there are already new snapshots after Pt Created.

                             
                            PISDK.PISDK sdk = new PISDK.PISDK();
                            PIPoint p = sdk.Servers["xxxxxxxx"].PIPoints["sinusoid"];
                            string DigState = "Pt Created";
                            PIValues pvs = p.Data.RecordedValues("*-2h", "*", BoundaryTypeConstants.btInside, 
                                string.Format("'{0}'=\"{1}\"",p.Name,DigState), FilteredViewConstants.fvRemoveFiltered, null);
                            
                            PISDK.IPIData2 ipid2 = (PISDK.IPIData2)p.Data;
                            ipid2.RemoveSpecifiedValues(pvs, null);
                            

                             

                             

                            This way you can remove any instance of a specific digital state over a specified period (in this case the last 2 hours) of time like for example Pt Created or I/O Timeout

                                • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                                  gpriyanka

                                  Wolfgang,

                                   

                                  I tried compiling your code but I get an error. I am using Visual Studio 2005 and PI SDK 1.3.8.388

                                   

                                  Error: No overload for method 'GetPoints' takes '1' argument

                                   
                                  PointList points = _Server.GetPoints("Tag='*'");
                                  

                                   and a similar error for Remove Values.

                                   
                                  po.Data.RemoveValues(pv.TimeStamp, pv.TimeStamp, DataRemovalConstants.drRemoveFirstOnly);
                                  

                                   Do I need to specify the PIAsynchStatus?

                                   

                                   What is the line PIPoint po = points supposed to end as?

                                   

                                  Thanks,

                                   

                                  Priyanka

                                    • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                                      hanyong

                                      Hi Priyanka.

                                       

                                      Depending on the combination of Visual Studio and PI SDK versions, in some cases the compiler will not think that the PIAsynchStatus argument in as an optional argument for methods that supports asynchronous execution. All you have to do is to specify NULL for the second parameter or instantiate PIAsynchStatus and pass it to the method call.

                                       

                                      As for your second question, I am guessing it is:

                                      PIPoint po = points/[i/]

                                      The '/' before the square brackets is to prevent the system to interpret it as an emoticon, just remove them in your code.

                                        • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                                          aabrodskiy

                                          There is an easy way to remove all these PT Created using simple query with PI OLEDB Provider - one query removes all of them at once and no restriction on times, etc..

                                           

                                          I used to find it the fastest way to do the thing...

                                            • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                                              gpriyanka

                                              Alex, Can you provide details for this query? I need to run it on a PI server with 4 million tags and I want to test its performance. Thanks.

                                                • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                                                  aabrodskiy

                                                  For a particular tag the query would look like the following:

                                                   
                                                  delete from picomp2 where tag = 'sinusoid' and status = -253
                                                  

                                                   -253 here is the Digital State of "PT Created" from the SYSTEM digital state set. Same way you can remove any other states, like Bad, Shutdown, etc...

                                                   

                                                  For all tags just remove the "tag='sinusoid" condition from the query.

                                                    • Re: Snippet / Delete all PT - Created Values from Snapshot/Archive
                                                      mhamel

                                                      Alex: For more safety before executing your query it would be preferable to test how many “records” you will remove by using the COUNT operator. I have seen many times where you do a typo. with the status for example and you would delete more than you expect. It is also preferable to set time boundaries to your query even if it is large. This will increase the speed of the process. Generally speaking you when you want to remove points with the PtCreated status, those would refer from the current year or less. Taking all these aspects together will give you this statement.

                                                       

                                                      SELECT COUNT(*) FROM PIARCHIVE..PICOMP2 WHERE TAG = 'TAG1' AND STATUS = -253 AND BETWEEN TIME = '*-1y' AND '*'

                                                       

                                                      If you want to proceed with more than a point at a time you can use the IN keyword and form a list of tags to remove the PtCreated status.

                                                       

                                                      SELECT COUNT(*) FROM PIARCHIVE..PICOMP2 WHERE TAG IN ('TAG1', 'TAG2', 'TAG3') AND STATUS = -253 AND BETWEEN TIME = '*-1y' AND '*'

                                                       

                                                      Priyanka: If you foresee to do the same on a 4 million tags PI System, it would probably better to create a simple stored procedure on Microsoft SQL Server that would perform one tag at a time. In your case, “divide for conquer” principle would apply better. I propose you this script if you are interested.

                                                       

                                                      DECLARE @intMaxItems int

                                                       

                                                      DECLARE @intCurrentItem int

                                                       

                                                      DECLARE @vchCurrentTagName varchar(1024)

                                                       

                                                      DECLARE @Q varchar(2)

                                                       

                                                      DECLARE @T1 varchar(255)

                                                       

                                                      DECLARE @T2 varchar(255)

                                                       

                                                      DECLARE @nchvQueryTemplate nvarchar(1024)

                                                       

                                                      DECLARE @nchvQuery nvarchar(1024)

                                                       

                                                      DECLARE @ErrorCount int

                                                       

                                                      DECLARE @Result TABLE (

                                                       

                                                             AFFECTEDROWS  int           not null

                                                       

                                                      )

                                                       

                                                       

                                                       

                                                      DECLARE @TagList TABLE (

                                                       

                                                             ID     int                  identity,

                                                       

                                                             TAG    varchar(1024)        not null

                                                       

                                                      )

                                                       

                                                       

                                                       

                                                      --Initialize

                                                       

                                                      SET @Q = ''''

                                                       

                                                      SET @T1 = '*-1y'

                                                       

                                                      SET @T2 = '*'

                                                       

                                                      SET @ErrorCount = 0

                                                       

                                                       

                                                       

                                                      --Extract the list of tag to browse for deletion.

                                                       

                                                      INSERT INTO @TagList (TAG)

                                                       

                                                      SELECT * FROM OPENQUERY(PI, 'SELECT TAG FROM PIPOINT..CLASSIC ORDER BY TAG')

                                                       

                                                       

                                                       

                                                      --Get the number of items received.

                                                       

                                                      SET @intMaxItems = (SELECT MAX(ID) FROM @TagList)

                                                       

                                                      --Initialize counter.

                                                       

                                                      SET @intCurrentItem = 1

                                                       

                                                      SET @nchvQueryTemplate = N'SELECT * FROM OPENQUERY(PI,' +

                                                       

                                                                                 @Q +

                                                       

                                                                                 'DELETE FROM PIARCHIVE..PICOMP2' +

                                                       

                                                      'WHERE TAG = %TAG% AND STATUS = -253' +

                                                       

                                                      'AND TIME BETWEEN %TIME1% AND %TIME2%' +

                                                       

                                                                                 @Q + ')'

                                                       

                                                       

                                                       

                                                      WHILE (@intCurrentItem < @intMaxItems)

                                                       

                                                      BEGIN

                                                       

                                                             --Get the current tag name.

                                                       

                                                             SET @vchCurrentTagName = (SELECT TAG FROM @TagList WHERE ID = @intCurrentItem)

                                                       

                                                       

                                                       

                                                             --Replace the tokens (%TOKEN%) with the query template with the tag, time1 and time2 values.

                                                       

                                                             SET @nchvQuery = @nchvQueryTemplate

                                                       

                                                             SET @nchvQuery = REPLACE(@nchvQuery, '%TAG%',

                                                       

                                                      @Q + @Q + @vchCurrentTagName + @Q + @Q)

                                                       

                                                             SET @nchvQuery = REPLACE(@nchvQuery, '%TIME1%', @Q + @Q + @T1 + @Q + @Q)

                                                       

                                                             SET @nchvQuery = REPLACE(@nchvQuery, '%TIME2%', @Q + @Q + @T2 + @Q + @Q)

                                                       

                                                            

                                                       

                                                             --Validate the query.

                                                       

                                                             PRINT @nchvQuery

                                                       

                                                            

                                                       

                                                             --Execute the query dynamically against the Linked Server.

                                                       

                                                             INSERT INTO @Result EXEC sp_EXECUTESQL @nchvQuery

                                                       

                                                            

                                                       

                                                             --Validate if the operation worked otherwise reports it...

                                                       

                                                             IF(SELECT AFFECTEDROWS FROM @Result) <> 1

                                                       

                                                             BEGIN

                                                       

                                                                    --Add a logic to report the problem with this point.

                                                       

                                                                    SET @ErrorCount = @ErrorCount + 1

                                                       

                                                             END

                                                       

                                                            

                                                       

                                                             --Clear the table.

                                                       

                                                             DELETE FROM @Result

                                                       

                                                            

                                                       

                                                                    --Increment the current item counter by one.

                                                       

                                                             SET @intCurrentItem = @intCurrentItem + 1

                                                       

                                                      END