Influxdb sensor needs to show tags

Feature Requests
1

I’m using the InfluxDB sensor with the following query:

SELECT entity_id,max(last) as value FROM
  (SELECT last("value") FROM "Watts" WHERE ("entity_id" != 'Total') GROUP BY "entity_id");

With this result:

name: Watts
time                entity_id   value
----                ---------   -----
1548096215000000000 Bedroom_2&3 333.12

The following works ok in homeassistant:

 sensor:                                                                                                                                                                                
    - platform: influxdb
      queries:                                                                                                                                                                           
       - name: Iotawatt Max            
         measurement: (SELECT entity_id, last("value") FROM "Watts" WHERE ("entity_id" != 'Total') GROUP BY "entity_id")                                                                
         where: '1 = 1'                                                                                                                                                                 
         field: last                                                                                                                                                                    
         group_function: max                                                                                                                                                            
         value_template: '{{ value | round(1) }}'                                                                                                                                       
         database: iotawatt

The thing is that I need the entity_id too (which can be extracted with this query: SELECT distinct(entity_id) as value from (SELECT entity_id,max(last) as value from (SELECT last("value") FROM "Watts" WHERE ("entity_id" != 'Total') GROUP BY "entity_id"));)

The best would however be if the InfluxDB had the tags as attributes (or even the whole result) so one can build a template out of the values.

2

Forunatly there is a REST api to InfluxDB so this url: http://192.168.1.2:8086/query?pretty=true&db=iotawatt&q=SELECT%20entity_id,max(last)%20as%20value%20from%20(SELECT%20last(%22value%22)%20FROM%20%22Watts%22%20WHERE%20(%22entity_id%22%20!=%20%27Total%27)%20GROUP%20BY%20%22entity_id%22)

Generates this json:

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "Watts",
                    "columns": [
                        "time",
                        "entity_id",
                        "value"
                    ],
                    "values": [
                        [
                            "2019-08-19T17:51:55Z",
                            "Inverter",
                            682.78
                        ]
                    ]
                }
            ]
        }
    ]
}

To be used in HA as follows:

sensor:                                                                                                                                                                                    
  - platform: rest                                                                                                                                                                         
    resource: 'http://192.168.1.2:8086/query?db=iotawatt&q=SELECT%20entity_id,max(last)%20as%20value%20from%20(SELECT%20last(%22value%22)%20FROM%20%22Watts%22%20WHERE%20(%22entity_id%22%20!=%20%27Total%27)%20GROUP%20BY%20%22entity_id%22)'                                                                                                                                name: IoTaWatt top consumer                                                                                                                                                            
    value_template: '{% set v = value_json["results"][0]["series"][0]["values"][0]%} {{ "%s:  %d" % (v[1], v[2]) }}'                                                                       
    json_attributes: 'results'                                                                                                                                                             
  - platform: template                                                                                                                                                                     
    sensors:                                                                                                                                                                               
      iotawatt_top_usage:                                                                                                                                                                  
        value_template: "{{ states.sensor.iotawatt_top_consumer.attributes.results[0].series[0]['values'][0][2] | round(0)}}"  

# OR if following the influxDB idea and returning as value but keeping the results as attributes:
sensor:                                                                                                                                                                                    
  - platform: rest                                                                                                                                                                         
    resource: 'http://192.168.1.2:8086/query?db=iotawatt&q=SELECT%20entity_id,max(last)%20as%20value%20from%20(SELECT%20last(%22value%22)%20FROM%20%22Watts%22%20WHERE%20(%22entity_id%22%20!=%20%27Total%27)%20GROUP%20BY%20%22entity_id%22)'                                                                                                                                name: IoTaWatt top usage                                                                                                                                                               
    value_template: '{{ value_json["results"][0]["series"][0]["values"][0][2] |                                                                                                            
    round(0) }}'                                                                                                                                                                           
    json_attributes: 'results'                                                                                                                                                             
  - platform: template                                                                                                                                                                     
    sensors:                                                                                                                                                                               
      iotawatt_top_consumer:                                                                                                                                                               
        value_template: "{% set v = states.sensor.iotawatt_top_usage.attributes.results[0].series[0]['values'][0] %} {{ '%s:  %d' % (v[1], v[2]) }}"
2 Likes