6 Replies Latest reply on Feb 9, 2018 5:17 PM by Devin

    UFL Connector INI help for some practical common REST endpoints

    Devin

      I am evaluating the UFL Connector and need a little hand-holding with how to get the ini files right for a few REST end points. I am playing with NOAA weather (want to pull in current temp and hour rainfall) as well as a river stage from USGS for my town. I am banging my head on how to format my datetime for a field when the data is coming in formats such as 2018-02-05T19:56:00+00:00. How do you filter out characters such as "T" or want to ignore everything after the minutes? Also, how do you set a message filter to look down different levels of inner JSONs? Or maybe it does this automatically until it reaches something that matches (or does it just stay at the top level)? For example, the river stage level from USGS is 5 or 6 levels down and the word "value" is used several times at different levels. There is only one river stage number that needs to be processed in the JSON as it only is querying for the most recent value, so I don't think looping applies? All I need to do is grab two things from this JSON. I have poured over GitHub examples as well as read the Connector user guide. A little more explanation on how this works, and I think I could be rocking and rolling! Much thanks.

       

      It would be wonderful if during configuration of the data sources, all you needed to do was highlight and define values in the REST output interactively and the ini could be built automatically. Don't know if that is possible, but would sure be nice. Maybe future release?

        • Re: UFL Connector INI help for some practical common REST endpoints
          gmichaud-verreault

          Hi Devin,

           

          Edit: example here - Capture real-time weather data using the PI Connector for UFL 1.2

           

          Concerning the timestamp, you can simply define the format as such:

          FIELD(x).NAME="TimeStamp"

          FIELD(x).TYPE="DateTime"

          FIELD(x).FORMAT="yyyy-MM-ddThh:mm:ss.nnn-08:00"

           

          To navigate the JSON, you can simply use a combination of the new JsonGetValue() and JsonGetItem() functions.

          I would suggest taking a look at the following example:

          https://github.com/osisoft/PI-Connector-for-UFL-Samples/blob/master/INI_FILE_EXAMPLES/Examples_for_Version_1.2/Example22_ForEach_Json.ini

          https://github.com/osisoft/PI-Connector-for-UFL-Samples/blob/master/INI_FILE_EXAMPLES/Examples_for_Version_1.2/Example22_ForEach_Json.json

           

          Gabriel

          1 of 1 people found this helpful
          • Re: UFL Connector INI help for some practical common REST endpoints
            mhruzik

            Hi Devin Doring, I am one of the UFL developers and I believe the following INI should provide you the data you need including the timestamp. However you will need the latest version of the connector - 1.2. You can also take a look here for a better explanation of JSON parsing: PI Connector for UFL 1.2 is ready. Loops, JSON & CSV native support and much more....  Hope it helps

             

            [FIELD]
            FIELD(1).NAME = "Value"
            FIELD(1).TYPE = "Number"
            FIELD(2).NAME = "Timestamp"
            FIELD(2).TYPE = "DateTime"
            FIELD(2).FORMAT = "yyyy-MM-ddThh:mm:ss.000-08:00"
            '"dateTime": "2018-02-09T03:00:00.000-08:00"
            FIELD(3).NAME = "Temp"
            FIELD(4).NAME = "Temp2"
            
            
            [MSG]
            MSG(1).NAME = "Data"
            
            
            [Data]
            Data.FILTER = C1=="*" 
            
            
            FOREACH (JsonGetItem(__MESSAGE, "value\timeSeries[]")) DO
                temp = __ITEM
                PRINT(__ITEM)
                FOREACH (JsonGetItem(temp, "values[]")) DO
                    temp2 = __ITEM
                    PRINT(__ITEM)
                    FOREACH (JsonGetItem(temp2, "value[]")) DO
                        Value = JsonGetValue(__ITEM, "value")
                        Timestamp = JsonGetValue(__ITEM, "dateTime")
                        StoreEvent("waterlevel","wt",timestamp,value)
                    ENDFOR
                ENDFOR
            ENDFOR
            
            2 of 2 people found this helpful
              • Re: UFL Connector INI help for some practical common REST endpoints
                Devin

                Thanks Martin! I will try it out. I was getting closer I think when I look at your code and compare to what I currently have. I tried running this and only got a bunch of errors.

                 

                '============Define Variables=========
                [FIELD]
                FIELD(1).NAME= "Timestamp"
                FIELD(1).TYPE= "DateTime"
                FIELD(1).Format= "yyyy-MM-ddThh:mm:ss.nnn"
                FIELD(2).NAME= "Stage"
                FIELD(2).TYPE= "Number"
                '================Define Message Statements===========
                [MSG]
                MSG(1).NAME= "getdata"
                '================Extract Variables and Store PI Points===========
                [getdata]
                getdata.FILTER=C1=="*"
                FOREACH (JsonGetItem(__MESSAGE, "Value[]")) DO
                     FOREACH (JsonGetItem(__MESSAGE, "TimeSeries[]")) DO
                          FOREACH (JsonGetItem(__MESSAGE, "Values[]")) DO
                               FOREACH (JsonGetItem(__MESSAGE, "Value[]")) DO
                                    Stage = JsonGetValue(__ITEM, "Value")
                                    Timestamp = JsonGetValue(__ITEM, "datetime")
                                        Print(Stage)
                                        Print(Timestamp)
                                        StoreEvent(, ,TimeStamp, Stage)
                               ENDFOR
                          ENDFOR
                     ENDFOR
                ENDFOR
                
                
                

                 

                I still have a couple questions. So by specifying "value\timeseries[]" in the first FOREACH, this in essence drives it two layers deep into the JSON?

                Also I'm trying to following what is happening when you use the temp and temp2 variable to pass __ITEM to the next FOREACH. Can you explain a little more?

                • Re: UFL Connector INI help for some practical common REST endpoints
                  Devin

                  Unfortunately, the INI you gave me does not work. Every time it fires I get about 25 errors all about error parsing line. Is there something else in the configuration that might be preventing it from parsing correctly, such as the word wrap or new line parameters? Right now I don't have anything specified for New Line and word wrap is 0.

                    • Re: UFL Connector INI help for some practical common REST endpoints
                      Devin

                      Martin - Disregard. I changed the word wrap parameter to -1 and it is working!

                      3 of 3 people found this helpful
                        • Re: UFL Connector INI help for some practical common REST endpoints
                          mhruzik

                          Oh, sorry, I forgot to mention it, WordWrap needs to be set to -1, so the whole stream is processed as one line.

                           

                          By specifying "value\timeseries[]", the logic goes to JSON key (in this case it's a whole JSON) in ROOT\value, then it finds "timeseries" key, and by specifying "[]" it know's it's an array which you want to iterate. In this case there is only one JSON in the array, but if there will be more ([{},{},{}...]), the logic goes through all of them.

                           

                          Unfortunately to get your timestamp and value, the logic needs to iterate through three nested arrays and since every single array needs an own iterator, we need to use temp1 and temp2 for it. Foreach in INI always returns the current item in the "__Item" variable, so we need to assign the value to temp's manually. Therefore it's possible to iterate through nested loops.

                          Anyway I'am glad it works for you! Martin H.

                          2 of 2 people found this helpful