10 Replies Latest reply on Nov 9, 2017 7:37 PM by gregor

    Powershell cmdlets,

    HansBleijendaal

      I try to run powershell cmdlets, but I am struggling te get data.

       

      I want to run the "Find-AFEventFrame" command to find particular eventframes.

      Example: Filter on element, template, eventframe template etc...

       

      So in the cmdlets I need to provide the correct parameters and that is the problem.

      Which parameter to use and how?

       

      The first cmd return data, the second cmd givers error, see below.

      THe question is what parameter and how to use? What do I wrong?

      How to use wildcards?

       

       

      Find-AFEventFrame   -maxcount 10 -AFDatabase $afDB

      tartTime                : 11/2/2017 9:32:01 AM

       

      EndTime                  : 12/31/9999 11:59:59 PM

       

      TimeRange                : 11/2/2017 9:32:01 AM - 12/31/9999 11:59:59 PM

       

      Duration                 : 69970191h+27m+57s+850.998ms

       

      IsNew                    : False

       

      IsDirty                  : False

       

      CheckOutInfo             :

       

      Security                 : ReadWrite, Delete, Admin, ReadWriteData, Annotate

       

      IsTemporary              : False

       

      IsLocked                 : False

       

      AreValuesCaptured        : False

       

      HasChildEventFrames      : False

       

      HasChildElements         : True

       

      IsRoot                   : True

       

      Parent                   :

       

      Parents                  : {}

       

      PrimaryReferencedElement : 730PM001Log

       

      Analysis                 : 440-Koek persen

       

      EventFrames              : {}

       

      Severity                 : None

       

      CanBeAcknowledged        : False

       

      IsAcknowledged           : False

       

      AcknowledgedBy           :

       

      AcknowledgedDate         : 1/1/1970 12:00:00 AM

       

      ReferencedElements       : {730PM001Log}

       

      Name                     : 440-Koek persen 2017-11-02 09:32:01.149

       

      Description              :

       

      Database                 : CocoaMain

       

      Template                 : EFPerserijSubCyclus

       

      Type                     : None

       

      DefaultAttribute         :

       

      DefaultInputPort         :

       

      DefaultOutputPort        :

       

      DefaultUndirectedPort    :

       

      IsAnnotated              : False

       

      ExtendedProperties       : {}

       

      CategoriesString         : Perserij Cyclus;

       

      Categories               : {Perserij Cyclus}

       

      Attributes               : {PersStatus, Teller, Duur, KoekSoort...}

       

      Ports                    : {}

       

      ID                       : 5c68c83a-5c61-421e-0000-00000097b85f

       

      UniqueID                 : 5c68c83a-5c61-421e-0000-00000097b85f

       

      PISystem                 : KOOG-DMZ-PIAF

       

      IsDeleted                : False

       

      Identity                 : EventFrame

       

      CreationDate             : 11/2/2017 9:32:06 AM

       

      ModifyDate               : 11/2/2017 9:32:06 AM

       

      RevisionNumber           : 1

       

      StartTime                : 11/2/2017 9:31:25 AM

       

      EndTime                  : 12/31/9999 11:59:59 PM

       

      TimeRange                : 11/2/2017 9:31:25 AM - 12/31/9999 11:59:59 PM

       

      Duration                 : 69970191h+28m+33s+339.9964ms

       

      IsNew                    : False

       

       

      Find-AFEventFrame   -maxcount 10 -NameFilter 310-Open vulklep 2017-11-01 15:52:52.617 -AFDatabase $afDB Find-AFEventFrame   -maxcount 10 -NameFilter 310-Open vulklep 2017-11-01 15:52:52.617 -AFDatabase $afDB


      Find-AFEventFrame : Cannot bind parameter 'AFSearchMode'. Cannot convert value "15:52:52.617" to type "OSIsoft.AF.Asset.AFSearchMode". Error: "Unable to match the

       

      identifier name 15:52:52.617 to a valid enumerator name.  Specify one of the following enumerator names and try again: None, StartInclusive, EndInclusive, Inclusive,

       

      Overlapped, InProgress"

       

      At line:1 char:75

       

      +  Find-AFEventFrame   -maxcount 10 -NameFilter 310-Open vulklep 2017-11-01 15:52: ...

       

      +                                                                           ~~~~~~

       

          + CategoryInfo          : InvalidArgument: (:) [Find-AFEventFrame], ParameterBindingException

       

          + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,OSIsoft.PowerShell.FindAFEventFrame

       

       

      310-Open vulklep 2017-11-01 15:52:52.617 is the name of the eventframe....

       

       

       

       

      Find-AFEventFrame   -maxcount 10 -NameFilter 730PM002Log -AFDatabase $afDB

      Find-AFEventFrame : Parameter set cannot be resolved using the specified named parameters.

       

      At line:1 char:2

       

      +  Find-AFEventFrame   -maxcount 10 -NameFilter 730PM002Log -AFDatabase $afDB

       

      +  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

       

          + CategoryInfo          : InvalidArgument: (:) [Find-AFEventFrame], ParameterBindingException

       

          + FullyQualifiedErrorId : AmbiguousParameterSet,OSIsoft.PowerShell.FindAFEventFrame

       

       

       

       

      730PM002Log is the name of the element....

       

       

       

        • Re: Powershell cmdlets,
          gregor

          Hello Hans,

           

          I believe one of the problems is the amount of possible parameters one could use with Find-AFEventFrame.

           

          Please enclose strings into double-quotes e.g. when specifying a NameFilter.

          As far as I can see, the Element name is not included with the Event Frame name. Hence using the Element name as NameFilter will likely cause an empty result set being returned. You can use the Element (object) as input for Find-AFEventFrame. To Get an Element, Get-AFElement comes in handy but this method does not crawl the entire Element tree but only the children of the object passed.

          I have created a sample for your reference against Pump Condition Based Maintenance Demo. Please let us know if this gets you started and if you have additional questions.

           

          $afServer = Get-AFServer -Name "AFServer"
          $afDB = Get-AFDatabase -AFServer $afServer -Name "OSIDemo_Pump"
          $afRootElement = Get-AFElement -AFDatabase $afDB -Name "Pump Station"
          $afElement = Get-AFElement -AFElement $afRootElement -Name "Pump02" 
          $afEventFrames = Find-AFEventFrame -AFElement $afElement -NameFilter "*High Bearing Temperature*" -StartTime '1-Nov-2017' -EndTime '2-Nov-2017'
          $afEventFrames
          
          1 of 1 people found this helpful
            • Re: Powershell cmdlets,
              HansBleijendaal

              Hi Gregor,

               

              THank you for thius info, however these cmdlets are still very unclear.

              With our AF (which is much more complex than the pump-example) I have difficulty to reach an element.

              Currently with your example I can only reach the next level in hierachie above root, I cannot go deeper, without adding another line, see example:

               

               

               

               

              So I need some mechanisme to search through the hierarchie...

              Are there more examples or whitepapwers available to find out how to do this. With the current help-files *I have very much difficulty to understand.

              Below a screenshot of our AF

               

               

               

               

                

               

                • Re: Powershell cmdlets,
                  gregor

                  Hello Hans,

                   

                  The limitation with Get-AFElement is a known issue. Please see 171417 - Ability to search for AF Elements recursively through AF hierarchy

                  If you like to stick with OSIsoft.PowerShell and use the Element as the filter for EventFrames, than the only option is to "browse" from the root to the Element EventFrames belong to.

                  There are however other options like using AF SDK within PowerShell or just using the NameFilter. Before suggesting those, I would like to get a better understanding about which search criteria you like to apply.

                  Can you please set up a search in PI System Explorer and take a screenshot of the dialog?

                   

                  I wouldn't know of much resources for OSIsoft.PowerShell module at current time. Please allow me to ask if you are new to PowerShell in general or if you are just struggling with OSIsoft.PowerShell?

                  When I started creating the first PowerShell scripts, I read everywhere how powerful PowerShell is but found it not as intuitive right away. My personal takeaway with PowerShell is that there is a learning curve as well. In case your first steps with OSIsoft.PowerShell are your first steps with PowerShell at all, you may want to exercise one of the almost countless tutorials available in the internet before looking into OSIsoft.PowerShell.

                  If you are experienced with PowerShell but find it hard to use OSIsoft.PowerShell module and am missing examples, we will be happy to support you.  

                    • Re: Powershell cmdlets,
                      HansBleijendaal

                      Hi Gregor,

                       

                      Thanks for your info, this gives me a better insight about Osisoft.Powershell. Although I am a beginner with powershell, I start to learn how to deal with it. The most important reason for now is to be able to remove a large amount of eventframes from the database for particular elements. Now I created the following script that follows the AF-hierarchy and this works fine, however very slow if you need to remove several milions EF's. Also I have to change the script for another element.

                       

                       

                      "AF SDK with powershell" I did not look at, but I will dive into that too...

                       

                      Maybe it is better to use a direct SQL-statement to remove large amounts of eventframes. But in that case I need to know the exact table-hierachy for removing an eventframe. Or better, a script for remove an eventframe, because this info is stored in several tables...  What is your opinion about this?

                        • Re: Powershell cmdlets,
                          gregor

                          Hello Hans,

                           

                          Deleting millions of EventFrames sounds like a tedious and time consuming task. What went wrong?

                           

                          Because PI OLEDB Enterprise provides you with read-only access, I assume you refer to deleting from PIFD database directly. I like to discourage you from doing this because we don't support direct access to the PIFD database.

                           

                          Have you looked into using PI System Explorer or PI Builder add-in to MS Excel? The search for EventFrames should be pretty complete and hence convenient. The delete operation itself will still take time.

                            • Re: Powershell cmdlets,
                              HansBleijendaal

                              Hi Gregor,

                               

                              In fact nothing went wrong..., but we did not realized we were logging such an amount of eventframes. In this case it was the cocoa-presses, total number is 20 machines, every cycle takes avg 5 minutes, every cycle generates minimal 10 eventframes (10 steps) resulta is more then 20 milj. EF's. per year. This is too much data for us at the moment, it slows down the reports and it really does not add so much additional info.

                              We changed the logging into once per day per product type: this is more then 20 times less. With this info we still have enough information we need about the press-cycles.

                              But we already stored more then 1 year EF's, and this amount stands in the way so I am deleting them with the script. First all EF's for those assets are deleted, and after that we create with backfill for those assets the EF's again, but then based on the "once per day per producttype"- concept.
                              It will take some time, I expect untill the end of this month...

                                • Re: Powershell cmdlets,
                                  gregor

                                  Hello Hans,

                                   

                                  If I understand correctly, you like to purge all EventFrames for a particular database. Is this the case? Let's see if we can buy you some time.

                                  I have again used the Pump Demo database mentioned earlier. The first thing which I did was stopping all Analysis generating EventFrames.

                                  I than merged some of your PowerShell snippets with mine. The resulting script doesn't care about AFElements but just for the AFDatabase. It takes the EventFrames between 1-Jan-2016 and 9-Nov-2017 in chunks of 100 and deletes them 1-by-1. It took less than 5 minutes to wipe the 7311 EventFrames in my personal Pump Demo database. The script may run for several hours in your case but should do the job.

                                   

                                  $afServer = Get-AFServer -Name "AFServer"
                                  $afDB = Get-AFDatabase -AFServer $afServer -Name "OSIDemo_Pump"
                                  $hasEventFrames = $true
                                  Do
                                      {
                                          $afEventFrames = Find-AFEventFrame -AFDatabase $afDB -NameFilter "*" -StartTime '1-Nov-2016' -EndTime '9-Nov-2017' -MaxCount 100
                                          $efCount = $afEventFrames.Count
                                          Write-Host "Event Frames found: " $efCount
                                          foreach ($ef in $afEventFrames)
                                          {
                                              Write-Host "Deleting " $ef.Name
                                              Remove-AFEventFrame $ef -CheckIn
                                          }
                                          if ($efCount -eq 0) { $hasEventFrames = $false }
                                      } While ($hasEventFrames -eq $true)
                                  Write-Host "Done .."
                                  

                                   

                                  Please make sure you understand how the script is working and be cautious since the remove operations are irreversible.

                                  After purging all EventFrames, you will want to start those Analysis which are generating EventFrames again and configure them for backfilling. Please again be cautious.

                                    • Re: Powershell cmdlets,
                                      HansBleijendaal

                                      Hi Gregor, thanks for the coding.

                                      However, this code only filters on name, not of AFElement name and we do not want to eliminate all the EF's.  And the naming of the EF's is not such that we can filter on this only.

                                      But I merged your code and our code this is now deleting the EF's.

                                      The speed is about 25000/hr. We still need to delete about 8 Milj EF's, so it will take about 13 days.

                                      End of the moth's we are done.

                                       

                                      I hope that OsiSoft will provide in the future some advanced bulk delete tools for this kind of actions...

                                       

                                      Thanks

                                        • Re: Powershell cmdlets,
                                          gregor

                                          Hello Hans,

                                           

                                          As mentioned earlier, PI System Explorer is a useful tool.

                                          In the results view, click an Event Frame and press [Ctrl]+[a] on your keyboard to select all Event Frames retrieved through the search. Right-click and chose "Delete".

                                          PI System Explorer also offers a mimic to backfill Analysis and there is an option to permanently delete existing data before the recalculation.

                                      • Re: Powershell cmdlets,
                                        gmichaud-verreault

                                        The AFDiag utility has a /DelEF flag that allows you to bulk delete event frames.

                                        From the user guide:

                                        /DelEF: Deletes event frames from the PI AF SQL Server database with an end time before the date specified in month/day/year format (for example, /DelEF:"11/24/2016").Since this can be a lengthy operation, the command will be executed asynchronously.

                                         

                                        Note: You can only specify a time, so it is not possible to filter by name, template, etc. It deletes all event frames that end before the time specified

                                         

                                        Reading the whole thread, this does not appear like it would work for you in this case, but you may find it useful for other bulk deletions of EF's

                                         

                                        --

                                        Gabriel

                                        1 of 1 people found this helpful