VOLTTRON publish message to bus and see data in Grafana

In my VOLTTRON agent I am making some calculations and then publishing some float data to the VOLTTRON message bus where then the forward agent should pick it up and SQL agent ingest the data into the time scale SQL.

Can someone help me with if I publish to the bus with this in my agent code:

#publish to message bus
self.vip.pubsub.publish(
    peer="pubsub",
    topic=
    f"LBS_AGENT",
    headers={headers.TIMESTAMP: format_timestamp(get_aware_utc_now())},
    message=f"TOTAL_AIR_FLOW/{total_flow_temp}",
)

How would I query for it in Grafana? This doesn't work:

SELECT
  "time" AS "time",
  metric AS metric,
  value
FROM slipstream_volttron
WHERE
  $__timeFilter("time") AND
  metric LIKE 'TOTAL_AIR_FLOW'
ORDER BY 1,2

To get from start to finish here, a few things need to happen. (Note that this assumes that when you say "SQL agent" you are referring to the SQLHistorian agent, and not a custom database solution.)

1. Once you publish the data, the historians (both the ForwardHistorian and the SQLHistorian) need to recognize the topic as one they should archive/forward.
   The default behavior for the historian is to save/forward topics which begin with one of four prefixes: "devices/", "analysis/", "datalogger/", or "record/". Additional topics may be added in the configuration of the historian agents, but unless you have done so, they will ignore this publish, since your topic begins with 'LBS_AGENT'. It is probably better to choose a topic prefix which the historians already understand (i.e., analysis), as we will see in the next bit.

2. The format of the message needs to be appropriate to the topic on which it is published for the SQL Historian to be able to interpret it. The base historian is not aware of what structure data of topics beginning with LBS_AGENT might have. Devices, analysis, and datalogger topics all require the message to have a particular structure. These can be found here: https://volttron.readthedocs.io/en/main/agent-framework/historian-agents/historian-topic-syntax.html. Devices and analysis topics take dictionaries, so you are probably better off publishing like this:

   self.vip.pubsub.publish(
     peer="pubsub",
     topic=f"analysis/LBS_AGENT",
     headers={headers.TIMESTAMP: format_timestamp(get_aware_utc_now())},
     message={"TOTAL_AIR_FLOW": total_flow_temp}
   )
   

3. Your datasource query in Grafana needs to match the structure of the historian database. In the case of the SQLHistorian, there are two tables: "topics", and "data". The first time a given topic is stored, it will create an entry in the "topics" table with a "topic_name" and a "topic_id". The "topic_name" field is the topic you published with (without the analysis prefix) plus the key of the dictionary in your message -- i.e., "LBS_AGENT/TOTAL_AIR_FLOW". The topic_id is also contained as a foreign key in the data table. The "data" table, meanwhile, has the columns "ts" (your time information), "topic_id", and "value_string" (which contains your data). If you know the topic_id already when you create your query in Grafana, you can use a query along the lines of:

   SELECT
     ts AS "time",
     value_string AS value
   FROM slipstream_volttron 
   WHERE
     $__timeFilter("time") AND
     topic_id = <the_actual_topic_id>
   

If you are not going to know the topic_id, or need to be more dynamic, you can join the topics and data tables on topic_id or use a Grafana query variable to get the topic_ids and use that variable in the query.

The way I do it is:

1. Find the topic (change fake to what you want)

SELECT * FROM topics WHERE topic_name LIKE ('%fake%');

You will see something like

1 | campus/building/fake/OutsideAirTemperature1 | {"units": "F", "type": "integer", "tz": "US/Pacific"}
2 | campus/building/fake/SampleLong1 | {"units": "Enumeration", "type": "integer", "tz": "US/Pacific"}
3 | campus/building/fake/SampleBool1 | {"units": "On / Off", "type": "integer", "tz": "US/Pacific"}
4 | campus/building/fake/OutsideAirTemperature2 | {"units": "F", "type": "integer", "tz": "US/Pacific"}
5 | campus/building/fake/SampleLong2 | {"units": "Enumeration", "type": "integer", "tz": "US/Pacific"}
6 | campus/building/fake/SampleBool2 | {"units": "On / Off", "type": "integer", "tz": "US/Pacific"}

Let say you are interested in campus/building/fake/SampleLong2

Then Grafana would be:

  SELECT
    $__time(data.ts),
    CAST (data.value_string AS DECIMAL) as "Label"
  FROM data, topics
  WHERE
    $__timeFilter(ts) AND
    data.topic_id = topics.topic_id AND
    topics.topic_name LIKE ('campus/building/fake/SampleLong2')
  ORDER BY 1,2

This will work for a Postgres db.