Issue with JSON query - Custom API for PI

Hi!
I made a little api that is meant to speak with an OSIsoft PI AFSDK, so I’m not using the Grafana OSIsoft module, but a custom API.

Here is the error I get in the explore (same error in a dashboard)

{
  "state": "Error",
  "series": [],
  "timeRange": {
    "from": "2023-04-09T21:09:22.045Z",
    "to": "2023-04-10T03:09:22.045Z",
    "raw": {
      "from": "now-6h",
      "to": "now"
    }
  },
  "graphFrames": [],
  "logsFrames": [],
  "traceFrames": [],
  "nodeGraphFrames": [],
  "flameGraphFrames": [],
  "tableFrames": [],
  "rawPrometheusFrames": [],
  "rawPrometheusResult": null,
  "graphResult": null,
  "logsResult": null,
  "tableResult": null,
  "request": {
    "app": "explore",
    "dashboardId": 0,
    "timezone": "browser",
    "startTime": 1681096162268,
    "interval": "2s",
    "intervalMs": 2000,
    "panelId": "Q-c9c004f2-5fa0-485a-b514-b8b129804055-0",
    "targets": [
      {
        "target": "TAG123.VAL",
        "refId": "A",
        "type": "timeserie"
      }
    ],
    "range": {
      "from": "2023-04-10T02:09:22.184Z",
      "to": "2023-04-10T03:09:22.184Z",
      "raw": {
        "from": "now-1h",
        "to": "now"
      }
    },
    "requestId": "explore_left",
    "rangeRaw": {
      "from": "now-1h",
      "to": "now"
    },
    "scopedVars": {
      "__interval": {
        "text": "2s",
        "value": "2s"
      },
      "__interval_ms": {
        "text": 2000,
        "value": 2000
      }
    },
    "maxDataPoints": 1835,
    "liveStreaming": false,
    "adhocFilters": [],
    "endTime": 1681096162576
  }
}

