I have some issues with one of my FIBARO motion detectors connected to my Domoticz. It triggered some false alarms while away. Battery has been recently changed and all parameters settings are identical to similar devices. I wish to study it closely to determine if there is a valid reason for triggering movement or the device is just at the end of life. DzVents Log Level is set to Error+minimal execution info but since log lines are limited by the time that one might investigate them, they are gone!
While looking in the fora, I discovered interesting posts concerning exporting both logs and data from our sqlite Domoticz.
Beyond knowing the abbreviation SQL I claim ignorance concerning the subject. However I read enough in the web pages to get the following structure of our venerable Domoticz.
Here is a site that allowed me to get this far.
https://ourtechroom.com/tech/open-read-sqlite-file/
DOMOTICZ SQLITE Database Structure
- BackupLog MobileDevices Scenes
Cameras MultiMeter SetpointTimers
CamerasActiveDevices MultiMeter_Calendar SharedDevices
CustomImages MySensors Temperature
DeviceStatus MySensorsChilds Temperature_Calendar
DeviceToPlansMap MySensorsVars TimerPlans
EnOceanNodes Notifications Timers
EventMaster Percentage ToonDevices
EventRules Percentage_Calendar UV
Fan Plans UV_Calendar
Fan_Calendar Preferences UserSessions
Floorplans PushLink UserVariables
Hardware Rain Users
LightSubDevices Rain_Calendar WOLNodes
LightingLog SceneDevices Wind
Meter SceneLog Wind_Calendar
Meter_Calendar SceneTimers ZwaveNodes
To develop the following:
DzVents script defining data to be extracted from Domoticz database. I was interested in the Switch data but then again you may adapt it to whatever other information is needed.
This script will make a temporary copy of actual database; extract requested data and copy it to a csv file.
Script will also make a call to Bash script which makes json call to export log. AWK commands format output and remove unnecessary data.
SIGNAL_DEVICES in script allow data collection when devices are On. One can also juggle with triggers to get the needed effect.
Again my goal is to allow automatic data export to files which can be analysed at a one’s leisure. File names are time stamped to allow user to pinpoint the incident better.
Hope others find this useful!
ElZorroVega
P.S. Included MAWK commands directly to BASH script to avoid reading a separate MAWK file.
DzVents Script
Code: Select all
--[[ -- sqliteDump.lua
Dumps daily data as csv file
requires sqlite3 in host machine
install command on linux: sudo apt install sqlite3
install command on openwrt: opkg install sqlite3-cli
install command on synology: sudo /opt/bin/opkg install sqlite3-cli
History:
20191231 first public release
20200102 Add sudo chmod to make csvfile readable for all
20201023 Add option to use MultiMeter
20201026 Added option to get daily data, and included yesterdays' date to csv filename
20220816 Added option to get switch data and combined with log export Bash Script
]]--
local SIGNAL_DEVICES = {
'Hall Siren',
'Chamber 1 Siren',
'Fibaro Wall Plug 1'
}
--
return
{
active = true,
on =
{
timer =
{
--'at 00:04',
--'at 17:16',
},
devices =
{
'Fibaro Wall Plug 1',
--'Hall Motion Sensor'
},
},
logging =
{
level = domoticz.LOG_DEBUG, -- set to domoticz.LOG_ERROR when all is OK
marker = 'sqliteDump',
},
execute = function(dz, item)
-- ======================= enter you settings below this line ==================
local path = "/home/pi/domoticz/" -- full qualified path to your domoticz directory
local database = 'domoticz.db' -- database filename + extension
local sqlite = '/usr/bin/sqlite3' -- location of your sqlite3 tool; check with 'which sqlite3'
local csvFile = "/home/pi/Documents/domoticz2excel" --full path to the csv filename for the dump, without the .csv extension (will be added by script, together with date)
-- set to '' for the types you don't need
--[[local allTemperatures = '152,148' -- comma separated list of Temperature sensors to export --
local allMeters = '36,1168,2872' -- comma separated list of Solar + Meters + Usage sensors
local allMultiMeters = '17' -- comma separated list P1 energy sensor
local allTemperatures = '12,16,38,62,78,83' -- comma separated list of Temperature sensors to export]]--
local allSwitches = '9'
-- ======================= No modification needed in this block ==================
local csvFile = csvFile .. "_" .. os.date("%Y-%m-%d",os.time()) .. ".csv"
local copy = 'safeCopy.db'
local baseCommand = 'sudo ' .. sqlite .. ' -header -csv ' .. copy
local closure = ' >> ' .. csvFile
local collects = {}
-- ======================= Adjust the SQL Query(ies) in acordance to your needs following attention database structure ==================
--======================= https://inloop.github.io/sqlite-viewer/ =======================
--
--==Query for daily data==
--[[collects.meters = 'SELECT a.ID Meter, a.Name Name, b.Value Value, b.Counter Counter, b.Date Date FROM DeviceStatus a , Meter_Calendar b ' ..
' WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allMeters .. ');'
collects.Temperatures = 'SELECT a.ID Temperature, a.Name Name, b.Temp_Min min, b.Temp_Max max, b.Temp_Avg avg, b.Date Date ' ..
' FROM DeviceStatus a , Temperature_Calendar b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allTemperatures .. ') ORDER BY a.ID;'
collects.MultiMeters = 'SELECT a.Name, b.* FROM DeviceStatus a , MultiMeter_Calendar b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allMultiMeters .. ') ORDER BY b.DeviceRowID;']]--
--==end query daily data
--==Query for minute data
--[[collects.meters = 'SELECT a.ID Meter, a.Name Name, b.Value Value, b.Usage Usage, b.Date Date '..
' FROM DeviceStatus a , Meter b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allMeters .. ')'
collects.MultiMeters = 'SELECT a.Name, b.* FROM DeviceStatus a , MultiMeter b WHERE a.ID = b.DeviceRowID ' ..
' AND a.ID IN (' .. allMultiMeters .. ') ORDER BY b.DeviceRowID'
collects.Temperatures = 'SELECT a.ID ID, a.Name Name, b.Temperature Temperature, b.Date Date ' ..
' FROM DeviceStatus a , Temperature b WHERE a.ID = b.DeviceRowID AND ' ..
' a.ID IN ( '.. allTemperatures .. ') ORDER BY a.ID '
--==end query minute data
DOMOTICZ SQLITE Database Structure
BackupLog MobileDevices Scenes
Cameras MultiMeter SetpointTimers
CamerasActiveDevices MultiMeter_Calendar SharedDevices
CustomImages MySensors Temperature
DeviceStatus MySensorsChilds Temperature_Calendar
DeviceToPlansMap MySensorsVars TimerPlans
EnOceanNodes Notifications Timers
EventMaster Percentage ToonDevices
EventRules Percentage_Calendar UV
Fan Plans UV_Calendar
Fan_Calendar Preferences UserSessions
Floorplans PushLink UserVariables
Hardware Rain Users
LightSubDevices Rain_Calendar WOLNodes
LightingLog SceneDevices Wind
Meter SceneLog Wind_Calendar
Meter_Calendar SceneTimers ZWaveNodes]]--
--==Query for Switches
collects.LightingLogs = 'SELECT b.Date Date, a.ID ID, a.Name Name, b.nValue nValue ' ..
' FROM DeviceStatus a , LightingLog b WHERE a.ID = b.DeviceRowID AND ' ..
' a.ID IN ( '.. allSwitches .. ') ORDER BY a.ID '
local function osCommand(cmd)
dz.log('Executing Command: ' .. cmd,dz.LOG_DEBUG)
local fileHandle = assert(io.popen(cmd .. ' 2>&1 || echo ::ERROR::', 'r'))
local commandOutput = assert(fileHandle:read('*a'))
local returnTable = {fileHandle:close()}
if commandOutput:find '::ERROR::' then -- something went wrong
dz.log('Error ==>> ' .. tostring(commandOutput:match('^(.*)%s+::ERROR::') or ' ... but no error message ' ) ,dz.LOG_DEBUG)
else -- all is fine!!
dz.log('ReturnCode: ' .. returnTable[3] .. '\ncommandOutput:\n' .. commandOutput, dz.LOG_DEBUG)
end
return commandOutput,returnTable[3] -- rc[3] contains returnCode
end
local function sqliteDump()
local result, rc
if dz.utils.fileExists(path .. database) then
if dz.utils.fileExists(sqlite) then
result, rc = osCommand('cp ' .. path .. database .. ' ' .. copy)
if rc ~= 0 then return rc, result end
result, rc = osCommand('rm -f ' .. csvFile)
if rc ~= 0 then return rc, result end
for _, sql in pairs (collects) do
result, rc = osCommand(baseCommand .. ' "' .. sql .. '" ' .. closure)
if rc ~= 0 then return rc, result end
end
result, rc = osCommand('rm -f ' .. copy)
if rc ~= 0 then return rc, result end
result, rc = osCommand('sudo chmod 777 ' .. csvFile)
if rc ~= 0 then return rc, result end
else
return -1,'sqlite3 not installed / not found'
end
else
return -1,'wrong path to database'
end
return commandOutput, 0
end
--
-- Collection Iterator using nested filter and reduce functions
--
local noise = dz.devices().filter(SIGNAL_DEVICES)
.reduce(
function (acc, device)
dz.log('Device: ' .. device.name)
if device.state == 'On' or device.state == 'Open' then
acc = true
end
return acc
end, false)
-- local noises = dz.devices().filter(SIGNAL_DEVICES)
-- noises.forEach(function(noise)
-- dz.log('Device: ' .. noise.name)
-- if noise.state == 'On' or noise.state == 'Open' then
-- count = true
-- else
-- count = false
-- end
-- end)
--
--
-- main program
if noise == true then
local result, rc = sqliteDump()
if rc ~= 0 then dz.log(result,dz.LOG_ERROR) end
-- call Bash Script using JSON calls to export logs to file
local result, rc = osCommand('/home/pi/Scripts/getdzlog.sh log')
end
end
}Code: Select all
#!/bin/bash
# Script is to get log files from Domoticz when security alarm is tripped
# El Zorro 30/07/22
#
# /json.htm?type=command¶m=getlog&lastlogtime=LASTLOGTIME&loglevel=LOGLEVEL
# LOGLEVEL 1 = normal
# 2 = status
# 4 = error
# 268435455 = all
#
DHOST=192.168.1.30:8080
MESSAGE='Hello+DOMOTICZ'
lnormal=1
lstatus=2
lerror=4
lall=268435455
LASTLOGTIME=0
LEVEL=$lnormal
LOGLEVEL=$lall
IDX_Chamber_1_Siren=7
IDX_Hall_Siren=22
#
Chamber_1_Siren=$(curl -s 'http://'$DHOST'/json.htm?type=devices&rid='$IDX_Chamber_1_Siren'' | mawk '/Status/ { gsub (/^[ \t]+|[ \t]+$/, ""); gsub(/"Status" : /,""); gsub(/"/,""); gsub(/,/,"") ; print }')
#
Hall_Siren=$(curl -s 'http://'$DHOST'/json.htm?type=devices&rid='$IDX_Hall_Siren'' | mawk '/Status/ { gsub (/^[ \t]+|[ \t]+$/, ""); gsub(/"Status" : /,""); gsub(/"/,""); gsub(/,/,"") ; print }')
#
date_now=$(date +"%Y%m%d%H%M")
curl -s 'http://'$DHOST'/json.htm?type=command¶m=addlogmessage&message='$MESSAGE'&level='$LEVEL''
if [ "$#" != "1" ]; then
curl -s 'http://'$DHOST'/json.htm?type=command¶m=getlog&lastlogtime='$LASTLOGTIME'&loglevel='$LOGLEVEL'' | mawk 'BEGIN {print ("DOMOTICZ LOG Information")} /message/ { gsub(/\{/, //) ; gsub(/\}/,//) ; gsub(/\[/,//); gsub(/\]/,//); gsub (/^[ \t]+|[ \t]+$/, ""); gsub(/"message" : /,""); gsub(/"/,"") ; print }'
else
curl -s 'http://'$DHOST'/json.htm?type=command¶m=getlog&lastlogtime='$LASTLOGTIME'&loglevel='$LOGLEVEL'' | mawk 'BEGIN {print ("DOMOTICZ LOG Information")} /message/ { gsub(/\{/, //) ; gsub(/\}/,//) ; gsub(/\[/,//); gsub(/\]/,//); gsub (/^[ \t]+|[ \t]+$/, ""); gsub(/"message" : /,""); gsub(/"/,"") ; print }' > "/home/pi/Documents/$1$date_now"
sudo chmod 777 /home/pi/Documents/$1$date_now
fi
exit