28.05.2016, 16:52
Is there an API for a new trends mechanism? e.g. for using some average values in scripts?
|
Trends API
|
|
28.05.2016, 17:13
Hi buuuudzik,
I think it must be possible for own values made them via script to write them in new "virtual" GA's, which you can log for trends. So I made it with custom values.
28.05.2016, 22:01
Hi buuuudzik,
Try this: require('trends') name = 'my trend name' dates = {} dates['start'] = { year = 2016, month = 4, day = 12 } dates['end'] = { year = 2016, month = 4, day = 13 } -- resolution (in seconds) is optional -- default trend resolution is used when not set resolution = nil res = trends.fetch(name, dates, resolution) log(res) BR, Erwin
29.05.2016, 11:22
(29.05.2016, 06:12)admin Wrote: Docs: Thank you everyone, especially you admin, because this is exactly what I was looking for 
02.09.2016, 18:46
(This post was last modified: 05.09.2016, 09:31 by Erwin van der Zwart.)
Here is a script to create trends as batch from start address until end address for new (and relaese candidate) FW.
Code: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154 -- Batch adding trends - Created by Erwin van der Zwart - Schneider Electric Netherlands -----------
-- For spaceLYnk FW 1.2.1 or higher and homeLYnk FW 1.5.1 or higher --------------------------------
-- Refresh browser after creation to show the trends -----------------------------------------------
----------------------------------------- Start Parameters -----------------------------------------
-- Start address of objects
start_objectaddress = '1/1/1'
-- Trends for all objects between these range are created
-- End address of objects
end_objectaddress = '1/1/3'
-- Set username and password for access to HL
username = 'admin'
password = 'admin'
-- Select trend type to create (or multiple types on same object)
create_trendtype_counter = true
create_trendtype_counter_with_negative_delta = true
create_trendtype_absolute_value = true
-- Set trend precicion (!Important! Can only be these values: 0 to 8)
trendprecision = 2
-- Set trend resolution (!Important! Can only be these values: 5 / 10 / 15 / 20 / 30 / 60 minutes))
trendresolution = 5
-- Set trend count resolution (!Important! Can only be these values: '30' = 30 days, '180' = 180 days, '365' = 1 year, '730' = 2 years, '1825' = 5 years
trendcountresolution = 365
-- Set trend count days (!Important! Can only be these values: '1' = 1 year / '2' = 2 years / '5' = 5 years / '10' = 10 years)
trendcountdaily = 10
-- Set show always 0 base line
trendshowzero = 0 -- 0 = disabled 1 = enabled
------------------------------------------ End Parameters ------------------------------------------
------------------------------ DON'T CHANGE ANYTHING UNDER THIS LINE -------------------------------
-- Load modules
require('json')
require('socket.url')
require('socket.http')
-- Set HL ip address as localhost
ip = '127.0.0.1'
-- Calculate start address to DB format
start_objectaddress = knxlib.encodega(start_objectaddress)
-- Calculate end address to DB format
end_objectaddress = knxlib.encodega(end_objectaddress)
-- Create url for trend creation
url = 'http://' .. username .. ':' .. password .. '@' .. ip .. '/scada-main/trends/save'
-- Function to send request to create trend
function url_send(trend_object, trend_name, trend_type, trend_resolution, trend_precision, trend_count_resolution, trend_count_daily, trend_show_zero, trend_id)
local trend = {
object = trend_object,
name = trend_name,
type = trend_type,
resolution = trend_resolution,
precision = trend_precision,
count_resolution = trend_count_resolution * 12 * 24, -- nr of days * nr of points in a hour at min resolution (60/5 = 12) * hours per day
count_daily = trend_count_daily * 365, -- nr of days * 365 days a year
show_zero = trend_show_zero,
id = trend_id,
}
data = json.encode(trend)
form_data = 'data=' .. socket.url.escape(data)
socket.http.TIMEOUT = 15
local res, code, response_header = socket.http.request(url, form_data)
return res, code, response_header
end
-- Set counters for creation log
number_of_trends = 0
number_failed = 0
-- Loop from startadres to end address to create trends
for i = start_objectaddress, end_objectaddress, 1 do
-- Get current group address from loop
current_GA = knxlib.decodega(i)
-- Get current object info
objectinfo = grp.find(current_GA)
-- Check if object excists
if objectinfo ~= nil then
-- Set parameters for request
trendobject = objectinfo.id
trendname = objectinfo.name
trendid = ''
-- Check if trend type 'Counter' should be created
if create_trendtype_counter == true then
-- Call function to send create request
result, code, response_header = url_send(trendobject, trendname, 'C', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
-- Calculate results for creation results log
if result == '{"success":true}' then
number_of_trends = number_of_trends + 1
else
number_failed = number_failed + 1
end
end
-- Check if trend type 'Counter with negative delta' should be created
if create_trendtype_counter_with_negative_delta == true then
-- Call function to send create request
result, code, response_header = url_send(trendobject, trendname, 'D', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
-- Calculate results for creation results log
if result == '{"success":true}' then
number_of_trends = number_of_trends + 1
else
number_failed = number_failed + 1
end
end
-- Check if trend type 'Absolute value' should be created
if create_trendtype_absolute_value == true then
-- Call function to send create request
result, code, response_header = url_send(trendobject, trendname, 'G', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
-- Calculate results for creation results log
if result == '{"success":true}' then
number_of_trends = number_of_trends + 1
else
number_failed = number_failed + 1
end
end
end
end
if number_failed == 0 then
log ("Created " .. number_of_trends .. " trends succesfully")
else
log ("Created " .. number_of_trends .. " trends succesfully and creation of " .. number_failed .. " trends failed")
end
-- Disable script when done automaticly
script.disable(_SCRIPTNAME)Here is a script to batch export last month trend data as csv and send by mail (with trend selection) for new (and relaese candidate) FW. Code: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237 --**************************************************************************--
--** Email trendlog data as CSV attachment created by Erwin van der Zwart **--
--************ For HL from FW 1.5 and SL from FW 1.2 with NGINX ************--
--**************************************************************************--
--*************************** Start of parameters **************************--
-- Version 1.1 *************************************************************--
--Gmail (smtp) username !IMPORTANT!
user = 'YOUR EMAIL ADRESS'
--Gmail (smtp) password !IMPORTANT!
password = 'YOUR PASSWORD'
--Sender for e-mail
from = '<' .. user .. '>'
alias_from = 'YOUR ALIAS'
--Recipient for e-mail
to = '<receiver@domain.com>'
alias_to = 'receiver'
--Subject for e-mail
subjectpart1 = 'Trend export file'
subjectpart2 = 'automaticly send by homeLYnk'
--Message on bottom of email (will only be showed when client don't understand attachment)
epilogue = 'End of message'
--Set export mode (selected trend(s) or all trends)
export_all = true
--Set trend names if not all trends need to be exported (only used when export_all = false)
trendnames = {
"Total Electric Usage",
"Total Water Usage",
"Total Gas Usage",
}
--**************************************************************************--
--**************************** End of parameters ***************************--
--**************************************************************************--
--****************** DON'T CHANGE ANYTHING UNDER THIS LINE *****************--
--**************************************************************************--
require('trends')
-- Get all trend names from DB
trends_table = db:getall('SELECT name FROM trends ORDER BY name DESC')
-- Check if all trends or selection neeed to be exported
if export_all == false then
-- Loop through trends_table
i = 1
for _, trend_names in ipairs(trends_table) do
delete_from_table = true
-- Loop through trendnames
for _, trendname in ipairs(trendnames) do
if trendname == trend_names.name then
delete_from_table = false
end
end
if delete_from_table == true then
table.remove(trends_table, i)
end
i = i + 1
end
end
-- Check if the is at least 1 trend to be exported
if #trends_table < 1 then
log("No trends available, Could not export trends")
return
end
-- csv buffer
buffer = {}
-- Add to buffer
table.insert(buffer, '"This file contains the export data of ' .. #trends_table .. ' trend(s) and is automaticly created on ' .. os.date("%A",os.time()) .. ' ' .. os.date("%d-%m-%y at %H:%M") .. '"')
-- Add empty line
table.insert(buffer, '""')
-- months
local months = { "Januari", "Februari", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }
-- Loop through trends_table
for _, trend_names in ipairs(trends_table) do
-- Grab trend name from DB table
trend_name = trend_names.name
-- Add to buffer
table.insert(buffer, '"##### START OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')
-- Add empty line
table.insert(buffer, '""')
-- Get current timestamp
timestamp = os.time()
startpoint = os.date('*t', timestamp)
endpoint = os.date('*t')
-- Reset to first of month
startpoint.sec = 0
startpoint.min = 0
startpoint.hour = 0
startpoint.yday = startpoint.yday - (startpoint.day -1)
startpoint.day = 1
startpoint.wday = 1
endpoint.sec = 0
endpoint.min = 0
endpoint.hour = 0
endpoint.yday = endpoint.yday - (endpoint.day -1)
endpoint.day = 1
endpoint.wday = 1
-- Get data for the past month
dates = {}
dates['start'] = startpoint
if dates['start'].month == 1 then
dates['start'].month = 12
dates['start'].year = dates['start'].year - 1
else
dates['start'].month = dates['start'].month - 1
end
dates['end'] = endpoint
dates['end'].month = dates['start'].month + 1
-- Set resolution to dayly data
resolution = 86400
-- Get last month for data by each days in this month
trenddatamonth = trends.fetch(trend_name, dates, resolution)
-- Get last month total avarage data
trenddatamonthavg = trends.fetchone(trend_name, dates, resolution)
-- Add to buffer
table.insert(buffer, '"Export of the average usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')
-- Add empty line
table.insert(buffer, '""')
-- Add header
table.insert(buffer, '"Start date","End Date","Average month value"')
-- Add to buffer
table.insert(buffer, '"01-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. #trenddatamonth .. '-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. trenddatamonthavg .. '"')
-- Add empty line
table.insert(buffer, '""')
-- Add to buffer
table.insert(buffer, '"Detailed export of the daily usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')
-- Add empty line
table.insert(buffer, '""')
-- Add header
table.insert(buffer, '"Weekday","Date","Average day value"')
for _, row in ipairs(trenddatamonth) do
stamp = dates['start'].year .. '-' .. dates['start'].month .. '-' .. string.format("%02d", _)
local y, m, d = stamp:match("(%d+)%-(%d+)%-(%d+)")
local t = { year = y, month = m, day = d}
-- format csv row
csv = string.format('%q,%q,%q', os.date("%A",os.time(t)), "" .. string.format("%02d", _) .. "-" .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year, row)
-- add to buffer
table.insert(buffer, csv)
end
-- Add empty line
table.insert(buffer, '""')
-- Add header
table.insert(buffer, '"##### END OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')
-- Add empty line
table.insert(buffer, '""')
end
--Create table to include mail settings
local settings = {
from = from,
rcpt = to,
user = user,
password = password,
server = 'smtp.gmail.com',
port = 465,
secure = 'sslv23',
}
--Create attachment inside FTP server
src = 'Trend Export '.. os.date('%Y-%m-%d %H#%M#%S') .. '.csv'
dst = '/home/ftp/' .. src
io.writefile(dst, buffer)
--Create subject
subject = subjectpart1 .. ": " .. src .. " " .. subjectpart2
--Load required modules to send email with attachment
local smtp = require("socket.smtp")
local mime = require("mime")
local ltn12 = require("ltn12")
--Create e-mail header
settings.source = smtp.message{
headers = {
from = '' .. alias_from .. ' ' .. from .. '',
to = '' .. alias_to .. ' ' .. to .. '',
subject = subject
},
--Load attachment inside body
body = {
preamble = "",
[1] = {
headers = {
["content-type"] = 'text/plain',
["content-disposition"] = 'attachment; filename="'..src..'"',
["content-description"] = '.. src ..',
["content-transfer-encoding"] = "BASE64",
},
body = ltn12.source.chain(
ltn12.source.file(io.open(dst, "rb")),
ltn12.filter.chain(
mime.encode("base64"),
mime.wrap()
)
)
},
epilogue = epilogue
}
}
--Send the email
r, e = smtp.send(settings)
--Create alert when sending gives an error with error message
if (e) then
log (e)
log (r)
alert("Could not send email: ", e, "\n")
end
--Delete created temp csv file from ftp folder inside HL
os.remove(dst)BR, Erwin van der Zwart
05.09.2016, 06:01
Nice work Erwin, in your first script you can replace IP with 127.0.0.1 and remove "if-json" call.
07.12.2016, 20:15
Thanks Erwin, those scripts save me a lot of time. I made a small change and all objects with specific tag will have own trend:
Code: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148 -- Batch adding trends - Created by Erwin van der Zwart - Schneider Electric Netherlands -----------
-- For spaceLYnk FW 1.2.1 or higher and homeLYnk FW 1.5.1 or higher --------------------------------
-- Refresh browser after creation to show the trends -----------------------------------------------
----------------------------------------- Start Parameters -----------------------------------------
-- Create trend to object with this tag
tag_name = "make_trend"
-- Set username and password for access to HL
username = 'admin'
password = 'admin'
-- Select trend type to create (or multiple types on same object)
create_trendtype_counter = true
create_trendtype_counter_with_negative_delta = true
create_trendtype_absolute_value = true
-- Set trend precicion (!Important! Can only be these values: 0 to 8)
trendprecision = 0
-- Set trend resolution (!Important! Can only be these values: 5 / 10 / 15 / 20 / 30 / 60 minutes))
trendresolution = 15
-- Set trend count resolution (!Important! Can only be these values: '30' = 30 days, '180' = 180 days, '365' = 1 year, '730' = 2 years, '1825' = 5 years
trendcountresolution = 365
-- Set trend count days (!Important! Can only be these values: '1' = 1 year / '2' = 2 years / '5' = 5 years / '10' = 10 years)
trendcountdaily = 10
-- Set show always 0 base line
trendshowzero = 0 -- 0 = disabled 1 = enabled
------------------------------------------ End Parameters ------------------------------------------
------------------------------ DON'T CHANGE ANYTHING UNDER THIS LINE -------------------------------
-- Load modules
require('json')
require('socket.url')
require('socket.http')
-- Set HL ip address as localhost
ip = '127.0.0.1'
-- Create url for trend creation
url = 'http://' .. username .. ':' .. password .. '@' .. ip .. '/scada-main/trends/save'
-- Function to send request to create trend
function url_send(trend_object, trend_name, trend_type, trend_resolution, trend_precision, trend_count_resolution, trend_count_daily, trend_show_zero, trend_id)
local trend = {
object = trend_object,
name = trend_name,
type = trend_type,
resolution = trend_resolution,
precision = trend_precision,
count_resolution = trend_count_resolution * 12 * 24, -- nr of days * nr of points in a hour at min resolution (60/5 = 12) * hours per day
count_daily = trend_count_daily * 365, -- nr of days * 365 days a year
show_zero = trend_show_zero,
id = trend_id,
}
data = json.encode(trend)
form_data = 'data=' .. socket.url.escape(data)
socket.http.TIMEOUT = 15
local res, code, response_header = socket.http.request(url, form_data)
return res, code, response_header
end
-- Set counters for creation log
number_of_trends = 0
number_failed = 0
objectwithtag = grp.tag(tag_name)
-- Loop for each group adress with specific tag
for key, value in pairs(objectwithtag) do
-- Get current group address from loop
current_GA = knxlib.decodega(value.id)
-- Get current object info
objectinfo = grp.find(current_GA)
-- Check if object excists
if objectinfo ~= nil then
-- Set parameters for request
trendobject = objectinfo.id
trendname = objectinfo.name
trendid = ''
-- Check if trend type 'Counter' should be created
if create_trendtype_counter == true then
-- Call function to send create request
result, code, response_header = url_send(trendobject, trendname, 'C', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
-- Calculate results for creation results log
if result == '{"success":true}' then
number_of_trends = number_of_trends + 1
else
number_failed = number_failed + 1
end
end
-- Check if trend type 'Counter with negative delta' should be created
if create_trendtype_counter_with_negative_delta == true then
-- Call function to send create request
result, code, response_header = url_send(trendobject, trendname, 'D', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
-- Calculate results for creation results log
if result == '{"success":true}' then
number_of_trends = number_of_trends + 1
else
number_failed = number_failed + 1
end
end
-- Check if trend type 'Absolute value' should be created
if create_trendtype_absolute_value == true then
-- Call function to send create request
result, code, response_header = url_send(trendobject, trendname, 'G', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
-- Calculate results for creation results log
if result == '{"success":true}' then
number_of_trends = number_of_trends + 1
else
number_failed = number_failed + 1
end
end
end
end
if number_failed == 0 then
log ("Created " .. number_of_trends .. " trends succesfully")
else
log ("Created " .. number_of_trends .. " trends succesfully and creation of " .. number_failed .. " trends failed")
end
-- Disable script when done automaticly
script.disable(_SCRIPTNAME)
13.02.2018, 18:49
(13.02.2018, 16:52)Domoticatorino Wrote: What kind of script is this? User library? Any kind of script. You just have to run it once.
------------------------------
Ctrl+F5
19.02.2018, 15:39
(07.12.2016, 20:15)Pawel Wrote: Thanks Erwin, those scripts save me a lot of time. I made a small change and all objects with specific tag will have own trend: Hi Pawel, in this case don't you create .csv file? Thanks.
19.02.2018, 16:32
No this script creates automaticaly trends attached to objects, way faster then creating them by hand (:
BR, Erwin
17.09.2019, 21:10
Hi
This script lacks the aggregate function. Could you update the script? Is there a function to delete all trends via script?
24.09.2019, 06:53
You can add aggregation parameter to the function arguments and trend parameter table. Possible values are MIN, MAX, LAST and AVERAGE.
To delete a certain trend, replace "save" with "delete" in URL and pass trend ID like this, where 123 is the ID you want to delete. Code: 1 data = json.encode({ id = 123 })
17.06.2022, 12:57
(This post was last modified: 17.06.2022, 12:59 by Trond Hoyem.)
Hi
I am using a somewhat minimized version of this script. I have used it many times before, but today it wont work. I get the error 'Host not found'. I was thinking it was because I had set the IP address and password wrong in my first attempt, but no... still after changing, it did not work. Any idea where to look for errors? I have the script in a Wiser FW 2.7.0 it that is important... My script is this one: Code: 123456789101112131415161718192021222324252627282930313233343536373839404142 require('json')
require('socket.url')
require('socket.http')
-- parameters
daily = 365*24*60 -- 1 day
daily1year = 365*24*12 -- 365 day
daily2year = 2*365*24*12 -- 365 day
year5_d = 5*365 -- 5 year
tag = 'nyNyTrend'
obj = grp.tag(tag)
for _, addr in ipairs(obj) do
object = addr.address
alias = addr.name
trend = {
object = knxlib.encodega(object), -- object id in db
name = alias, -- trend name
type = trend_type,
resolution = 5, -- every 5 minute
precision = 2, -- floating point precision
count_resolution = 365*24*12, -- 1 year
count_daily = 5*365 -- 5 years
}
data = json.encode(trend)
post = 'data=' .. socket.url.escape(data)
grp.removetags(addr.name, tag)
res, err = socket.http.request('http://admin:qSHs5FAcfY@HRdn@192.168.68.249/scada-main/trends/save', post)
log(res, err)
end
script.disable(_SCRIPTNAME)
There are 10 kinds of people in the world; those who can read binary and those who don't
17.06.2022, 13:20
Host not found is usually a DNS error. Are you trying to do a request to a remove device or to a local one? For local requests you should use 127.0.0.1 as the IP address.
17.06.2022, 13:23
(17.06.2022, 13:20)admin Wrote: Host not found is usually a DNS error. Are you trying to do a request to a remove device or to a local one? For local requests you should use 127.0.0.1 as the IP address. I did use the IP address of the device. I have changed it to 120.0.0.1, and now the error is timeout.
There are 10 kinds of people in the world; those who can read binary and those who don't
|
|
« Next Oldest | Next Newest »
|