PI is supposed to receive TAG123.VAL and return an array like this :

      "target": "TAG123.VAL",
      "dataPoints": [
        {
          "value": 404.8739929199219,
          "timestamp": 1681092366864
        },
        {
          "value": 404.773681640625,
          "timestamp": 1681092370930
        },
        {
          "value": 404.8453063964844,
          "timestamp": 1681092372907
        },

… and so on …

I think part of the problem I that the amount of information I have to give is lower than what grafana is expecting, but right now I don’t know where to look.

Here is my query endpoint:

        [HttpPost("/query")]
        public async Task<IActionResult> Query([FromBody] QueryRequestBody query)
        {
            try
            {
                var queryResponseList = new List<QueryResponse>();

                if (query.Targets != null)
                {
                    foreach (var queryTarget in query.Targets)
                    {

                        var tagName = queryTarget.Target;
                        var startTime = query.Range.From.UtcDateTime;
                        var endTime = query.Range.To.UtcDateTime;

                        if (tagName != null)
                        {

                            var piValues = await _piWrapperService.GetPlotValuesAsync(tagName, startTime, endTime);
                            _logger.LogInformation("Retrieved {Count} data points for target {Target} between {StartTime} and {EndTime}", piValues.Count, tagName, startTime, endTime);

                            var dataPoints = piValues.Select(piValue => new DataPoint
                            {
                                Value = piValue.Value,
                                Timestamp = new DateTimeOffset(piValue.Timestamp).ToUnixTimeMilliseconds()
                            }).ToList();

                            queryResponseList.Add(item: new QueryResponse
                            {
                                Target = tagName,
                                DataPoints = dataPoints
                            }); ;
                        }
                    }
                }

                return Ok(queryResponseList);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error in /query endpoint");
                return StatusCode(StatusCodes.Status500InternalServerError, "Internal server error");
            }
        }

And finally the wrapper method GetPlotValuesAsync:

        public async Task<List<PIValue>> GetPlotValuesAsync(string? tagName, DateTime startTime, DateTime endTime, int intervals = 100)
        {
            var plotRequest = new
            {
                TagName = tagName,
                StartTime = startTime.ToString("yyyy-MM-ddTHH:mm:ss.fffZ"),
                EndTime = endTime.ToString("yyyy-MM-ddTHH:mm:ss.fffZ"),
                Intervals = intervals
            };

            var content = new StringContent(JsonConvert.SerializeObject(plotRequest), System.Text.Encoding.UTF8, "application/json");
            var response = await _httpClient.PostAsync("/plots", content);
            response.EnsureSuccessStatusCode();
            var responseBody = await response.Content.ReadAsStringAsync();
            return JsonConvert.DeserializeObject<List<PIValue>>(responseBody) ?? new List<PIValue>();
        }

I’m really sorry for the long post, but I felt that I needed to put as much information as possible to better understand to problem.
Thanks for you help and I will be pleased to provide further information!

Hello

I am not seeing what the exact error is?

Hello Yosiasz,

I may have been unclear. It’s not an actual error, but my data are not recognized correctly by grafana.
When I query, I get this error from Grafana :

Query processing error
Unsupported data format

Full query :

{
  "request": {
    "url": "api/datasources/proxy/uid/2hOGeBYVz/query",
    "data": {
      "app": "explore",
      "dashboardId": 0,
      "timezone": "browser",
      "startTime": 1681184075965,
      "interval": "2s",
      "intervalMs": 2000,
      "panelId": "Q-cda3499c-56c6-411e-8dfa-0bf4c4d6df9e-0",
      "targets": [
        {
          "target": "TAGNAME.Val",
          "refId": "A",
          "type": "table"
        }
      ],
      "range": {
        "from": "2023-04-11T02:34:35.952Z",
        "to": "2023-04-11T03:34:35.953Z",
        "raw": {
          "from": "now-1h",
          "to": "now"
        }
      },
      "requestId": "explore_left",
      "rangeRaw": {
        "from": "now-1h",
        "to": "now"
      },
      "scopedVars": {
        "__interval": {
          "text": "2s",
          "value": "2s"
        },
        "__interval_ms": {
          "text": 2000,
          "value": 2000
        }
      },
      "maxDataPoints": 1835,
      "liveStreaming": false,
      "adhocFilters": [],
      "endTime": 1681184076157
    },
    "method": "POST",
    "hideFromInspector": false
  },
  "response": [
    {
      "target": "PT2633.Val",
      "dataPoints": [
        {
          "value": -0.05341097712516785,
          "timestamp": 1681180879075000
        },
        {
          "value": -0.05722567439079285,
          "timestamp": 1681180880093000
        },
        {
          "value": -0.05341097712516785,
          "timestamp": 1681182003462000
        },
        {
          "value": -0.05722567439079285,
          "timestamp": 1681182004084000
        },
        {
          "value": -0.05341097712516785,
          "timestamp": 1681182211417000
        }
      ]
    }
  ]
}

I’m not too sure what is missing
In the SimpleJSON doc, what the datasource is expecting is this :

  {
    "target":"upper_75", // The field being queried for
    "datapoints":[
      [622,1450754160000],  // Metric value as a float , unixtimestamp in milliseconds
      [365,1450754220000]
    ]
  },

Hope it’s more clear! Thanks!

1 Like

yes very clear. Could you please post the exact json data being returned by your api? We can just mock it up on our side to see how we can help.

Here is a full query from the grafana explorer, I kept only the response part:

  "response": [
    {
      "target": "PT3401.Val",
      "dataPoints": [
        {
          "value": 397.9659423828125,
          "timestamp": 1681226155438000
        },
        {
          "value": 397.95159912109375,
          "timestamp": 1681226171451000
        }
      ]
    }
  ]
}

Hope it helps!
Thanks!

Hello

Not the json from grafana. The json from your rest api exactly as is. Still waiting

Hi,
I’m using Nancy for my API. I made some changes to the code, so this is now what I give grafana

