Calling a SQL query within a openhistorian query type in Grafana

GPA Products openHistorian
1

Hi all, does anyone knows if is possible to call a SQL query line within a openHistorian line in Grafana ?

Example:
FILTER ActiveMeasurements WHERE SignalType = (‘FREQ’, IPHA’)

rather than ‘FREQ’ I woudl like to replace with another value coming from the result of a SQL, such as this syntax:
FILTER ActiveMeasurements WHERE SignalType = {SELECT Acronym from SignalType}

This “hybrid” query would allow more flexibility within what I’m trying to build in Grafana dashboards.

Thanks!

2

Sub-query statements are currently not supported in the FILTER operations due to the added complexity this would add to the filtering syntax. Here is the standard FILTER syntax that is officially supported in STTP (as a reference):

raw.githubusercontent.com/sttp/filter-expressions/main/FilterExpressionSyntax.g4

However, you can query available sub type metadata in Grafana using a “variable” with an expression like the following:

image
image844×753 39.3 KB

Note above the WHERE option, here you can further apply filtering to selected metadata.

Make sure and check the Multi-value option, like below, so several values can be selected:

image
image717×397 22.9 KB

Then you can use the selected variable results in a standard filter expression:

image

FILTER TOP 10 ActiveMeasurements WHERE SignalType IN (${SignalTypes:sqlstring})

All that said, you can actually use a regular SQL Server data source in Grafana, even one which can indirectly call the openHistorian for data. With this you can use highly complex sub queries, see example below. Note, however, that there is a performance penalty in querying openHistorian data in this manner:

 /*
  For the following expression:

      CONVERT(DATETIME2, REPLACE('{__from:date:YYYY-MM-DD HH!mm!00.000 Z}', '!', ':')) AT TIME ZONE 'UTC'
  
  the custom date time format, e.g., YYYY-MM, is being used to get dashboard starting date and time for only
  one half second of data. The custom time format is from the Moment JS library which, for the way it is being
  used in Grafana, only returns the timestamp in local time, not UTC. Luckily this can include the time zone,
  i.e., the 'Z' option, which allows a time zone conversion when string is converted to a DATETIME2 using the
  "AT TIME ZONE 'UTC'" SQL Server expression. Also, this needs to use '!' for the time separator in the format
  string since the ':' is used in Grafana custom time variables as a reserved parsing token.
*/
SELECT ADB.Longitude, ADB.Latitude, MDB.PointTag, AVG(MOH.Value) AS Value
FROM GetHistorianData('OHDNSNameOrIP', 'PPA',
(CONVERT(DATETIME2, REPLACE('${__from:date:YYYY-MM-DD HH!mm!00.000 Z}', '!', ':')) AT TIME ZONE 'UTC'), /* from time down to minute, second 0.0 */
(CONVERT(DATETIME2, REPLACE('${__from:date:YYYY-MM-DD HH!mm!00.500 Z}', '!', ':')) AT TIME ZONE 'UTC'), /* from time down to minute, second 0.5 */
(SELECT STUFF((
  SELECT ',' + CAST(PointID AS NVARCHAR)
  FROM Measurement
  LEFT JOIN ActiveMeasurement ON Measurement.SignalID = ActiveMeasurement.SignalID
  WHERE SignalType = 'FREQ'
  FOR XML PATH('')), 1, 1, ''))
) AS MOH
INNER JOIN Measurement AS MDB ON MOH.ID = MDB.PointID
INNER JOIN ActiveMeasurement AS ADB ON ADB.SignalID = MDB.SignalID
GROUP BY ADB.Longitude, ADB.Latitude, MDB.PointTag
1 Like
3

Hi Ritchie, thanks for all the details here. And indeed, you have shown an workaround which I can explore in order to get the best of the two worlds. I will give a try!