2 Replies Latest reply on May 3, 2018 9:25 AM by AKASH MISAL

    How to add eventframe attributes into SQL view

    AKASH MISAL

      Hello Everyone!

       

      I am working on Event Frames (AF 2016 R2) and I have created view into PI SQL commander as shown in below picture, right now I am getting output into view is "Event Name" , "Start time" and "End time".

       

      Now I need event frame attributes into same view, how would I get other attributes into view. I know for this I need to join two tables into this view but where I would get event frames attributes, please check below query which I have used in view.

       

       

       

       

      Regards,

      Akash.

        • Re: How to add eventframe attributes into SQL view
          rschmitz

          Hi Akhash,

           

          I would recommend taking a quick look at the example query from the manual for doing this operation. You can get this information by joining in the EventFrameAttribute table on the EventFrame ID and the EventFrameSnapshot (Or EventFrameArchive table depending on your needs) on the EventFrameAttributeID. The result would look something like the following:

           

          SELECT ef.Name EventFrame, ef.StartTime StartTime, ef.EndTime EndTime, efa.Name, efs.Time, efs.Value
          
           FROM
           (
               -- Get first 100 event frames derived from TestTemplate1 template
               SELECT TOP 100 *
               FROM [NuGreen].[EventFrame].[EventFrame]
               WHERE EventFrameTemplateID IN 
               (
                   SELECT distinct ID
                   FROM [NuGreen].[EventFrame].[EventFrameTemplate]
                   WHERE Name = N'TestTemplate1'
               )
           ) ef
           INNER JOIN [NuGreen].[EventFrame].[EventFrameHierarchy] efh
               ON efh.ID = ef.ID
           INNER JOIN [NuGreen].[EventFrame].[EventFrameAttribute] efa 
               ON efa.EventFrameID = ef.ID
           INNER JOIN [Nugreen].[Data].[EventFrameSnapshot] efs 
               ON efs.EventFrameAttributeID = efa.ID
          
           OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
          

           

          The result will print out a different row for each attribute you've queried for

           

           

          If you're looking to get all of the attributes values in a single row for a single Event Frame, I would recommend taking a look at the transpose functions (I find their output to be a little cleaner)

           

          Example Resulting output:

           

           

          Cheers,

          Rob

          3 of 3 people found this helpful