2 Replies Latest reply on Aug 17, 2018 4:31 PM by a.olsen

    Question about substituted values


      We use older Excel sheets with PiPutVal macros to record some laboratory data still. Since the workbooks inception (20 years ago) there have been some issues.

      There are buttons that enable the user to input a time and a date. Then clear and submit the entered data.

      For some reason unknown to me, every Thursday at the 6 AM timestamp, the value is substituted for some random number. It only happens for the RVP cell, and only on Thursdays at 6 AM. No matter what they enter, the data sent to PI when clicking record changes.

      You can see the spikes every Thursday at 6 AM.

      Any ideas? The macros are all the same. The formatting is the same for the cells.

      I can attach the sheet, but I'm not sure how macro enabled workbooks are handled.

        • Re: Question about substituted values

          Hi Andy,


          It sounds like there may be a scheduled task of some sort doing a calculation and writing a single value every Thursday at that time.


          What version of the PI Data Archive are you running?  If you are running version 2017 or later, there is a tuning parameter Snapshot_TracePointID that you can use to enable tracing for the troublesome tag.  This will record to the message log the application, machine name, and user that wrote a value to the tag which would be extremely helpful in tracking down where the odd writes are coming from.


          How many users do you have using the workbooks?  Also, are the data writes from the workbooks buffering via PI Buffer Subsystem?  If the workbook writes are buffered, we may still be able to track how the data is being written.  If you run "piartool -sd <tagname>" on the PI Data Archive, you can see the buffer source ID that last wrote to the tag.  Then, running "piartool -bfs" will show you all the buffered sources.  You could use this to find the machine writing the values.  You would probably want to time running these commands immediately after Thursday at 6:00am, and if the write is buffered, you will be able to find the machine it is coming from.


          Otherwise, tracking this down will be more tricky.  Since it's on a schedule, I would perhaps start by reviewing task scheduler on machines running the workbook and see if anything suspicious shows up.


          - Adam

            • Re: Question about substituted values

              That's the thing, there is only one workbook, and one user entering data each few hours. All other hour entries are logged correctly.

              It isn't scheduled, the user picks the timestamp and enters the value, then clicks the record button.

              Just on Thursdays, and only at 6AM