Help in using HTTP GET to capture electricity usage

[grant1]

Greetings. My local power utility company offers a user-specific URL that displays hourly electricity usage by day for the past 13 months. I tried using the HTTP Request node to capture this data that I can parse and send to Influx or some other place, but am having trouble deciphering the output.

Here is what it looks like in a browser and here is the URL (no authentication is needed).

Here is what I tried (this is using Node-RED v2.1.3)

...and obviously it's not coming through as a parsed JSON object, but some table.

Looking for tips on how to get this data into something that I can send to InfluxDB, CSV or really anything other than an HTML table.

[E1cid]

looks like an xml is returned, try passing it through a xml node.
e.g.

[{"id":"35eb22f321d14cdc","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"10","payloadType":"num","x":190,"y":3880,"wires":[["92cb805f.88deb"]]},{"id":"92cb805f.88deb","type":"http request","z":"bf9e1e33.030598","name":"","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://usagedata.dteenergy.com/link/E3E1B93D-4349-48FD-8289-566E8FB37B01","tls":"","persist":false,"proxy":"","authType":"","x":310,"y":3960,"wires":[["50215da8.3ebfa4"]]},{"id":"50215da8.3ebfa4","type":"xml","z":"bf9e1e33.030598","name":"","property":"payload","attr":"","chr":"","x":480,"y":3960,"wires":[["252eefed571911b3"]]},{"id":"252eefed571911b3","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":710,"y":3960,"wires":[]}]

[grant1]

Thanks, that definitely helps.

I can see that, for example, these two values:

are here:

Since there are ~8,904 rows of data being returned (past 371 days x 24 hrs/day), I think I would just have to create a lookup table with a, b and c to get the readings for each hourly time value.

IntervalBlock[a].IntervalReading[b].value[c]

where:

• a represents the day (371 represents the prior day, which is the most recent available)
• b represents the hour of the day (0 starts at midnight)
• c represents the real energy usage in kilowatt-hours

Unless someone sees an easier way?

[E1cid]

you could return the js timestamp of the start hour and the value.
e.g.

[{"id":"35eb22f321d14cdc","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"10","payloadType":"num","x":190,"y":3880,"wires":[["92cb805f.88deb"]]},{"id":"92cb805f.88deb","type":"http request","z":"bf9e1e33.030598","name":"","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://usagedata.dteenergy.com/link/E3E1B93D-4349-48FD-8289-566E8FB37B01","tls":"","persist":false,"proxy":"","authType":"","x":310,"y":3960,"wires":[["50215da8.3ebfa4"]]},{"id":"50215da8.3ebfa4","type":"xml","z":"bf9e1e33.030598","name":"","property":"payload","attr":"","chr":"","x":480,"y":3960,"wires":[["45ea9f11.2d206"]]},{"id":"45ea9f11.2d206","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.feed.entry[4].content[0].IntervalBlock[*].IntervalReading.{\t    \"Value\": $number($.value[0]), \"time\":$number($.timePeriod[0].start[0])*1000}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":3960,"wires":[["252eefed571911b3"]]},{"id":"252eefed571911b3","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":710,"y":3960,"wires":[]}]

JSONata expression

$$.payload.feed.entry[4].content[0].IntervalBlock[*].IntervalReading.{
   "Value": $number($.value[0]),
   "time":$number($.timePeriod[0].start[0])*1000
}

This should return 8927 values and timestamps.
The timestamp could be further parsed if needed

[grant1]

Thank you, that's brilliant. I have only recently started to explore JSONata, but can see it's super powerful.

Two questions about your flow:

1. why are we injecting 10 into the flow? Since this xml file seems to update once every 24 hours, I was just planning to inject one every 24 hours.
2. why are we multiplying the timestamp by 1000? Obviously if the energy usage is only supplied as value for every hour, then millisecond precision should be fine for Influx.

Thank you very much for your help! Once I get this sorted out, I will post for others to use in case they have the same utility provider and wish to capture this information via Node-RED.

[E1cid]

1. no reason just reusing an old inject
2. to convert from unix(seconds) timestamp to js(milliseconds). You don't have to but if you want to parse further JS is more useful.