export all trends logs

[ionel]

hello, I would like to export twice a month all trends log to an FTP server . can you help me with a script?
thank you.

[Daniel]

Check this
https://forum.logicmachine.net/showthrea...51#pid2251

[ionel]

I use this  script but don,t work:

Code:

--**************************************************************************--
--** 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 *************************************************************--


--Mettre la valeur true pour exporter l'ensemble des courbes :
export_all = true

-- mettre le noms de chaque courbes que vous souhaitez exporter (Seulement utilisé quand : export_all = false)
trendnames = {
  "Total Electric Usage",
  "Total Water Usage",
  "Total Gas Usage",
}


require('trends')


trends_table = db:getall('SELECT name FROM trends ORDER BY name DESC')


if export_all == false then
 
  i = 1
    for _, trend_names in ipairs(trends_table) do
    delete_from_table = true
   
    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


if #trends_table < 1 then
  log("No trends available, Could not export trends")
  return
end


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") .. '"')

table.insert(buffer, '""')


local months = { "Januari", "Februari", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }


for _, trend_names in ipairs(trends_table) do
 
  trend_name = trend_names.name
 
 
  table.insert(buffer, '"##### START OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')
 
  table.insert(buffer, '""')


  timestamp = os.time()
    startpoint = os.date('*t', timestamp)
    endpoint = os.date('*t')

   
    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

   
    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
 
 
  resolution = 86400
 
 
  trenddatamonth = trends.fetch(trend_name, dates, resolution)
 
 
  trenddatamonthavg = trends.fetchone(trend_name, dates, resolution)
 
 
  table.insert(buffer, '"Export of the average usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')
 
  table.insert(buffer, '""')
 
  table.insert(buffer, '"Start date","End Date","Average month value"')
 
  table.insert(buffer, '"01-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. #trenddatamonth .. '-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. trenddatamonthavg .. '"')
 
  table.insert(buffer, '""')
 
  table.insert(buffer, '"Detailed export of the daily usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')
 
  table.insert(buffer, '""')
 
  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} 
   
    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)
   
    table.insert(buffer, csv)
  end
 
  table.insert(buffer, '""')
 
  table.insert(buffer, '"##### END OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')
 
  table.insert(buffer, '""')
end



src = 'Trend Export '.. os.date('%Y-%m-%d %H#%M#%S') .. '.csv'
dst = '/home/ftp/' .. src
io.writefile(dst, buffer)




local ftp = require("socket.ftp")
local ltn12 = require("ltn12")


-- Indiquer le dossier du serveur FTP dans lequel vous souhaitez mettre le fichier CSV des courbes
target = '/SALVARI_BMS/' .. src

--Indiquer HOST / USER / PASSWORD
f, e = ftp.put{
  host = "192.168.1.181",
  user = "ionel_bbtp",
  password = "my_pass",
  type = "i",
  argument = target,
  source = ltn12.source.file(io.open(dst, "rb"))
 
}

if (e) then
  log (e)
  log (f)
  alert("Could not ftp: ", e, "\n")
end
log("ftp_trends")


os.remove(dst)

---

it is a scheduled script

[ionel]

UPDATE:


I tried the script on an LM with an older firmware version and it works. it seems that the problem is with the latest firmware version(20240426)

any idea how i can solve the problem?

 Thank you

[Daniel]

Do you have any logs/errors/alerts?

[ionel]

Do you have any logs/errors/alerts?

no, i don't have any alert/error. I also made a script for email test and it doesn't work either. I tried on two LMs in two different locations (new firmware) and both the email test script and the ftp export script do not work.  For an LM with an old version (SW: 20191015) they work very well.

[admin]

Did you check Logs? The script should at least produce an entry there when it runs. Are you using "Run script" in the editor?

[ionel]

Did you check Logs? The script should at least produce an entry there when it runs. Are you using "Run script" in the editor?

the script is active. but I don't have any in the logs. it's a empty page

[admin]

It's not about script being active. Try running it manually via "Run script" button in the editor and check what happens in Alerts/Logs/Error log tabs.

[ionel]

It's not about script being active. Try running it manually via "Run script" button in the editor and check what happens in Alerts/Logs/Error log tabs.

 I have the error message:         Script is already running or disabled

[admin]

How often is the script set to run? Try rebooting LM and running the script manually again.

[ionel]

How often is the script set to run? Try rebooting LM and running the script manually again.

I solved it. in the common functions there was a script that blocked everything. the script was default in the firmware. mqtt broker. I deleted this part and it worked

Thanks for everything.

[ionel]

hello

for this script, what needs to be modified to give me maximum value. Not "Average day value"
TKS