4 Replies Latest reply on Dec 17, 2018 8:09 PM by BrianKHeritage

    Identifying tags with duplicate records.

    BrianKHeritage

      We have a situation at site where occasionally duplicate records are created for some of the tags.  The problem is intermittent and doesn't always happen on the same tag every time.  Because this is an intermittent problem, OSISoft has recommended we monitor the tags and get back to them when it happens again.  So to allow us to monitor, I've come up with a Powershell script that will list all the tags on the system and their 11:00:00 value.  If there is more than one value, both are output.  This is the script I wrote:

       

      # connect to PI Data Archive by machine name
      #$PIServerHost = "OTCENPIDADEV1"
      $PIServerHost = "OTCENPIDAPRD1"
      $con = Connect-PIDataArchive -PIDataArchiveMachineName $PIServerHost -ErrorAction Stop

      if ($con.Connected)
      {
          # get a collection of PIPoints matching mask "*CN*"
          $PIPoints = Get-PIPoint -Name *CN* -AllAttributes -Connection $con

          if ($PIPoints -ne $null)
          {
              foreach ($PIPoint in $PIPoints)
              {
                  $ProcessTag = $PIPoint
                  $ProcessTagName = $ProcessTag.Point.Name
                  Write-Output "Now processing $ProcessTagName"
                  Get-PIValue -PIPoint $PIPoint -Starttime "17-Dec-2018" -EndTime "18-Dec-2018" | Where-Object { ($_.Timestamp).timeofday -eq "11:00:00"} | Select-Object Timestamp,Value
              }
          }
      }

       

      And this is an example of the output:

       

      Now processing CN_AMB_II_Menor

      2018-12-17 11:00:00 AM     0

      Now processing CN_AMB_IV_Grave

      Now processing CN_AMB_I_Insignificante

      Now processing CN_AMB_V_Catastrofico

      2018-12-17 11:00:00 AM     0

      2018-12-17 11:00:00 AM   25

       

      This is good, but I then have to scroll through the listing (almost 5000 tags) looking for any tags with two records.  What I'd like to see is:

       

      Now processing CN_AMB_II_Menor

      Count: 1

      Now processing CN_AMB_IV_Grave

      Count: 0

      Now processing CN_AMB_I_Insignificante

      Count: 0

      Now processing CN_AMB_V_Catastrofico

      Count: 2

       

      Something like this would help because then all I'd need to do is use the find option in Notebook and look for "Count: 2".  Alternatively, something like this would be better:

       

      Now processing CN_AMB_II_Menor

      Count: Good

      Now processing CN_AMB_IV_Grave

      Count: Good

      Now processing CN_AMB_I_Insignificante

      Count: Good

      Now processing CN_AMB_V_Catastrofico

      Count: Bad

       

      This would be ideal, because I'd only need to look for the word "Bad".

       

      Any help you folks could give would be greats.  Thanks in advance.

        • Re: Identifying tags with duplicate records.
          Lal_Babu_Shaik

          Hi Brian

           

          Quick way to check duplicate values using PI OLEDB .

           

          Open PI SQL commander and use below query.

           

          SELECT TAG, TIME  FROM PIARCHIVE..PICOMP2  WHERE TAG IN ('TAG1', 'TAG2’) AND TIME BETWEEN '*-7d' AND '*'  AND _INDEX > 1

          GROUP BY TAG, TIME 

           

          Additional reference:PI OLEDB Provider

           

          Thanks

          Lal

           

           

           

           

           

            • Re: Identifying tags with duplicate records.
              BrianKHeritage

              Thanks Lal.  Your solution is a good one, however what I'm hoping for is changing the Powershell script to give output in the one of the two examples.  The user is quite used to running this script and what I'm trying to do is give him an easier criteria.  Searching for "Bad" is a lot easier than scrolling through 5000 tags and trying to spot when there are two records.  Another reason I want this full listing is that it confirms to the user that the script looked at every tag on the system.

            • Re: Identifying tags with duplicate records.
              ssatpathy

              Hi Brian,

               

              You can just store the object returned by the Get-PIValue cmdlet like this:

               

              $values = Get-PIValue -PIPoint $PIPoint -Starttime "17-Dec-2018" -EndTime "18-Dec-2018" | Where-Object { ($_.Timestamp).timeofday -eq "11:00:00"} | Select-Object Timestamp,Value

               

              $values.Count will contain the number of values returned and you can print these out as you'd like.

                • Re: Identifying tags with duplicate records.
                  BrianKHeritage

                  Thanks Shrey,

                   

                    This is exactly the answer I was looking for!!  From this I built the following:

                   

                  $values = Get-PIValue -PIPoint $PIPoint -Starttime "17-Dec-2018" -EndTime "18-Dec-2018" | Where-Object { ($_.Timestamp).timeofday -eq "11:00:00"} | Select-Object Timestamp,Value

                   

                  $GoodBad = "Good"

                  if ($values.Count -gt 1)

                     {

                         $GoodBad = "Bad"

                     }

                  Write-Output "Count: $GoodBad"

                   

                  The above gave me output that looks like this:

                   

                  Now processing CN_AMB_II_Menor

                  Count: Good

                  Now processing CN_AMB_IV_Grave

                  Count: Good

                  Now processing CN_AMB_I_Insignificante

                  Count: Good

                  Now processing CN_AMB_V_Catastrofico

                  Count: Bad

                   

                    Thanks so much for the help!!