sending object log by object address or name via email

[IC_Systems]

hello every one 

I found this function script in the community that receives the log hours and the email addresses, and it will send the logged objects by email.

I want the function script to send only the logged data for a specific object address.

script:  "

  ------------------------BEGINNING OF CSV CREATING-------------------------------

  logtime = os.time() - 60 * 60 * log_hrs

-- list of objects by id

objects = {}

-- objects with logging enabled

query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'

for _, object in ipairs(db:getall(query)) do

  objects[ tonumber(object.address) ] = {

    datatype = tonumber(object.datatype),

    name = tostring(object.name or ''),

  }

end

--log("objects",objects)

-- csv buffer

  buffer = {'sep=,'..'\r\n'.. '"date","address","name","value"' }

-- get object logs

query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'

--log(query, logtime)

for _, row in ipairs(db:getall(query, logtime)) do

  object = objects[ tonumber(row.address) ]

  --log("object " .. _, object)

  -- found matching object and event type is group write

  if object and row.eventtype == 'write' then

    datatype = object.datatype

    -- check that object datatype is set

    if datatype then

      -- decode data

      data = knxdatatype.decode(row.datahex, datatype)

      -- remove null chars from char/string datatype

      if datatype == dt.char or datatype == dt.string then

        data = data:gsub('%z+', '')

      -- date to DD.MM.YYYY

      elseif datatype == dt.date then

        data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)

      -- time to HH:MM:SS

      elseif datatype == dt.time then

        data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)

      end

    else

      data = ''

    end

    -- format csv row

    logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)

    csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

    -- add to buffer

    table.insert(buffer, csv)

  end

end

  -------------------------END OF CSV CREATING----------------------------

"

this is the community link:  https://community.se.com/t5/KNX-Forum/HomeLynk-sending-object-log-via-email/td-p/51550

@Erwin-vd-Zwart 

[Erwin-vd-Zwart]

Hi,

You just need to change the query in that case:

-- get object logs

query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE address = ? AND logtime >= ? ORDER BY id DESC'

--log(query, logtime)

for _, row in ipairs(db:getall(query, "1/1/1", logtime)) do

In this query only object 1/1/1 is selected

BR,

Erwin

[IC_Systems]

hank you @Erwin-vd-Zwart  for your reply 

I tried that but I receive an empty CSV file.

is there anything else that should be changed?

[Erwin-vd-Zwart · Accepted solution]

Yes, sorry for the mistake but the address is encoded in the DB so you need an extra command (:

Change:

db:getall(query, "1/1/1", logtime)

into:

db:getall(query, knxlib.encodega("1/1/1"), logtime)

[IC_Systems]

thank you @Erwin-vd-Zwart  I appreciate your time.

with your permission, I need one last thing: 

how to send in the same CSV file multiple sheets.

I have three objects logged and I want to send one CSV file containing three sheets for each object log.

regards,

[Erwin-vd-Zwart]

Hi,

CSV does not support multiple sheets, that is a Excel functionality..

[IC_Systems]

thanks @Erwin-vd-Zwart 

appreciate your help

[IC_Systems]

Hi @Erwin-vd-Zwart  I hope you are doing well 

I want to show some of the objects log values in the visualisation, but I am lost in this code.

Could you tell me how to deal with object log values using lua script.

Regards

[Erwin-vd-Zwart]

Well basically we do 2 query’s on the SQLite DB because the object log table holds only the records for the encoded GA, timestamp and value, so to get the name and other parameter we do the second request to map both together..

To help you better it would be good if you can elaborate a bit in what you try to achieve..

i also have a .lp code (client side lua in HTML) so you can create an app view for the object logs so you can embed the view by a frame into the visu after adding the .lp just need to add /user/logs.lp as URL in the frame to show them.

[IC_Systems]

Hi @Erwin-vd-Zwart  I want to show daily/monthly energy which saved in the object logs.

I think what you say about .ip code will be a good solution, and its interesting.

Regard,

[Erwin-vd-Zwart]

I can prepare something, but do you need to filter certain objects from the log, or just show all that is available in the list?

[IC_Systems]

thank you @Erwin-vd-Zwart  for your response and effort 

yah I want to filter certain objects.

regards,

[Erwin-vd-Zwart · Accepted solution]

Try this:

1) Add a unique TAG to your objects of interest you want to show in your logs view, in this sample i used 'logs' 