Raw JSON response: [{"Target":"TagName.Val","DataPoints":[[2.13311767578125,1681324718782],[2.2265167236328125,1681324729720],[2.1435089111328125,1681324734866],[2.1538848876953125,1681324747728]]}]

does the api really respond with ??

Raw JSON response: [{"Target":"TagName.Val

as in it actually has the words “Raw JSON response:” in the response?

No it was the result of this instruction :

_logger.LogInformation("Raw JSON response: {JsonResponse}", jsonResponse);
1 Like

so, what do you now want to do with this json response from Nancy? What kind of visualization do you want to plot this in? Here is an example using UQL with infinity plugin that can read a rest api endpoint. In the below case since I do not have an api that returns it, I used inline json you provided

This is the kind of result that I want.
Select 1 or more tag
Get the graphic of the value in time

Installing Infinity right now

If it may help I will provide additionnal info on it.
Everything runs in local and will stay that way.
We use a Aveva PI server for our historian and I want to be able to make graphs using the PIpoints provided in the query.
Working part: Select one tag name ; the search function of the api is working well
image
Api is deserialising the info - send it to a wrapper service, wich is in turn responsible of fetching the PIpoint values and send it back to the API.

This is where I have trouble, I struggle to find the right way to send it back.
I’m pretty new to Json and API, so I’m pretty sur I’m missing something obvious.

many thanks for the help^

Example of what I want to “beautify” :

1 Like

Detailed info is great but too much info unrelated to solving your problem only muddies the question

this looks totally different than your previous post of data returned by the api

"response": [
    {
      "target": "PT3401.Val",
      "dataPoints": [
        {
          "value": 397.9659423828125,
          "timestamp": 1681226155438000
        },
        {
          "value": 397.95159912109375,
          "timestamp": 1681226171451000
        }
      ]
    }
  ]
}

Hard to help you

Ok then for now I will close this ticket. I feel like I lost focus and making you lose your time.
I will return to my drawing board and return with a more concise question if I need it.

Thanks for your time and sorry for the disturbance.

1 Like

I dont think you need a new ticket and it is no disturbamve and no loss of my time. a new ticket wont nrcessarily give clarity

The question is simply for you to provide the response from the api. Which response is the accurate one? That will help us give you a correct answer.

Hi Yosiasz,
Regarding your previous post, you are right, the response has changed because I tried many differents layouts. It was the reason I felt that the topic was not making sense anymore.
But let’s move, on and I will try to give you the most concise answer I can.

Basically, here is the current response. Datapoints returns a timeseries of the given tag

                [HttpPost("/query")]
                ...
                            queryResponseList.Add(new QueryResponse
                            {
                                Target = tagName,
                                DataPoints = dataPoints
                             }
                return Ok(queryResponseList);

And the relevent classes :

        public class QueryResponse
        {
            public string? Target { get; set; }
            public List<DataPoint>? DataPoints { get; set; } = new();
        }

        public class DataPoint
        {
            public double Value { get; set; }
            public double Timestamp { get; set; }

        }

In grafana it looks like this :

response:Array[1]
 0:Object
  target:"TT2542D.Val"
   dataPoints:Array[1239]
    0:Object
     value:25.6217041015625
     timestamp:1681427246005000

Thanks a lot and I hope I sent the right thing this time … :dotted_line_face:

You provided your code but I asked for the response from that api as json format

:thinking::face_with_raised_eyebrow::disguised_face:

Still not making sense :sweat_smile:

Dang I never felt so stupid before. That’s the most json format response I can give

[
  {
    "target": "TT2542D.Val",
    "datapoints": [
      [
        25.538787841796875,
        1681430353635000
      ],
      [
        25.542346954345703,
        1681430355114000
      ]
    ]
  }
]
1 Like

Ok I just got it to work!
The problem was because of this line :

public List<DataPoint>? DataPoints { get; set; } = new();

Needed to be :

public List<DataPoint>? Datapoints { get; set; } = new();

Now everything is working, thanks for the help!

1 Like