1) Run code below once to automatically create the logs.lp file in the user FTP folder, after that you can deleted this code.

3) To test this .lp file open your browser and open http://192.168.0.10/user/logs.lp?tag=logs

4) Create a frame with the URL /user/logs.lp?tag=logs  or  /user/logs.lp?tag=logs&limit=10 to limit the number of logged items for each object.

-- Run this code once and use with url: /user/logs.lp?tag=logs or when to limit each log with less then 100 results use: /user/logs.lp?tag=logs&limit=10
dst = '/www/user/logs.lp'
io.writefile(dst, [[
<?
require('apps')
tagname = getvar('tag') or ''
limit = getvar('limit') or 100
objects = grp.tag(tagname)
logitems = {}
for _, obj in ipairs(objects) do
query = 'SELECT * FROM objectlog WHERE address=? ORDER BY id DESC LIMIT ?'
items = db:getall(query, obj.id, limit)
for _, item in ipairs(items) do
logtime = os.date('%c', item.logtime)
value = busdatatype.decode(item.datahex, obj.datatype)
if item.src > 0 then
src=buslib.decodeia(item.src)
else
src='local'
end
table.insert(logitems, {name = obj.name, datetime = logtime, value = value, src=src, eventtype = item.eventtype})
end
end
if #logitems == 0 then
print('no data found from objects with this tag in the object logs')
return
end
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Logs</title>
<link rel="stylesheet" href="/apps/css/bootstrap.css">
<style>
.table { table-layout: fixed; }
.name { width: 60%; }
.value { width: 20%; }
.datetime { width: 20%; }
/* .source { width: 20%; } */
/* .event { width: 20%; } */
</style>
</head>
<body>
<table class="table">
<tr>
<th class="name">Name</th>
<th class="value">Value</th>
<th class="datetime">Date/time</th>
<!-- <th class="source">Source</th> -->
<!-- <th class="event">Event type</th> -->
</tr>
<?
for _, item in ipairs(logitems) do
?>
<tr>
<td><?=escape(item.name)?></td>
<td><?=escape(item.value)?></td>
<td><?=escape(item.datetime)?></td>
<!-- <td><?=escape(item.src)?></td> -->
<!-- <td><?=escape(item.eventtype)?></td> -->
</tr>
<? end ?>
</table>
<script type="text/javascript">
</script>
</body>
</html>
]])
script.disable(_SCRIPTNAME)

[IC_Systems]

Thank you @Erwin-vd-Zwart  i appreciate your time and effort.

Amaizing its work greate

I have the following questions:

1- if i want to add more objects i will add tag to this object snd rerun the code again, this will not make any issue?

2- could I make the limit value as variable GA in the visu and the user change it.

3- also is it possible to let the user choose the objects he want to show?

3- the limit value shouldn't exceed 100?

Regards,

[Erwin-vd-Zwart]

1- Just add the tag 'logs' at multiple objects, no need to run the .lp creation code again as that won't change the .lp file..

2- Yes it could but that would require a change in the code of the .lp file and re-run it once.

3- Yes can be made, but this is next level (kinda goes to an app level as you need a html selection box with JS code)

4- Yes can be exeeded, when the argument &limit= is missing the value 100 is used as a default but you can set it in any value as it's only used in the SQL query as limit of fetching items from the DB.

For item 2 change the start of the .lp creation code like this and run once again:

-- Use with URL /user/logs/lp?tag=logs
dst = '/www/user/logs.lp'
limitaddr = '1/1/1' -- use a 1, 2 or 4 byte integer object for this
io.writefile(dst, [[
<?
require('apps')
tagname = getvar('tag') or ''
limit = grp.getvalue(limitaddr) or 100

.... rest of the code unchanged

[IC_Systems]

Thank you @Erwin-vd-Zwart  for your response.

Regarding point 3 it will be a better solution for the end user.

I will be waiting for that 🙂

Appreciate your time and effort.

Regards,

[Erwin-vd-Zwart · Accepted solution]

Every customer wants custom development for their special needs, We can’t support all off that (:

I can provide info on how to build your own app but it requires quite some knowledge of HTML, CSS, JS, SQL queries and LUA.

[IC_Systems]

Oh really 

I didn't think it will be in this complexity.

For me this is a new way of showing data, Could you tell me where I can use this method in the SL. This is my last question 🤣

I know that I am asking a lot but I like to learn from experts, and not always you got this chance  ☺️

thanks for your support @Erwin-vd-Zwart .

Hope you a good life .

Regards,

[IC_Systems]

Hello @Erwin-vd-Zwart  

I tried to use GA for the limit but I got This:

 

[Erwin-vd-Zwart]

Are you using /user/logs/lp?tag=logs

must be /user/logs.lp?tag=logs

[IC_Systems]

I try this: /user/logs.lp?tag=logs

I got data but the limit didn't change!

[Erwin-vd-Zwart]

The limit is per object that is tagged, so if you have 3 objects tagged and set the limit to 10 you get 3 times the limit of 10 so 30 lines, try to reduce the limit if you have a lot of tagged objects

[IC_Systems]

Hi @Erwin-vd-Zwart 

is there any thing in the limit?

I try an easy way for the list by adding and removing TAG using script 🙂

and it works fine but should refresh to view the changes.

[Erwin-vd-Zwart]

Hi,

In the .lp creation code below i added a listener on the limit object, if the value is changing i reload the document

-- Use with URL /user/logs.lp?tag=logs
dst = '/www/user/logs.lp'
io.writefile(dst, [[
<?
require('apps')
tagname = getvar('tag') or ''
limitaddr = '5/0/3' -- use a 1, 2 or 4 byte integer object for this
limit = grp.getvalue(limitaddr) or 100
objects = grp.tag(tagname)
logitems = {}
for _, obj in ipairs(objects) do
query = 'SELECT * FROM objectlog WHERE address=? ORDER BY id DESC LIMIT ?'
items = db:getall(query, obj.id, limit)
for _, item in ipairs(items) do
logtime = os.date('%c', item.logtime)
value = busdatatype.decode(item.datahex, obj.datatype)
if item.src > 0 then
src=buslib.decodeia(item.src)
else
src='local'
end
table.insert(logitems, {name = obj.name, datetime = logtime, value = value, src=src, eventtype = item.eventtype})
end
end
if logitems == nil or #logitems == 0 then
print('no data found from objects with this tag in the object logs')
return
end
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Logs</title>
<link rel="stylesheet" href="/apps/css/bootstrap.css">
  <script src="/apps/js/jquery.js.gz"></script>
  <script src="/apps/js/localbus.js.gz"></script>
  <script src="/scada/vis/busdecode.js.gz"></script>
<style>
.table { table-layout: fixed; }
.name { width: 60%; }
.value { width: 20%; }
.datetime { width: 20%; }
/* .source { width: 20%; } */
/* .event { width: 20%; } */
</style>
</head>
<body>
<table class="table">
<tr>
<th class="name">Name</th>
<th class="value">Value</th>
<th class="datetime">Date/time</th>
<!-- <th class="source">Source</th> -->
<!-- <th class="event">Event type</th> -->
</tr>
<?
for _, item in ipairs(logitems) do
?>
<tr>
<td><?=escape(item.name)?></td>
<td><?=escape(item.value)?></td>
<td><?=escape(item.datetime)?></td>
<!-- <td><?=escape(item.src)?></td> -->
<!-- <td><?=escape(item.eventtype)?></td> -->
</tr>
<? end ?>
</table>
<script type="text/javascript">
var limitaddr = <? print('"' .. limitaddr .. '"') ?>;
var startlimit = <? print(limit) ?>;
localbus.init();
// listen to a single object
localbus.listen('object', limitaddr, function(value) {
if (value != startlimit){
startlimit = value;
location.reload();
}
}, true);
</script>
</body>
</html>
]])
script.disable(_SCRIPTNAME)

[IC_Systems]

Great Jop @Erwin-vd-Zwart 

Many thanks 🙂

[IC_Systems]

@Erwin-vd-Zwart  How Can I add another listener, I want to use it to reload the document when the TAG List changed

regards, 

[Erwin-vd-Zwart]

Make sure to set the min setting (in the vis params of the object) of the limit object to 1 (otherwise with 0 there are no values fetched and loading discarded)

[Erwin-vd-Zwart]

You can solve that server side, add this add the end of your TAG adding script:

limitobject = '1/1/1'

limitvalue = grp.getvalue(limitobject)

grp.write(limitobject, limitvalue+1)  -- increase value with 1 to trigger the refresh

grp.write(limitobject, limitvalue) -- write original limit value back

[IC_Systems]

@Erwin-vd-Zwart  I don't know how to thank you.

Appreciate your time and effort.

Many thanks 🙂

regards,